Practice 3

1. Practice 3

2. Constraints • Primary key • Ensures uniqueness of each tuple • Implies NOT NULL • No duplicate values • Ex) create ‘professor’ table having id and name attribute CREATE TABLE professor ( id number(5) CONSTRAINT PK_ID PRIMARY KEY, name varchar2(50) NOT NULL);

3. Constraints (cont.1) • When primary key is a composite key, use the following syntax CONSTRAINT <name of primary key> PRIMARY KEY (<attribute 1>, <attribute 2>, …) • Ex) create a table factory_process having attributes, event_type, event_time, event_site, event_desc with primary key of (event_type, event_time) CREATE TABLE factory_process ( event_type number(1), event_time date, event_site char(50), event_desc varchar2(1024), CONSTRAINT event_key PRIMARY KEY (event_type, event_time));

4. Constraints (cont.2) • Foreign key • Ensures referential integrity • Syntax CONSTRAINT [constraint_name] FOREIGN KEY (column_name) REFERENCES referenced_table_name (referenced_column) [ON DELETE CASCADE] [ON DELETE SET NULL]

5. Constraints (cont.3) • Ex) Create department and student table. • Department table has attributes of id, name, phone, and fax. • Student table has attributes of id, name, address, birthday, year, and department_id. department_id is foreign key referencing id of department. • Other constraint is when a department id is deleted, the corresponding department_id in student table also have to be deleted.

6. Constraints (cont.4) • Sol.) CREATE TABLE department ( id number(3) PRIMARY KEY, name varchar2(50) NOT NULL, phone char(14) NOT NULL, fax char(14)); CREATE TABLE student ( id number(5) PRIMARY KEY, name varchar2(50) NOT NULL, address varchar2(50), birthday date, year number(1), department_id number(3), CONSTRAINT stu_dept_fk FOREIGN KEY (department_id) REFERENCES department(id) ON DELETE CASCADE);

7. Constraints (cont.5) • UNIQUE • Ensures no duplicate values for attribute • Allows NULL value • Ex) CREATE TABLE department ( id number(3) PRIMARY KEY, name varchar2(50) NOT NULL UNIQUE, phone char(14) NOT NULL, fax char(14));

8. Constraints (cont.6) • DEFAULT • Default value when no value is assigned to the attribute. • Ex) in the student table, we may do like CREATE TABLE student ( id number(5) PRIMARY KEY, name varchar2(50) NOT NULL, address varchar2(50), birthday date, year number(1) DEFAULT 1, department_id number(3), CONSTRAINT stu_dept_fk FOREIGN KEY (department_id) REFERENCES department(id) ON DELETE CASCADE);

9. Constraints (cont.7) • CHECK • Checks if input value is right • Syntax • CONSTRAINT <name of check constraint> CHECK (<condition>) • Properties • Use CHECK for data checking at data input and update • Several CHECK constraints can be made for one attribute • Several attributes can be CHECKed by one CHECK constraint • DEFAULT value must satisfy CHECK constraint

10. Constraints (cont.8) • Ex) an example using CHECK CREATE TABLE student ( id number(5) PRIMARY KEY, name varchar2(50) NOT NULL, address varchar2(50), birthday date, year number(1) DEFAULT 1, department_id number(3), CONSTRAINT stu_dept_fk FOREIGN KEY (department_id) REFERENCES department(id), CONSTRAINT year_chk CHECK (year in (1, 2, 3, 4)));

11. Possible solution • First, you should have department table CREATE TABLE department ( id number(3) PRIMARY KEY, name varchar2(50) NOT NULL, phone char(14) NOT NULL, fax char(14)); • Second, you should create student table

12. Possible solution (cont.1) CREATE TABLE student ( id number(5) PRIMARY KEY, name varchar2(50) NOT NULL, address varchar2(50), birthday date, year number(1) DEFAULT 1, department_id number(3), CONSTRAINT stu_dept_fk FOREIGN KEY (department_id) REFERENCES department(id), CONSTRAINT year_chk CHECK (year in (1, 2, 3, 4)));

13. Possible solution (cont.2) • Before you insert new record in student table, you have to insert new record in department table first due to referential integrity. Insert into department values (1, ‘Computer Engineering’,’051-320-5555’,’051-327-5555’) • Try to insert two different student records Insert into student values (1,’Hong’,’Busan’,’85/11/08’,2,1) : correct one Insert into student values (2,’Park’,’Seoul’,’84/12/25’,5,1) : incorrect one

14. Constraints (cont.9) • Ex) a similar CHECK constraint with different syntax CREATE TABLE student ( id number(5) PRIMARY KEY, name varchar2(50) NOT NULL, address varchar2(50), birthday date, year number(1) DEFAULT 1, department_id number(3), CONSTRAINT stu_dept_fk FOREIGN KEY (department_id) REFERENCES department(id), CONSTRAINT year_chk2 CHECK (year >= 1 AND year <= 4));

15. Exercise • Drop the student table and create new student table with new constraint for year check • Try to test by inserting 2 new records; Insert into student values (1,’Hong’,’Busan’,’85/11/08’,2,1) : correct one Insert into student values (2,’Park’,’Seoul’,’84/12/25’,5,1) : incorrect one

16. Maintaining Constraints • Constraints on tables and columns can be viewed from data dictionary ‘user_cons_columns’ • Ex) display table_name ,column_name, and constraint_name of table “STUDENT’ SQL>SELECT table_name ,column_name, constraint_name 2 FROM user_cons_columns 3 WHERE table_name = ‘STUDENT’;

