Dbm End of Chapter Solution 6

1. Write a database description for each of the relations shown, using SQL DDL. CREATE TABLE Student_T (StudentID INTEGER NOT NULL, StudentName VARCHAR2(25),CONSTRAINT Student_PK PRIMARY KEY (StudentID));CREATE TABLE Faculty_T (FacultyID INTEGER NOT NULL, FacultyName VARCHAR2(25),CONSTRAINT Faculty_PK PRIMARY KEY (FacultyID));CREATE TABLE Course_T (CourseID CHAR(8) NOT NULL, CourseName VARCHAR2(15),CONSTRAINT Course_PK PRIMARY KEY (CourseID));CREATE TABLE Qualified_T (FacultyID INTEGER NOT NULL, CourseID CHAR(8) NOT NULL, DateQualified DATE,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 (SectionNo INTEGER NOT NULL, Semester CHAR(7) NOT NULL, CourseID CHAR(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 INTEGER NOT NULL, SectionNo INTEGER NOT NULL, Semester CHAR(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