1 / 36

Practice 3

Practice 3. 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);.

terra
Télécharger la présentation

Practice 3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related