SQL - Part 3TopicsExample RelationsExample Relation InstancesSet OperationsPractice QuestionsSQL Query SummarySQL DDL OverviewDefining a DatabaseCREATE, ALTER, and DROPSQL CREATE TABLESQL IdentifiersSQL Data TypesData TypesData TypesSlide 16SQL CREATE TABLE ExampleSQL ConstraintsSQL Constraints - Entity IntegritySQL Constraints - Referential IntegritySQL Referential Integrity ExampleSQL Referential Integrity and UpdatesSQL Referential Integrity Example (2)Creating the Example DatabaseALTER TABLEALTER TABLE ExamplesDROP TABLEDatabase UpdatesUPDATE StatementDELETE StatementSQL - Part 3Much of the material presented in these slides was developed by Dr. Ramon Lawrence at the University of IowaTopicsSet operators in SQLDDL operations using SQLExample RelationsRelations:Emp (eno, ename, bdate, title, salary, supereno, dno)Proj (pno, pname, budget, dno)Dept (dno, dname, mgreno)WorksOn (eno, pno, resp, hours)Foreign keys:Emp: Emp.supereno to Emp.eno, Emp.dno to Dept.dnoProj: Proj.dno to Dept.dnoDept: Dept.mgreno to Emp.enoWorksOn: WorksOn.eno to Emp.eno, WorksOn.pno to Proj.pnoExample Relation InstancesSet OperationsThe basic set operations of union, intersection, and difference can be performed in SQL. They are generally used to combine the results of two separate SQL queries.UNION defined in SQL1, INTERSECT, EXCEPT in SQL2Example: Return the employees who are either directly supervised by 'R. Davis' or directly supervised by 'M. Smith'.(SELECT E.enameFROM emp as E, emp as MWHERE E.supereno = M.eno and M.ename='R. Davis')UNION(SELECT E.enameFROM emp as E, emp as MWHERE E.supereno = M.eno and M.ename='M. Smith');Our version of MySQL does not support INTERSECT and EXCEPTPractice QuestionsRelational database schema:Emp (eno, ename, bdate, title, salary, supereno, dno)Proj (pno, pname, budget, dno)Dept (dno, dname, mgreno)WorksOn (eno, pno, resp, hours)1) Return the employees who either manage a department or manage another employee.2) Return the employees who manage an employee but do not manage a department.SQL Query SummaryThe general form of the SELECT statement is:SELECT <attribute list>FROM <table list>[WHERE (condition)][GROUP BY <grouping attributes>][HAVING <group condition>][ORDER BY <attribute list>]Clauses in square brackets ([,]) are optional.There are often numerous ways to express the same query in SQL.Set operators can be used to combine queries.SQL DDL OverviewSQL contains a data definition language (DDL) that allows users to:create, modify, and drop database objectsdefine and enforce integrity constraintsMore…Defining a DatabaseThere is typically a hierarchy of database objects that you can create, alter, and destroy.SQL does not standardize how to create a database. A database often contains one or more catalogs, each of which contains a set of schemas.To make things more complicated, many DBMSs do not implement everything and rename things. e.g. A database IS a schema for MySQL (there is no CREATE SCHEMA command).CREATE, ALTER, and DROPDatabase objects are created, modified, or removed from the system using the keywords CREATE, ALTER, and DROP.The types of database objects include TABLE, VIEW, DATABASE, and INDEX.Note that INDEX is not part of the SQL standard and we won’t explore them.SQL CREATE TABLEThe CREATE TABLE command is used to create a table in the database. A table consists of a table name, a set of fields with their names and data types, and specified constraints.The general form is:CREATE TABLE tableName (attr1Name attr1Type [attr1_constraints],attr2Name attr2Type [attr2_constraints],...attrMName attrMType [attrM_constraints],[primary and foreign key constraints]);Covered in Ch 3 of your text (see page 75 for the BNF)SQL IdentifiersIdentifiers are used to identify objects in the database such as tables, views, and columns.The identifier is the name of the database object.An SQL identifier (name) must follow these rules:only contain upper or lower case characters, digits, and underscore ("_") characterbe no longer than 128 charactersDB vendors may impose stricter limits than this.must start with a lettercannot contain spacesSQL Data TypesIn the relational model, each attribute had an associated domain of values.In SQL, each column (attribute) has a data type that limits the values that it may store. The standard SQL data types are similar to their programming language equivalents.There are three main types : text, numbers, and Dates/Times. You can Define Data Types but we won’t cover that now.Data TypesTEXT TYPES CHAR( ) A fixed section from 0 to 255 characters long.VARCHAR( ) A variable section from 0 to 255 characters long.TINYTEXT A string with a maximum length of 255 characters.TINYBLOB A string with a maximum length of 255 characters.TEXT A string with a maximum length of 65535 characters.BLOB A string with a maximum length of 65535 characters.MEDIUMTEXT A string with a maximum length of 16777215 characters.MEDIUMBLOB A string with a maximum length of 16777215 characters.LONGTEXT A string with a maximum length of 4294967295 characters.LONGBLOB A string with a maximum length of 4294967295 characters.The ( ) brackets allow you to enter a maximum number of characters will be used in the column.Data TypesNUMBER TYPES TINYINT( ) 128 to 127 normal; 0 to 255 UNSIGNED.SMALLINT( ) 32768 to 32767 normal; 0 to 65535 UNSIGNED.MEDIUMINT( )8388608 to 8388607 normal; 0 to 16777215 UNSIGNED.INT( )2147483648 to 2147483647 normal; 0 to 4294967295 UNSIGNED.BIGINT( )9223372036854775808 to 9223372036854775807 normal; 0 to 8446744073709551615 UNSIGNED.FLOAT A small number with a floating decimal point.DOUBLE( , ) A large number with a floating decimal point.DECIMAL( , ) A DOUBLE stored as a string, allowing for a fixed decimal point.Data TypesDATE TYPES DATE YYYY-MM-DD.DATETIME YYYY-MM-DD HH:MM:SS.TIMESTAMP YYYYMMDDHHMMSS.TIME HH:MM:SS.SQL CREATE TABLE ExampleThe CREATE TABLE command for the Emp relation:CREATE TABLE Emp (eno CHAR(5) NOT NULL,ename VARCHAR(30),bdate DATE,title CHAR(2),salary DECIMAL(9,2),supereno CHAR(5),dno CHAR(5),PRIMARY KEY (eno)FOREIGN KEY (dno) REFERENCES Dept(dno)ON DELETE SET NULL ON UPDATE CASCADE);SQL ConstraintsConstraints are specified in CREATE and ALTER TABLE statements.Types of constraints:Required data - To specify