Reference no: EM131704436

Assignment

Download the sample database ITCO630_A from the link below. The sample database represents an educational institution with students and different schools. Each student goes to just one school. The students have various roles in different assignments and may work on more than one assignment at a time.

The following are the tables and data in the ITCO630_A database:

school_no	school_name	city
S1	West University	San Francisco
S2	Central University	Chicago
S3	East University	San Francisco.

Student Table

Student_no	student_fname	student_Iname	school_no
25348	John	Doe	S3
10102	Jane	Smith	S3
18316	Robert	Habner	S1
29346	James	Elkehart	S3
90316	Elise	Herbert	S2
25813	Lisa	Branson	S2
28559	Sarah	Masters	S1

Assignment Table

assignment_no	assignment_name	points
Al	Group Project	120
A2	Mid-term Exam	100
A3	Final Exam	200

Summary Table

student_no	assignment_no	role	start_date
10102	Al	Observer	10/1/2005
10102	A3	Leader	1/1/2006
25348	A2	Worker	2/15/2005
18316	A2	NULL	6/1/2005
29346	A2	NULL	11/15/2004
25813	A3	Observer	10/15/200.5
90316	Al	Leader	4/15/2005
28559	Al	NULL	8/112005
28559	A2	Worker	2/1/2006
90316	M	Worker	11/15/2004
29346	Al	Worker	1/4/2005

Using the sample database, write the scripts in a file called ITCO630_P3.SQL to create the following views. Remember to include a USES clause at the top of the script file to use the ITCO630_A database. Also include code that checks if the view already exists. If it does, it should be dropped and recreated.

1. Create a view named v_worker showing the student number, assignment number, and start date where the role is "worker."

2. Create a view called v_no_points with all the columns of the assignment table except the points column.

3. Create a view called v_count that shows the number of students working on each assignment. The view should have columns for the assignment number and the count.

Attachment:- Sample-Database.zip