section a use the following tables to create

Question

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)).