Lecture 08 - SQL

Lecture 08 - SQL - Chapter 8 SQL-99: Schema Definition,...

Info iconThis preview shows pages 1–9. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Chapter 8 SQL-99: Schema Definition, Constraints, & Queries and Views Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Database Systems Chapter Outline Data Definition, Constraints, and Schema Changes CREATE, DROP, and ALTER the descriptions of the tables (relations) of a database Referential Integrity Options Retrieval Queries in SQL SELECT <attribute list> FROM <table list> [ WHERE <condition>] [ GROUP BY <grouping attribute(s)>] [ HAVING <group condition>] [ ORDER BY <attribute list>] Specifying Updates in SQL INSERT , DELETE , and UPDATE Assertions, Triggers & Views Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Slide 8- 3 CREATE TABLE Specifies a new base relation by giving it: a name & specifying each of its attributes & their data types ( INTEGER, FLOAT, DECIMAL(i,j), CHAR(n), VARCHAR(n) ) A constraint NOT NULL may be specified on an attribute CREATE TABLE DEPARTMENT ( DNAME VARCHAR(10) NOT NULL , DNUMBER INTEGER NOT NULL , MGRSSN CHAR(9), MGRSTARTDATE CHAR(9) ); Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Slide 8- 4 CREATE TABLE In SQL2, can use the CREATE TABLE command for specifying: Primary Key attributes, Secondary Keys, & Referential Integrity Constraints (Foreign Keys). CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMP ); Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Slide 8- 5 DROP TABLE Used to remove a relation (base table) and its definition The relation can no longer be used in queries, updates, or any other commands since its description no longer exists Example : DROP TABLE DEPENDENT; Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Slide 8- 6 ALTER TABLE Used to add an attribute to one of the base relations The new attribute will have NULLs in all the tuples of the relation right after the command is executed; hence, the NOT NULL constraint is not allowed for such an attribute Example : ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12); The database users must still enter a value for the new attribute JOB for each EMPLOYEE tuple. This can be done using the UPDATE command. Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Slide 8- 7 REFERENTIAL INTEGRITY OPTIONS We can specify RESTRICT, CASCADE, SET NULL or SET DEFAULT on referential integrity constraints (foreign keys) CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMP ON DELETE SET DEFAULT ON UPDATE CASCADE); Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Slide 8- 8 REFERENTIAL INTEGRITY OPTIONS (continued) CREATE TABLE EMP( ENAME VARCHAR(30) NOT NULL, ESSN CHAR(9), BDATE DATE, DNO INTEGER DEFAULT 1, SUPERSSN CHAR(9), PRIMARY KEY (ESSN), FOREIGN KEY (DNO) REFERENCES DEPT(...
View Full Document

This note was uploaded on 03/19/2012 for the course CMP 202 taught by Professor Hanem during the Spring '12 term at Cairo University.

Page1 / 82

Lecture 08 - SQL - Chapter 8 SQL-99: Schema Definition,...

This preview shows document pages 1 - 9. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online