PowerPoint PresentationChapter 8Data Definition, Constraints, and Schema ChangesCREATE TABLESlide 5DROP TABLEALTER TABLEFeatures Added in SQL2 and SQL-99CREATE SCHEMAREFERENTIAL INTEGRITY OPTIONSREFERENTIAL INTEGRITY OPTIONS (continued)Additional Data Types in SQL2 and SQL-99Additional Data Types in SQL2 and SQL-99 (cont.)Retrieval Queries in SQLRetrieval Queries in SQL (cont.)Relational Database Schema--Figure 5.5Populated Database--Fig.5.6Simple SQL QueriesSimple SQL Queries (cont.)Slide 20Aliases, * and DISTINCT, Empty WHERE-clauseALIASESALIASES (cont.)UNSPECIFIED WHERE-clauseUNSPECIFIED WHERE-clause (cont.)USE OF *USE OF DISTINCTSET OPERATIONSSET OPERATIONS (cont.)NESTING OF QUERIESNESTING OF QUERIES (cont.)CORRELATED NESTED QUERIESCORRELATED NESTED QUERIES (cont.)Slide 34Slide 35THE EXISTS FUNCTIONTHE EXISTS FUNCTION (cont.)Slide 38EXPLICIT SETSNULLS IN SQL QUERIESJoined Relations Feature in SQL2Joined Relations Feature in SQL2 (cont.)Slide 43Slide 44AGGREGATE FUNCTIONSAGGREGATE FUNCTIONS (cont.)Slide 47GROUPINGGROUPING (cont.)Slide 50THE HAVING-CLAUSETHE HAVING-CLAUSE (cont.)SUBSTRING COMPARISONSUBSTRING COMPARISON (cont.)Slide 55ARITHMETIC OPERATIONSORDER BYORDER BY (cont.)Summary of SQL QueriesSummary of SQL Queries (cont.)Specifying Updates in SQLINSERTINSERT (cont.)Slide 64Slide 65Slide 66DELETEDELETE (cont.)UPDATEUPDATE (cont.)Slide 71Copyright © 2004 Pearson Education, Inc.Copyright © 2004 Pearson Education, Inc.Chapter 8SQL-99: Schema Definition, Basic Constraints, and QueriesSlide 8-3Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Ramez Elmasri and Shamkant NavatheData Definition, Constraints, and Schema ChangesUsed to CREATE, DROP, and ALTER the descriptions of the tables (relations) of a databaseSlide 8-4Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Ramez Elmasri and Shamkant NavatheCREATE TABLESpecifies a new base relation by giving it a name, and specifying each of its attributes and their data types (INTEGER, FLOAT, DECIMAL(i,j), CHAR(n), VARCHAR(n))A constraint NOT NULL may be specified on an attributeCREATE TABLE DEPARTMENT( DNAME VARCHAR(10) NOT NULL,DNUMBER INTEGER NOT NULL,MGRSSN CHAR(9),MGRSTARTDATE CHAR(9) );Slide 8-5Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Ramez Elmasri and Shamkant NavatheCREATE TABLEIn SQL2, can use the CREATE TABLE command for specifying the primary key attributes, secondary keys, and referential integrity constraints (foreign keys). Key attributes can be specified via the PRIMARY KEY and UNIQUE phrasesCREATE 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 );Slide 8-6Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Ramez Elmasri and Shamkant NavatheDROP TABLEUsed to remove a relation (base table) and its definitionThe relation can no longer be used in queries, updates, or any other commands since its description no longer existsExample:DROP TABLE DEPENDENT;Slide 8-7Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Ramez Elmasri and Shamkant NavatheALTER TABLEUsed to add an attribute to one of the base relationsThe 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 attributeExample: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.Slide 8-8Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Ramez Elmasri and Shamkant NavatheFeatures Added in SQL2 and SQL-99CREATE SCHEMAREFERENTIAL INTEGRITY OPTIONSSlide 8-9Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Ramez Elmasri and Shamkant NavatheCREATE SCHEMASpecifies a new database schema by giving it a nameSlide 8-10Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Ramez Elmasri and Shamkant NavatheREFERENTIAL INTEGRITY OPTIONSWe 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 EMPON DELETE SET DEFAULT ON UPDATE CASCADE );Slide 8-11Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Ramez Elmasri and Shamkant NavatheREFERENTIAL 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 ON DELETE SET DEFAULT ON UPDATE CASCADE,FOREIGN KEY (SUPERSSN) REFERENCES EMP ON DELETE SET NULL ON UPDATE CASCADE );Slide 8-12Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Ramez Elmasri and Shamkant NavatheAdditional Data Types in SQL2 and SQL-99Has DATE, TIME, and TIMESTAMP data typesDATE:–Made up of year-month-day in the format yyyy-mm-ddTIME:–Made up of hour:minute:second in the format hh:mm:ssTIME(i):–Made up of hour:minute:second plus i additional digits specifying fractions of a second–format is hh:mm:ss:ii...iTIMESTAMP:–Has both DATE and TIME componentsSlide 8-13Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Ramez Elmasri and Shamkant NavatheAdditional Data Types in SQL2 and SQL-99 (cont.)INTERVAL:–Specifies a relative value rather than an absolute value–Can be DAY/TIME intervals or YEAR/MONTH intervals–Can be positive or negative when added to or subtracted from an absolute value, the result is an absolute valueSlide 8-14Elmasri and Navathe, Fundamentals of Database Systems, Fourth EditionCopyright © 2004 Ramez Elmasri and Shamkant NavatheRetrieval Queries in SQLSQL has one basic statement for retrieving information from a database; the SELECT statementThis is not the same as the SELECT operation of the relational algebraImportant distinction between SQL and the formal relational model; SQL allows a table (relation) to have two or more tuples that are
View Full Document
Unlocking...