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

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

View Full Document Right Arrow Icon
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).
Background image of page 1

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

View Full Document Right Arrow Icon
Background image of page 2
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 Right Arrow Icon
Ask a homework question - tutors are online