17. Maintaining Constraints (cont1.) • ADD • Adds a new constraint • Syntax ALTER TABLE table_name ADD [CONSTRAINT constraint_name] constraint_type (column_name) [needed constraints]; • Constraint_type: PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE

18. Exercise • Add a constraint for address of students to have values of ‘Busan’, and ‘Seoul’ only.

19. Maintaining Constraints (cont.2) • DROP • Deletes a constraint • Syntax ALTER TABLE table_name DROP CONSTRAINT constraint_name; • When you want to delete primary key, you can just write, ALTER TABLE table_name DROP PRIMARY KEY;

20. Maintaining Constraints (cont.3) • DISABLE/ENABLE • Make a constraint disable or enable without deleting the constraint • Syntax ALTER TABLE table_name DISABLE CONSTRAINT constraint_name; ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

21. Maintaining Constraints (cont.4) • MODIFY • Modifies constraint • Ex) currently department(phone) has constraint of NOT NULL, make it NULL ALTER TABLE department MODIFY phone NULL;

22. Exercise VAR P BASE RELATION {P# P#, PNAME NAME, COLOR COLOR, WEIGHT WEIGHT, CITY CHAR} PRIMARY KEY {P#}; VAR SP BASE RELATION {S# S#, P# P#, QTY QTY} PRIMARY KEY {S#, P#}; FOREIGN KEY {S#} REFERENCES S FOREIGN KEY {S#} REFERENCES P; • Create 3 tables by using SQL for relvar S, P, and SP.(use char(4) for S#,P#; varchar2(20) for NAME, CHAR, COLOR; number(5,1) for WEIGHT; number(3) for QTY) VAR S BASE RELATION {S# S#, SNAME NAME, STATUS INTEGER, CITY CHAR} PRIMARY KEY {S#};

23. View • A virtual table derived from tables and other views • A kind of stored query • Syntax CREATE [OR REPLACE] VIEW view_name [ (column_name[,…])] AS <SELECT statement> [WITH CHECK OPTION [CONSTRAINT name]] [WITH READ ONLY] • WITH CHECK OPTION forces all constraints found in originating tables • WITH READ ONLY allows read only to the view

24. View (cont.1) • Ex) create a view that has list of students having ‘Lee’ as part of their name SQL> CREATE VIEW lee_list 2 AS 3 SELECT id, name, department_id, year 4 FROM student 5 WHERE name like ‘%Lee%’ 6 WITH CHECK OPTION

25. View (cont.2) • Ex) Try to enter the following tuples in the view ‘lee_list’, and see the result. INSERT INTO lee_list VALUES(1, ‘Lee Moon Soo’, 100, 4); INSERT INTO lee_list VALUES(2, ‘LimGguk Jung’, 100, 3); INSERT INTO lee_list VALUES(3, ‘Lee Young Hee’, 100, 5);

26. View (cont.3) • Ex) Make a view including the information of student id, student name, and department name. • Currently, attribute id, name exist in student table and department tablewe need table name before attribute name to distinguish CREATE VIEW std_dept AS SELECT student.id, student.name, department.name AS dept_name FROM student, department WHERE student.department_id = department.id;

27. View (cont.4) • Use ‘OR REPLACE’ option to modify existing view. • Ex) CREATE OR REPLACE VIEW std_dept AS SELECT student.name, department.name AS dept_name FROM student, department WHERE student.department_id = department.id;

28. View (cont.5) • Use ‘DROP VIEW’ statement to delete a view • Syntax DROP VIEW view_name;

29. Exercise • Create a view SPC having the attributes; {S#, CITY, P#, QTY} where the CITY value should come from table S. • Hint: use two tables, S and SP. • Insert the table values of S and SP, and see the view values of SPC.

30. Sequence • Makes unique sequence number automatically • Syntax CREATE SEQUENCE [schema.]sequence_name [INCREMENT BY n] : incrementing value, default=1 [START WITH n] : initial value, default=1 [MAXVALUE n|NOMAXVALUE] : max up to 1027 [MINVALUE n|NOMINVALUE] : min down to -1026 [CYCLE | NOCYCLE] : when reaches to max value, return to start value [CACHE | NOCACHE] : the number of numbers to generate in advance by Oracle server [ORDER | NOORDER] : sequence number is assigned based on request order for concurrent execution

31. Sequence (cont.1) • Ex) generate sequence numbers like 2, 5, 8, 11, 14, 17, 20, 0, 3 CREATE SEQUENCE seq_test INCREMENT BY 3 START WITH 2 MAXVALUE 20 MINVALUE 0 CYCLE NOCACHE;

32. Sequence (cont.2) • Ex) Use the sequence to enter data to table person CREATE TABLE person (id number(3) PRIMARY KEY, name varchar2(30) NOT NULL); INSERT INTO person VALUES(SEQ_TEST.NEXTVAL, ‘Hong-gil Dong’);

33. Sequence (cont.3) • Use ‘ALTER SEQUENCE’ to change previously defined sequence • Ex) ALTER SEQUENCE seq_test Increment by 1 Nomaxvalue;

34. Sequence (cont.4) • Deleting a sequence • Ex) DROP SEQUENCE seq_test;

35. Exercise • Create a sequence that can make supplier number (e.g., S + sequence number) and enter the following tuple values in S. • E.g.) the supplier numbers look like ‘S1’, ‘S2’, ‘S3’, etc.

36. Hints for the exercise • Use ‘||’ operator to concatenate characters • Use to_char(number) function to convert numbers to characters • Anyway, Oracle will covert numbers to characters automatically based on the context