Dbm End of Chapter Solution 6

Only available on StudyMode
  • Download(s) : 2296
  • Published : May 26, 2013
Open Document
Text Preview
1. Write a database description for each of the relations shown, using SQL DDL. CREATE TABLE Student_T(StudentIDINTEGERNOT NULL,StudentNameVARCHAR2(25),CONSTRAINT Student_PK PRIMARY KEY (StudentID));CREATE TABLE Faculty_T(FacultyIDINTEGERNOT NULL,FacultyNameVARCHAR2(25),CONSTRAINT Faculty_PK PRIMARY KEY (FacultyID));CREATE TABLE Course_T(CourseIDCHAR(8)NOT NULL,CourseNameVARCHAR2(15),CONSTRAINT Course_PK PRIMARY KEY (CourseID));CREATE TABLE Qualified_T(FacultyIDINTEGERNOT NULL,CourseIDCHAR(8)NOT NULL,DateQualifiedDATE,CONSTRAINT Qualified_PK PRIMARY KEY (FacultyID, CourseID),CONSTRAINT Qualified_FK1 FOREIGN KEY (FacultyID) REFERENCES Faculty_T(FacultyID),CONSTRAINT Qualified_FK2 FOREIGN KEY (CourseID) REFERENCES Course_T(CourseID));CREATE TABLE Section_T(SectionNoINTEGERNOT NULL,SemesterCHAR(7)NOT NULL,CourseIDCHAR(8)NOT NULL,CONSTRAINT Section_PK PRIMARY KEY (SectionNo, Semester, CourseID),CONSTRAINT Section_FK FOREIGN KEY (CourseID) REFERENCES Course_T(CourseID));CREATE TABLE Registration_T(StudentID INTEGERNOT NULL,SectionNoINTEGERNOT NULL,SemesterCHAR(7)NOT NULL,CONSTRAINT Registration_PK PRIMARY KEY (StudentID, SectionNo, Semester),CONSTRAINT Registration_FK1 FOREIGN KEY (StudentID) REFERENCES Student_T(StudentID),CONSTRAINT Registration_FK2 FOREIGN KEY (SectionNo) REFERENCES Section_T(SectionNo),CONSTRAINT Registration_FK3 FOREIGN KEY (Semester) REFERENCES Section_T(Semester));|

4. Write SQL data definition commands for each of the following queries: a. How would you add an attribute, Class, to the Student table? ALTER TABLE Student_T
ADD COLUMN Class;

b. How would you remove the Registration table?
DROP TABLE Registration_T;

c. How would you change the FacultyName field from 25 characters to 40 characters? ALTER TABLE Faculty_T
ALTER COLUMN FacultyName TYPE VARCHAR2(40);

6. Write SQL queries to answer the following questions:
a. Which students have an ID number that is less than...
tracking img