1 / 49

Chapter 6

Chapter 6. SQL. Agenda. Data Definition Language (DDL) Access Control. 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). Numeric Data Types.

clinth
Télécharger la présentation

Chapter 6

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. 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 (DD-MON-YY) • TIME [timeprecision (for second, default=0)] [with time zone (control hour and minute)]: hour:minute:second a.m. or p.m. • TIMESTAMP [timeprecision (for second, default=6] [with time zone (control hour and minute)]: date and times (DD-MON-YY hour:second:minute a.m. or p.m.)

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

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

  8. Domain Constraint - I • CHECK (search condition) • sex CHAR(1) NOT NULL CHECK (VALUE 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 Constraint - 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 Constraint • Methods • CHECK clause • UNIQUE clause • CREATE ASSERTION statement • CREATE ASSERTION assertion name CHECK (assertion condition): for defining attribute contraint • 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 (VALUE 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 (VALUE 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. Create A New Table Example • CREATE DOMAIN PropertyNo AS SMALLINT; • CREATE DOMAIN StaffNo AS CHAR(5) CHECK (VALUE IN (SELECT Sno FROM Staff); • CREATE DOMAIN Prent AS DECIMAL(6,2) CHECK (VLAUE BETWEEN 0 AND 9999.99); • CREATE TABLE PropertyForRent (Pno PropertyNo NOT NULL, Sno StaffNo CONSTRAINT StaffNotTooMuch CHECK (NOT EXIST (SELECT Sno FROM PropertyForRent GROUP BY Sno HAVING COUNT (*) >10)) NOT NULL, Rent Prent NOT NULL, CONSTRAINT pkPropertyForRent PRIMARY KEY (Pno), CONSTRAINT fkPropertyForRentStaff FOREIGN KEY (Sno) REFERENCES Staff (Sno));

  24. 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]

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

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

  27. 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));

  28. 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);

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

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

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

  32. Example • 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;

  33. Advantage of View • Data independence • Currency • Improved security • Reduced complexity • Convenience • Customization • Data integrity

  34. Disadvantage of View • Update restriction • Structure restriction (new attribute in the base table) • Performance (complex views)

  35. 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

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

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

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

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

  40. Student (Stuid, Stuname, Major, Credits) • Class (Course#, Facid, Sched, Room) • Faculty (Facid, Facname, Dept, Rank) • Enrollment (Course#, Stuid, Grade)

  41. Branch (Bno, Area, City) • Staff (Sno, Name, Position, Sex, Salary, Bno) • Property_for Rent (Pno, Area, City, Type, Rooms, Rent, Ono, Sno, Bno) • Renter (Rno, Name, Max_Rent) • Owner ( Ono, Name,) • Viewing (Rno, Pno, Date)

  42. Define City for Branch according to the following constraints: • Has to have a two character string value • Has to be equal to SF, NY, LA, or DC • Set default value to SF

  43. Define Sno for Staff according to the following constraints: • Has to have a three digits integer • Has to be between 111 and 999 • Has to be unique

  44. Define a domain citylocation for City according to the following constraints: • Has a two character string value • Has to be equal to SF, NY, LA, or DC • Set default value to SF • Define the City in the Branch or Property_for_Rent

  45. Define a domain noforsno according to the following constraints: • Has to have a three digits integer • Has to be equal to one of the sno in the Staff • Define the sno in the Property_for_rent using the domain noforsno

  46. Define an assertion that will limit 10 staff members in any branch

  47. Create staff table using following constraints • Sno is three digits integer, between 111 and 999, a primary key • Name and position are 30 alphanumeric fields • Sex has a default value (m) limits to m or f • Salary is between 50,000 and & 100,000 • Bno is equal to one value of bno in branch with 10 or less staff member, set null for delete, set cascade for update

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

  49. 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:

More Related