SQL Data Definition

1. SQL Data Definition using Oracle SQL data definition using Oracle

2. DDL • SQL = DDL + DML • Oracle has its own dialect of SQL • DDL (Data definition language) • Part of SQL • Some DDL statements • create table … • Fig. 4-4, page 77 • alter table … • drop table … SQL data definition using Oracle

3. Naming conventions, 68 • Naming conventions for table names and attributes names • Illegal • spaces • hyphens • Legal • letters [a-z A-Z, not æøå] + digits • _, #, $ • first character must be a letter • no reserved words in SQL [no attribute called 'by‘ or ‘table’] • Fig. 4-1, page 68 SQL data definition using Oracle

4. Data types, 68 • Slightly different from standard SQL • Strings • Numbers • Dates • Other • no Boolean data type SQL data definition using Oracle

5. Data types, strings, 69 • varchar2 (max_length) • varying length, like names, etc. • Varchar2(10) • Max length 10 bytes • Varchar2(10 char) • Max length 10 chars • Normally 1 char ~ 1 byte, but it depends on the character table (like UNICODE) • char (fixed_length) • fixed length, like cpr, phone, etc • Length in bytes or chars, like varchar2 SQL data definition using Oracle

6. Data types, numbers, 70 • Numbers • number (digits, decimals) • fixed number of digits and decimals, like price, salary, etc. • number (digits) integer • number floating point (high precision) SQL data definition using Oracle

7. Data types, dates, 70 • date • store date and time! • no special time data type • Oracle SQL has special functions to do arithmetic on dates • more on that when we look at "select …" • advice • don't make an attribute called "age", as it changes frequently. • make an attribute called birthday. SQL data definition using Oracle

8. Data types, other, 72 • CLOB character large object • texts up to 4 gigabytes • BLOB binary large object • up to 4 gigabytes, pictures, sounds, etc. • NCLOB • like CLOB, but using 2 bytes pr. character • BFILE binary file • reference to a binary file outside the DBMS • Summary, fig. 4-2, page 72 SQL data definition using Oracle

9. Constraints, types, 72 • Types of constraints • integrity constraints • primary key • foreign key • value constraints • not null • unique SQL data definition using Oracle

10. Constraints, naming, 72 • A constraints must have a name to be able to • alter the constraints • delete the constraint • naming convention • tableName_attributeName_constraintType • student_cpr_pk • fig. 4-3, page 73 • If you don’t name your constraints Oracle will name them. Find the names • select * from user_constraints where table_name = 'PERSON'; SQL data definition using Oracle

11. Constraints, defining + primary key, 73 • 2 ways to define a constraint • column level, applied to one attribute create table student ( cpr char (9) constraint student_cpr_pk primary key, … ) • table level, can be applied to more attributes create table student_course ( cpr char(9) not null, course_number number(3) not null, constraint student_cpr_pk primary key (cpr, course_number) ) SQL data definition using Oracle

12. Constraints, foreign key, 74 • Attributes in one table refers to attributes in [another] table. • DBMS checks if the referred attributes exist. • referential integrity • Example create table student_course ( cpr char(9) not null, course_number number(3) not null constraint student_course_number_fk references course (course_number), constraint student_cpr_pk primary key (cpr, course_number), constraint student_cpr_fk foreign key (cpr) references student (cpr) ); SQL data definition using Oracle

13. Referential triggered actions, (not in the book) • foreign key … on delete cascade • if the referred row is deleted the referring row should be deleted as well create table employee ( cpr char(9) primary key, … boss_cpr char(9), foreign key (boss_cpr) references employee (cpr) on delete cascade) ) • If we delete the top boss the whole organization vanishes!! • think carefully before writing … on delete cascade • useful in weak entity sets • no … on update cascade in Oracle!! • not necessary if we use surrogate keys (they never change) SQL data definition using Oracle

14. Constraints, not null, 75 • The attribute must no be NULL. • syntaxes • name varchar2(30) not null no name constraint • name varchar2(30) constraint student_name_nnnot null • can not be specified at table level [only at attribute level] • primary key attributes must be NOT NULL • NULL means • does not exist • is not known at the moment • Don't make your own NULL's using 0 or ‘ ‘ • Don’t write NOT NULL to often. • Think before you write! SQL data definition using Oracle

15. Constraints, unique, 75 • The value of an attribute must be unique • used when a table has more candidate keys • one of the candidates is appointed primary key • the rest of the candidates are [only] unique • syntaxes • attribute level • departmentName varchar(12) unique • table level • constraint student_name_uk unique (firstname, lastname) unlikely constraint!! SQL data definition using Oracle

16. Constraint, check, 75 • Checking business rules • General constraint that every row must satisfy • attribute level • salary number(5) check ( salary >= 0 ) • gender char(1) check (gender in ('M', 'F')) • table level • check (gender ='M' or salary < '1000') SQL data definition using Oracle

17. Default value + domains, 76 • No a constraint, but has similar syntax. • Used when no value is supplied • country char(2) default 'DK' • salary number(5) default 0 • Oracle has no domain concept • No create domain … • But there is a create type … SQL data definition using Oracle

18. Creating a table, 76 • General syntax in Oracle • Page 76 • Examples • Fig. 4-4, page 77 • Tables are usually created by a DBA (Database administrator) • Tables are usually not created from an application (Java program) SQL data definition using Oracle

19. Displaying table information • Forgot the name of your tables? • Select table_name from user_tables • User_tables has information in all your tables • Forgot the name + type of attributes? • Describe Person SQL data definition using Oracle

20. Using an SQL editor • Use an moderns SQL editor • Work with your SQL statements until they are correct. • Copy / past the SQL statement to a text file - and save it. • It's handy to have the SQL statements for documentation + maintenance. SQL data definition using Oracle

21. Alter table, 82 • Altering an existing table • add / delete attributes • add / delete constraints • most alternations are restricted [hard to do] so design your tables carefully before creating them!! • syntax • alter table tableNametheAlternation • alter table student add mobilPhone char(8) SQL data definition using Oracle

22. Chicken and egg problem • From http://www-db.stanford.edu/~ullman/fcdb/oracle/or-triggers.html • Problem • If table A refers to table B and vice versa which table should be created first? • Solution • create table A without foreign key to B • create table B with foreign key to A • alter table A adding a foreign key to B SQL data definition using Oracle

23. Chicken and egg, example create table chicken ( cID number(3) primary key, eID number(3) ) ------------------------- create table egg ( eID number (3) primary key, cID number (3), foreign key (cID) refers to chicken (cID) initially deferred deferrable ) ------------------------ alter table chicken add constraint chicken_eID_fk foreign key (eID) refers to egg (eID) initially deferred deferrable SQL data definition using Oracle

24. Chicken and egg, insertion • How to insert rows into the tables? • Problem • an egg row refers to a chicken row, and vice versa • Solution (Oracle) • create the foreign keys with "initially deferred deferrable" • defers constraint checking until transaction commit • insert into chicken (1,2) • insert into egg (2,1) • commit SQL data definition using Oracle

25. Drop tables, etc. 89 • truncate table tableName • remove all data from the table, but the [not empty] table still exists. • use carefully!! • drop table • deletes the table including data • use carefully!! • Referential integrity • You are not allowed to drop a table if another table refer to it • rename oldTableName to newTableName SQL data definition using Oracle