# discussion - 4.3.b Sol It is not possible for D to be not...

This preview shows pages 1–2. Sign up to view the full content.

Practical exercise chapter 4 Problem 4.6: Sol: Create table student (ID varchar(5), name varchar(20)not null, dept_name varchar(20), tot_cred numeric(3,0)check (tot_cred>=0),primary key(ID), foreign key(dept_name) references department on delete set null); Create table takes(ID varchar(5),course_id varchar(8), section_id varchar(8), semester varchar(6),year numeric(4,0), grade varchar(2), primary key(ID,course_id,section_id,semester,year), foreign key(course_id,section_id,semester,year)refernces section on delete cascade,foreign key(ID) references student on delete cascade); Create table advisor(i_id varchar(5),s_id varchar(5),primary key(s_ID), foreign key(i_ID) refernces instructor(ID) on delete set nul, foreign key(s_ID) refrences student(ID) on delete cascade); Create table prereg(course_id varchar(8),prereg_id varchar(8), primary key(course_id,prereq_id),foreign key(course_id) references course on delete cascade, foreign key(prereq_id) references course); Problem 4.3: 4.3.a Sol: Consider r=(a,b), s=(b1,c1),t=(b,d). The second expersion would give (a,b,NULL,d).

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 4.3.b: Sol: It is not possible for D to be not null while C is null in the result of the first expression, since in the sub expressions S natural left outer join t, it is not for C to be null while D is not null. In the overall expression C can be null if and only id some r tuples does not have any matching B values. Problem 4.4 4.4.a: Sol: Instructor = {(12345,’Guass’,’Physics’,10000)} Teaches = {(12345,’EE321’,1,’spring’, 2009)} Course = {(‘EE321’,’Magnetism’,’Elec.eng.’,6)} 4.4.b: Sol: Instructor have not taught a single course, should have number of sections as 0 in the query result. 4.4.c: Sol: Select * from teaches natural join instructor; In the above query, we would lose some sections if teaches.Id is allowed to be NULL and such tuples exist. If, just because teaches.ID is a foreign key to instructor, we did not create such a tuple the error in the above query not be detected....
View Full Document

{[ snackBarMessage ]}

### Page1 / 2

discussion - 4.3.b Sol It is not possible for D to be not...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online