Chapter 6

1. Chapter 6 SQL

2. Agenda • Data Definition Language (DDL) • Access Control

3. String Data Types • Boolean data: true, false, null, unknown value (true > false) • Character data • Fixed length: CHAR(4) • Variable length: VARCHAR(30) • Bit data: binary string • BIT(4)

4. Numeric Data Types • NUMERIC or NUMBER [precision, [scale]] • DECIMAL or DEC [precision, [scale]] • INTEGER or INT • SMALLINT (32,767) • FLOAT [precision] • REAL • DOUBLE PRECISION

5. Datetime data • DATE: year, month, day • TIME [timeprecision (for second, default=0)] [with time zone (control hour and minute)]: hour, minute, second • TIMESTAMP [timeprecision (for second, default=6] [with time zone (control hour and minute)]: date and times

6. Integrity Enhancement Feature • Required data • Domain constraints • Entity integrity • Referential integrity • Enterprise constraints

7. Required Data • Position VARCHAR(10) NOT NULL

8. Domain Constraints - I • CHECK (search condition) • sex CHAR(1) NOT NULL CHECK (sex IN (‘m’, ‘f’)) • credit AS NUMBER(3) CHECK (VALUE BETWEEN 0 AND 999) • major AS CHAR(3) DEFAULT ‘mis’ CHECK (VALUE IN (‘mis’, ‘man’, ‘act’, ‘obe’))

9. Domain Constraints - II • CREATE DOMAIN domain name [AS] data type [DEFACULT default option] [CHECK (VALUE IN (search condition))] • CREATE DOMAIN sextype AS CHAR(1) DEFACULT ‘m’ CHECK (VALUE IN (‘m’,’f’)); • sex sextype NOT NULL • CREATE DOMAIN cnumber AS CHAR(2) CHECK (VALUE IN (SELECT cno FROM customer)); • cid cnumber NOT NULL • DROP DOMAIN domain name [RESTRICT l CASCADE] • DROP DOMAIN cnumber;

10. Entity Integrity • PRIMARY KEY key name or key names NOT NULL • PRIMARY KEY sno NOT NULL • sno INTEGER NOT NULL UNIQUE • PRIMARY KEY (sno, classno, sdate) NOT NULL

11. Referential Integrity • Referential action for ON UPDATE and ON DELETE • CASCADE • SET NULL • SET DEFAULT • NO ACTION • Examples • FOREIGN KEY hotelno REFERENCES hotel (hotelno) • FOREIGN KEY hotelno REFERENCES hotel (hotelno) ON DELETE SET NULL • FOREIGN KEY hotelno REFERENCES hotel (hotelno) ON UPDATE CASCADE

12. Enterprise Constraints • Methods • CHECK clause • UNIQUE clause • CREATE ASSERTION statement • CREATE ASSERTION assertion name CHECK (assertion condition) • CREATE ASSERTION toomuch CHECK (NOT EXIST (SELECT sno FROM enroll GROUP BY sno HAVING COUNT (*) >10);

13. SQL DDL • CREATE • ALTER • DROP

14. CREATE • SCHEMA • DOMAIN • TABLE • INDEX • VIEW

15. ALTER • TABLE • DOMAIN

16. DROP • SCHEMA • DOMAIN • TABLE • INDEX • VIEW

17. CREATE Schema • CREATE SCHEMA [name | AUTHORIZATION creator-id] • DROP SCHEMA name [RESTRICT | CASCADE] • Examples • CREATE SCHEMA mis150 AUTHORIZATION tsai; • DROP SCHEMA mis150;

18. SQL DDL For Table • CREATE TABLE table-name (colm data-type [NOT NULL][UNIQUE] [DEFAULT option][CHECK search-cond][,...],[PRIMARY KEY (colm [,colm])],[FOREIGN KEY (colm [,colm]) REFERENCES (parent-table)[colms])

19. Create A New Table Example • CREATE TABLE student (stuid NUMBER(5) NOT NULL CHECK (stuid BETWEEN 00001 AND 99999), stuname CHAR(10), major CHAR(10), credit NUMBER(3), CONSTRAINT pkstudent PRIMARY KEY (stuid));

20. Create A New Table Example • CREATE TABLE faculty (facid NUMBER (5) NOT NULL, facname CHAR(10), dept CHAR(10), rank CHAR(3) CHECK (rank IN (‘F’,’Aso’, ‘Ast’)), CONSTRAINT pkfaculty PRIMARY KEY (facid));

21. Create A New Table Example • CREATE TABLE class (course# NUMBER(5) NOT NULL, facid NUMBER(5), sched CHAR(10), room CHAR(10), CONSTRAINT pkclass PRIMARY KEY (course#), CONSTRAINT fkclassfaculty FOREIGN KEY (facid) REFERENCES faculty (facid));

22. Create A New Table Example • CREATE TABLE enrollment (course# NUMBER(5) NOT NULL, stuid NUMBER(5) NOT NULL, grade CHAR(10), CONSTRAINT pkenroll PRIMARY KEY (course#, stuid), CONSTRAINT fkenrollclass FOREIGN KEY (course#) REFERENCES class (course#), CONSTRAINT fkenrollstudent FOREIGN KEY (stuid) REFERENCES student (stuid));

23. SQL DDL For Table • DROP TABLE table-name [RESTRICT | CASCADE]; • ALTER TABLE table-name [ADD][MODIFY][COLUMN] colm data-type [NOT NULL][UNIQUE] [DEFAULT option][CHECK search-cond][,...][DROP [COLUMN]colm [RESTRICT|CASCADE]

24. Examples • ALTER TABLE enrollment MODIFY (grade NUMBER(3)); • ALTER TABLE enrollment ADD (datetake DATE not null); • DROP TABLE enrollment;

25. SQL DDL For Table • ALTER TABLE table-name [ADD [CONSTRAINT[constrnt-name]]table-constrnt][DROP CONSTRAINT constrnt-name][RESTRICT | CASCADE]

26. CREATE TABLE customer (lastname CHAR(20) NOT NULL, firstname CHAR(20) NOT NULL, customerid NUMBER(5) NOT NULL, address CHAR(10)); • ALTER TABLE customer ADD (CONTRAINT pkcustomer PRIMARY KEY (lastname, firstname)); • ALTER TABLE customer DROP CONSTRAINT pkcustomer; • ALTER TABLE customer ADD (CONTRAINT pkcustomer PRIMARY KEY (customerid));

27. Example • CREATE TABLE purchaseorder (ponumber CHAR (5) NOT NULL, podate DATE) CONTRAINT pkpurchasorder PRIMARY KEY (ponumber)); • CREATE TABLE puchaseorderlineitem (ponumber CHAR(5) NOT NULL, lineitem CHAR(5) NOT NULL, quantity NUMBER(5) NOT NULL, CONTRAINT pkpuchaseorderlineitem PRIMARY KEY (ponumber, lineitem)); • ALTER TABLE puchaseorderlineitem ADD (CONSTRAINT fkpolineitem FOREIGN KEY (ponumber) REFERENCES purchaseorder (ponumber)); • ALTER TABLE puchaseorderlineitem DROP (CONSTRAINT fkpolineitem);

28. SQL DDL For Index • CREATE [UNIQUE] INDEX index-name ON base-table-name (column [ASC | DESC] [, ... ) • DROP INDEX index-name

29. Examples • CREATE INDEX studentname ON student (stuname DESC); • CREATE INDEX majorcredit ON student (major, credit); • DROP INDEX majorcredit;

30. SQL DDL For View • CREATE VIEW view-name [(view-colm [, ...])] AS SELECT ... [WITH [CASCADE|LOCAL] CHECK OPTION] • DROP VIEW view-name [RESTRICT | CASCADE]

31. Examples • CREATE VIEW substudent (studentid, studentname, major) AS SELECT stuid, stuname, major FROM student; • CREATE VIEW productprofit (productid, productname, profit) AS SELECT productid, productname, sale-cost FROM inventory; • DROP VIEW productprofit;

32. Advantages of Views • Data independence • Currency • Improved security • Reduced complexity • Convenience • Customization • Data integrity

33. Disadvantages of Views • Update restriction • Structure restriction • Performance

34. Access Control • GRANT system-privilege TO role-name • GRANT object-privilege ON [owner.] object-name TO role-name • GRANT role-name TO user-name • REVOKE privilege FROM role-name • REVOKE role-name FROM user-name

35. System Privileges • CREATE TABLE • CREATE VIEW • CREATE USER • ALTER INDEX

36. Object Privileges • SELECT • INSERT • UPDATE • DELETE

37. Examples • GRANT CREATE TABLE, CREATE VIEW, CREATE USER TO manager; • GRANT SELECT, INSERT, UPDATE, DELETE ON student TO manager; • GRANT manger TO tsai;

38. REVOKE DELETE ON student FROM manager; • REVOKE manager FROM tsai;

39. Points To Remember • Structured Query Language (SQL) • Data Definition Language (DDL) • Access Control

40. Assignment • Review chapters 1-4, 5, and appendix C • Read chapters M-2, M-3 • Homework assignment • 6.10, 6.11(a, b, c, d), 6.12, 6.13, 6.14 • Due date: