Given the relations below, create the database tables using SQL DDL (Data Definition Language). Assume the following attribute data types:
StudentID (integer, primary key)
StudentName (25 characters)
FacultyID (integer, primary key)
FacultyName (25 characters)
CourseID (8 characters, primary key)
DateQualified (date)
SectionNo (integer, primary key)
Semester (7 characters)
|
|
STUDENT (StudentID, StudentName)
|
|
StudentID
|
StudentName
|
|
38214
|
Alvaro
|
|
54907
|
Letterman
|
|
66324
|
Troy
|
|
70542
|
Myra
|
|
..........
|
|
|
FACULTY (FacultyID, FacultyName)
|
|
FacultyID
|
FacultyName
|
|
1123
|
Cooper
|
|
4237
|
Burgess
|
|
5115
|
May
|
|
........
|
|
|
COURSE (CourseID, CourseName)
|
|
CourseID
|
CourseName
|
|
CMIS 320
|
Relational Database
|
|
CMIS 102
|
Algorithm Design
|
|
CMIS 420
|
Advance Relational Database
|
|
CMIS 170
|
Introduction to XML
|
|
CMSC 100
|
Intro to Computer Science
|
|
........
|
|
|
|
QUALIFIED (FacultyID, CourseID, DateQualified)
|
|
FacultyID
|
CourseID
|
DateQualified
|
|
1123
|
CMIS 102
|
9/2014
|
|
1123
|
CMIS 170
|
9/2014
|
|
4237
|
CMIS 420
|
9/2012
|
|
4237
|
CMIS 320
|
9/2013
|
|
5115
|
CMIS 102
|
9/2011
|
|
5115
|
CMIS 170
|
9/2011
|
|
..........
|
|
|
|
SECTION (SectionNo, Semester, CourseID
|
|
SectionNo
|
Semester
|
CourseID
|
|
3115
|
FALL-2014
|
CMIS 320
|
|
3116
|
FALL-2014
|
CMIS 320
|
|
3117
|
FALL-2014
|
CMIS 102
|
|
3118
|
FALL-2014
|
CMIS 420
|
|
..........
|
|
|
|
REGISTRATION (StudentID, SectionNo, Semester)
|
|
StudentID,
|
SectionNo
|
Semester
|
|
38214
|
3117
|
FALL-2014
|
|
54907
|
3117
|
FALL-2014
|
|
54907
|
3118
|
FALL-2014
|
|
66324
|
3116
|
FALL-2014
|
|
.....
|
|
|
|
|
|
|
|
After creating the tables above, write SQL statements to answer the following questions. Submit your work as a text file containing the 6 SQL statements.
a) What is the name of the faculty member whose ID is 1123?
b) What is the smallest section number used in the FALL-2014 semester?
c) How many students are enrolled in Section 3117 of FALL-2014?
d) Create an SQL command that will modify the name of course CMIS 320 from Relational Database to Relational Database Concepts and Applications
e) Write a command that will remove Barry from the Student table
f) Write statement to add an attribute, Class to the Student table?