Slide 1History of SQLStandardsComponents of SQLRelational Database ArchitectureSlide 6DDL CommandsCREATE TABLEIdentifiersDatatypesMySQL DatatypesCreating the Tables for the University DatabaseSlide 13New DatatypesConstraintsSlide 16IndexesCREATE INDEX CommandALTER TABLE CommandOther Changes to TablesSQL DMLSELECT StatementUniversity DatabaseSimple retrieval with conditionAll columnsRetrieval without conditionsEntire TableORDER BY and ASMultiple ConditionsSELECT using multiple tablesNatural Join with OrderingNatural Join of three tablesUse of AliasesJoin without Equality ConditionSubqueries with EqualityFour Table ExampleExampleSubqueries using INNested SubqueriesQueries using EXISTSQueries using NOT EXISTSSlide 42UPDATE OperatorUPDATE ExamplesSlide 45Slide 46INSERT OperatorINSERT ExamplesSlide 49Slide 50DELETE OperatorDELETE ExamplesSlide 53Relational ViewsCreate ViewUsing ViewsVIEW ExamplesActive Databases-ConstraintsTriggersTrigger SyntaxSlide 61Slide 62Slide 63Ending TransactionsSQL ProgrammingShared VariablesSingle-row Embedded SQL SELECTInsert in Embedded SQLDelete in Embedded SQLUpdate in Embedded SQLError Handling using WHENEVERUsing CursorsCursor ExampleUpdate and Delete Using a CursorUpdate and Delete ExamplesDynamic SQLAPI, ODBC and JDBCODBC/JDBC ComponentsSQL/PSMSQL/PSM Procedures and FunctionsDeclaration, Assignment, Control Statements in SQL/PSMSystem CatalogUsing Oracle Data Dictionary-ExamplesIBM DB2 UDB CatalogCSC 3800 Database Management SystemsTime: 1:30 to 2:20 Meeting Days: MWF Location: Oxendine 1237BTextbook: Databases Illuminated, Author: Catherine M. Ricardo, 2004, Jones & Bartlett PublishersFall 2009Chapter 6Relational Database Management Systems and SQL Dr. Chuck LillieHistory of SQLHistory of SQLProposed by E.F.Codd in his 1970 paperUsed in System R, IBM’s research relational database in early 1970s-D.D. Chamberlin et al at IBM Research Center, San Jose, CaliforniaUsed in Oracle, released in late 1970sIncorporated into IBM’s SQL/DS in 1981, and DB2 in 1983Also used in Microsoft SQL Server, MySQL, Informix, Sybase, dBase, Paradox, r:Base, FoxPro, and othersStandardsStandardsANSI and ISO published SQL standards in 1986, called SQL-1Minor revision, SQL-89Major revision, SQL-2,1992SQL-3, multi-part revision, contains SQL:1999, which included object-oriented (OO) facilities, and user defined datatypes (UDTs)Most vendors support standard, but have slight variations of their ownComponents of SQLComponents of SQLData definition language - DDLData manipulation language - DMLAuthorization language – grant privileges to usersRelational Database Relational Database ArchitectureArchitectureSeparate external, logical, internal modelsBase tables and indexes form logical levelIndexes are B+ trees or B trees – maintained by systemRelational views (external level) are derived from base tablesUsers see views or base tables, or combinationInternal level - filesSQL supports dynamic database definition-can modify structures easilySee Figure 6.1External LevelConceptual levelInternal levelLogical data independencePhysical data independenceUser 1 User nUser 2 User 3View A View B View C View KBase table1 + indexesBase table2 + indexesBase table3 + indexesBase tablem + indexesFile 1 File 2 File pFigure 6.1 Three level architecture for relational databasesDDL CommandsDDL CommandsCREATE TABLECREATE INDEXALTER TABLERENAME TABLEDROP TABLEDROP INDEX Also – CREATE VIEWCREATE TABLECREATE TABLECREATE TABLE base-table-name (colname datatype [column constraints – NULL/NOT NULL, DEFAULT…, UNIQUE, CHECK…, PRIMARY KEY],[,colname datetype [column constraints …]]...[table constraints – PRIMARY KEY…, FOREIGN KEY…, UNIQUE…, CHECK…][storage specifications]);IdentifiersIdentifiersNo SQL keywords may be usedTable name must be unique within the database For each column, the user must specify a name that is unique within the tableDatatypesDatatypesEach column must have a datatype specifiedStandards include various numeric types, fixed-length and varying-length character strings, bit strings, and user-defined types available datatypes vary from DBMS to DBMS Oracle types include CHAR(N), VARCHAR2(N), NUMBER(N,D), DATE, and BLOB (binary large object) and othersDB2 types include SMALLINT, INTEGER, BIGINT, DECIMAL/NUMERIC, REAL, DOUBLE, CHAR(N), VARCHAR(N), LONG VARCHAR, CLOB, GRAPHIC, DBCLOB, BLOB, DATE, TIME, and TIMESTAMP SQL Server includes types of NUMERIC, BINARY, CHAR, VARCHAR DATETIME, MONEY, IMAGE, and others Access supports several types of NUMBER, as well as TEXT, MEMO, DATE/TIME, CURRENCY, YES/NO, and othersMySQL DatatypesMySQL DatatypesVARCHARTINYINTTEXTDATESMALLINTMEDIUMINTINTBIGINTFLOATDOUBLEDECIMALDATETIMETIMESTAMPTIMEYEARCHARTINYBLOBBLOBMEDIUMBLOBMEDIUMTEXTLONGBLOBLONGTESTENUMSETBOOLBINARYVARBINARYCreating the Tables for the University Creating the Tables for the University DatabaseDatabaseCREATE TABLE Student(stuId CHAR(6),lastName CHAR(20) NOT NULL,firstName CHAR(20) NOT NULL,major CHAR(10),credits SMALLINT DEFAULT 0,CONSTRAINT Student_stuId_pk PRIMARY KEY (stuId),CONSTRAINT Student_credits_cc CHECK (credits>=0 AND credits < 150)); CREATE TABLE Faculty (facId CHAR(6),name CHAR(20) NOT NULL,department CHAR(20) NOT NULL,rank CHAR(10),CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId));Creating the Tables for the Creating the Tables for the University DatabaseUniversity DatabaseCREATE TABLE Class (classNumber CHAR(8),facId CHAR(6) NOT NULL,schedule CHAR(8),room CHAR(6),CONSTRAINT Class_classNumber_pk PRIMARY KEY (classNumber),CONSTRAINT Class_facId_fk FOREIGN KEY (facId) REFERENCES Faculty (facId)) ;CREATE TABLE Enroll (stuId CHAR(6),classNumber CHAR(8),grade CHAR(2),CONSTRAINT Enroll_classNumber_stuId_pk PRIMARY KEY (classNumber, stuId),CONSTRAINT Enroll_classNumber_fk FOREIGN KEY (classNumber) REFERENCES Class (classNumber),CONSTRAINT Enroll_stuId_fk FOREIGN KEY (stuId) REFERENCES Student(stuId));New DatatypesNew DatatypesSQL:1999 provides new User Data Types (UDT)Can define structured UDTsAlso new DISTINCT types e.g.CREATE DOMAIN creditValues INTEGER DEFAULT 0 CHECK (VALUE >=0 AND VALUE <150);New type can then be used in defining columnsIn Student, can write credits creditValues,...Can’t compare values of two different DISTINCT types, even if underlying