SQL Data Definition (CB Chapter 6)DDL OverviewDataType examplesIntegrity Enhancement FeaturesForeign Key constraintsCreate DomainCreate TableExample Relation (PhoneBook)PhoneBook table attributes…Phonebook table constraintsDomainsAltering a TableCreating a ViewNotes on the end of Ch. 6SQL Data Definition(CB Chapter 6)CPSC 356 DatabaseEllen WalkerHiram College(Includes figures from Database Systems by Connolly & Begg, © Addison Wesley 2002)DDL Overview•Data types–Char (n), varchar (n), integer, decimal(m,n), date, time•Domains (and domain constraints)•Entity and Referential Integrity Constraints–Primary key and Foreign Key–On Update and On Delete conditions•Other (enterprise) constraints–Assertions•Commands–Create, Alter, Drop–… Schema (database), Domain, Table, View–Schemas and Views cannot be alteredDataType examples•Char(5) ‘hello’, ‘today’, ‘12345’•Varchar(5) ‘hi’, ‘hello’, ‘ciao’•Integer 17, 12345, 100000000•Decimal(5,2) 99999.99, 25.00, 0.12•Date 2000-01-01, 2002-09-21•Time 13:01:00Integrity Enhancement Features•Required Data (NOT NULL)–… position VARCHAR(10) NOT NULL•Domain constraints (CHECK)–… sex CHAR … CHECK (sex IN (‘M’,’F’))•Entity Integrity–PRIMARY KEY (attributes)–UNIQUE (attributes) -- use for alternate keys•Referential Integrity–FOREIGN KEY …Foreign Key constraints•FOREIGN KEY (attributes) •REFERENCES (table)•ON DELETE (or ON UPDATE)–Cascade •Delete parent tuple, and all child tuples–Set Null •Delete parent tuple, set all child attributes null–Set Default•Delete parent tuple, set all child attributes to default–No Action•Do not deleteCreate Domain•CREATE DOMAIN name AS datatype [DEFAULT value ] [CHECK (condition) ]CREATE DOMAIN ClassYear AS CHAR[4] DEFAULT ‘2006’ CHECK (VALUE IN (‘2003’, ‘2004’, ‘2005’, ‘2006’);•ClassYear can now be used as a data typeCreate Table•CREATE TABLE name (Attribute DataType conditions,Attribute DataType conditions, …CONSTRAINT name check-condition, …PRIMARY KEY (attributes), Foreign key constraintsCheck conditions•)Example Relation (PhoneBook)First Last Dept Email Phone TitleObie Slotterbeck CS Obie 5275 ProfIrina Lomonosov CS LomonosovI 5002 InstEllen Walker CS WalkerEL 5250 ProfAngela Guercio CS GuercioA 6048 AsstJane Rose WEC RoseJP 5163 DeanMario Renzi ECN RenziMA 5420 ProfPhoneBook table attributes…CREATE TABLE PhoneBook (Email varchar(32)NOT NULL, First varchar(32),Last varchar(32)NOT NULL, Phone PhNum ,Title ProfTitle ,Dept varchar(4) ,Phonebook table constraintsPRIMARY KEY (Email) ,FOREIGN KEY (Dept) REFERENCES DepartmentON UPDATE CASCADE,ON DELETE SET NULL);Domains•CREATE DOMAIN PhNum AS IntegerCHECK (VALUE >5000 and VALUE < 6999);•CREATE DOMAIN ProfTitle AS CHAR(4)CHECK (VALUE IN (Inst, Asst, Assc, Prof, Dean));Altering a Table•ALTER TABLE–ADD attribute description–DROP attribute [RESTRICT | CASCADE]– ADD constraint conditions–DROP constraint–ALTER attribute SET DEFAULT value–ALTER attribute DROP DEFAULTCreating a View•CREATE VIEW name [(new column names)] AS SELECT …CREATE VIEW Locations ASSELECT fName, lName, dept, bldgFROM PhoneBook, DepartmentWHERE dept = Department.nameNotes on the end of Ch. 6•Section 6.5 will be covered along with Transactions•Section 6.6 will be covered along with