Create Table Construct An SQL relation is defined using the create table command: create table r (A1 D1:A2 D2:...An Dn (integrity-constraint ) (integrity-constraint)) ris the name of the relation each A,is an attribute name in the schema of relation r D;is the data type of values in the domain of attribute A; Example: create table instructor( D char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2)) Database System Concepts-7th Edition 3.7 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 3.7 ©Silberschatz, Korth and Sudarshan th Edition Create Table Construct ▪ An SQL relation is defined using the create table command: create table r (A1 D1 , A2 D2 , ..., An Dn , (integrity-constraint1 ), ..., (integrity-constraintk )) • r is the name of the relation • each Ai is an attribute name in the schema of relation r • Di is the data type of values in the domain of attribute Ai ▪ Example: create table instructor ( ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2))
Integrity Constraints in Create Table Types of integrity constraints 。primary key(A,,An) foreign key (Am,...An)references r 。not null ■ SQL prevents any update to the database that violates an integrity constraint. ■ Example: create table instructor( ID char(5), name varchar(20)not null, dept name varchar(20), salary numeric(8,2), primary key(ID), foreign key(dept_name)references department), Database System Concepts-7th Edition 3.8 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 3.8 ©Silberschatz, Korth and Sudarshan th Edition Integrity Constraints in Create Table ▪ Types of integrity constraints • primary key (A1 , ..., An ) • foreign key (Am, ..., An ) references r • not null ▪ SQL prevents any update to the database that violates an integrity constraint. ▪ Example: create table instructor ( ID char(5), name varchar(20) not null, dept_name varchar(20), salary numeric(8,2), primary key (ID), foreign key (dept_name) references department);
And a Few More Relation Definitions create table student( ID varchar(5), name varchar(20)not null, dept name varchar(20). tot cred numeric(3,0), primary key (ID), foreign key(dept name)references department): create table takes( ID varchar(5). course id varchar(8), sec_id varchar(8), semester varchar(6), year numeric(4,0), grade varchar(2), primary key (ID,course_id,sec_id,semester,year), foreign key(ID)references student, foreign key(course id,sec id,semester,year)references section); Database System Concepts-7th Edition 3.9 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 3.9 ©Silberschatz, Korth and Sudarshan th Edition And a Few More Relation Definitions ▪ create table student ( ID varchar(5), name varchar(20) not null, dept_name varchar(20), tot_cred numeric(3,0), primary key (ID), foreign key (dept_name) references department); ▪ create table takes ( ID varchar(5), course_id varchar(8), sec_id varchar(8), semester varchar(6), year numeric(4,0), grade varchar(2), primary key (ID, course_id, sec_id, semester, year) , foreign key (ID) references student, foreign key (course_id, sec_id, semester, year) references section);
And more still create table course course id varchar(8), title varchar(50), dept name varchar(20). credits numeric(2,0). primary key (course_id), foreign key(dept_name)references department); Database System Concepts-7th Edition 3.10 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 3.10 ©Silberschatz, Korth and Sudarshan th Edition And more still ▪ create table course ( course_id varchar(8), title varchar(50), dept_name varchar(20), credits numeric(2,0), primary key (course_id), foreign key (dept_name) references department);
Updates to tables ■ Insert insert into instructor values ('10211','Smith','Biology,66000); ■Delete Remove all tuples from the student relation delete from student Drop Table ·drop table r ■Alter ·alter table radd A D where A is the name of the attribute to be added to relation r and D is the domain of A. All exiting tuples in the relation are assigned null as the value for the new attribute. ·alter table r drop A where A is the name of an attribute of relation r Dropping of attributes not supported by many databases. Database System Concepts-7th Edition 3.11 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 3.11 ©Silberschatz, Korth and Sudarshan th Edition Updates to tables ▪ Insert • insert into instructor values ('10211', 'Smith', 'Biology', 66000); ▪ Delete • Remove all tuples from the student relation ▪ delete from student ▪ Drop Table • drop table r ▪ Alter • alter table r add A D ▪ where A is the name of the attribute to be added to relation r and D is the domain of A. ▪ All exiting tuples in the relation are assigned null as the value for the new attribute. • alter table r drop A ▪ where A is the name of an attribute of relation r ▪ Dropping of attributes not supported by many databases