Section A: Use the following tables to create a database called College. Use SQL commands.
Student
|
stuid(primary)
|
lastName
|
firstName
|
major
|
credits
|
|
S1001
|
Smith
|
Tom
|
History
|
90
|
|
S1002
|
Chin
|
Ann
|
Math
|
36
|
|
S1005
|
Lee
|
Perry
|
History
|
3
|
|
S1010
|
Burns
|
Edward
|
Art
|
63
|
|
S1013
|
McCarthy
|
Owen
|
Math
|
0
|
|
S1015
|
Jones
|
Mary
|
Math
|
42
|
|
S1020
|
Rivera
|
Jane
|
CSC
|
15
|
Faculty
|
facid (primary)
|
name
|
department
|
rank
|
|
F101
|
Adams
|
Art
|
Professor
|
|
F105
|
Tanaka
|
CSC
|
Instructor
|
|
F110
|
Byrne
|
Math
|
Assistant
|
|
F115
|
Smith
|
History
|
Associate
|
|
F221
|
Smith
|
CSC
|
Professor
|
Class
|
classNumber (primary)
|
facid
|
schedule
|
room
|
|
ART103A
|
F101
|
MWF9
|
H221
|
|
CSC201A
|
F105
|
TuThF10
|
M110
|
|
CSC203A
|
F105
|
MThF12
|
M110
|
|
HST205A
|
F115
|
MWF11
|
H221
|
|
MTH101B
|
F110
|
MTuTh9
|
H225
|
|
MTH103C
|
F110
|
MWF11
|
H225
|
Enroll
|
stuid
|
classNumber
|
grade
|
|
S1001
|
ART103A
|
A
|
|
S1001
|
HST205A
|
C
|
|
S1002
|
ART103A
|
D
|
|
S1002
|
CSC201A
|
F
|
|
S1002
|
MTH103A
|
B
|
|
S1010
|
ART103A
|
|
|
S1010
|
MTH103C
|
|
|
S1020
|
CSC201A
|
B
|
|
S1020
|
MTH101B
|
A
|
1. Create the database College.
2. Create the table Student.
3. Create the table Faculty.
4. Create the table Class.
5. Create the table Enroll.
6. Create all foreign keys
Section B: Using the database in Section A. Answer all questions.
7. Create a view that finds the student name, major and enrolled in the art class
8. Create a view that finds the student name, and classes enrolled
9. Create a stored procedure that finds the name of faculty and their schedule
10. Create a stored procedure that finds the student names for a particular course.
11. Create a role called students; give SELECT permission. Use a cursor to add all students as members of the above. Setup each student as a user with temporary password of first four letter of last name and add '8888'.
12. Convert to XML the student table.
Section C: Using the Halloween database.
13. Create a table called ProductImages which has the following fields ImageID (int, primary key, identity), productid (varchar), and ImageProduct (varbinary(max)).