Integrity Constraints in Create Table not null primary key (A1,...A) foreign key (Am,...An)references r 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), primary key declaration on an attribute automatically ensures not null Database System Concepts-6th Edition 3.7 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 3.7 ©Silberschatz, Korth and Sudarshan th Edition Integrity Constraints in Create Table not null primary key (A1 , ..., An ) foreign key (Am, ..., An ) references r 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); primary key declaration on an attribute automatically ensures not null
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); Note:sec_id can be dropped from primary key above,to ensure a student cannot be registered for two sections of the same course in the same semester Database System Concepts-6th Edition 3.8 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 3.8 ©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); Note: sec_id can be dropped from primary key above, to ensure a student cannot be registered for two sections of the same course in the same semester
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-6th Edition 3.9 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 3.9 ©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 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. Database System Concepts-6th Edition 3.10 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 3.10 ©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
Basic Query Structure A typical SQL query has the form: select A1,A2,...,A from n,r2,...,Im where P A,represents an attribute R;represents a relation P is a predicate. The result of an SQL query is a relation. Database System Concepts-6th Edition 3.11 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 3.11 ©Silberschatz, Korth and Sudarshan th Edition Basic Query Structure A typical SQL query has the form: select A1 , A2 , ..., An from r1 , r2 , ..., rm where P Ai represents an attribute Ri represents a relation P is a predicate. The result of an SQL query is a relation