csc4402_home3_ans_06

csc4402_home3_ans_06 - Answer Ket to CSC4402 Homework3 Due...

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

View Full Document Right Arrow Icon
Answer Ket to CSC4402 Homework3 Due Monday October 2, 2006 I. Following is a relational scheme for the Professor-Student database, describing professors, students, and the relationship "serving-on-the committee" between the professors and students. The primary keyfields are underlined: Prof: ( Pnum Pname City Dept) for Professors St: ( Snum Sname City Dept Degree Major_Pnum) for Students Com: ( Snum Pnum Hours) for committees In the above database, the data type for the Hours field in table Comis SMALLINT ,and all other fields have data type CHAR. Note that the field "Major_Pnum" denotes the Pnum for the student’s major professor and thus it is a foreign key intable St. Assume that every professor serves on one or more committees and every student has a committee of one or more professors. The student’smajor professor must be in his/her committee. Write CREATE TABLE statements to create the database for the above scheme (pay attention to the primary key and foreign key clauses). Answer: Create Table Prof (Pnum char(10), Pname Char(20), City Char(25), Dept Char(10), Primary key (Pnum)) Create Table St (Snum char(10), Sname char(20), City Char(25), Dept Char(10), Degree Char(6), Major_Pnum char(10) NOTNULL, Primary key(Snum), Foreign key (Major_Pnum) References Prof On Delete cascade) Create Table Com (Snum char(10), Pnum char(10), Hours SMALLINT, Primary key (Snum, Pnum), Foreign key (Snum) References St On Delete cascade, Foreign key (Pnum) References Prof On Delete cascade, Check (Hours > 0)) III. Write SQL statements for the following queries to the above database:
Background image of page 1

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

View Full DocumentRight Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 10/02/2011 for the course CS cs4402 taught by Professor Jianhua during the Fall '11 term at LSU.

Page1 / 5

csc4402_home3_ans_06 - Answer Ket to CSC4402 Homework3 Due...

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