1) List all the referential integrity constraints that should hold on the database schema?

(2.5 marks)

2) Write appropriate SQL DDL statements to define the database with the integrity constraints and store in a text file called userid_universityschema.sql.

Attach this file or also show it in your script file of (v) using more file.sql command before or after running sqlplus. Do the same for the files in (iii) and (iv). (2.5 marks)

3) To insert the data in the database tables, also write appropriate SQL DML instructions in a text file called userid_universitydata.sql. (2.5 marks)

4) To remove any inserted data and destroy all created tables in the university reservation database, write appropriate SQL DML and DDL statements in a text file called userid_universitydroptable.sql to first delete all data in the tables and then drop the tables.

5) Using Oracle Sqlplus, implement this database design by creating all the tables with the integrity constraints using the SQL DDL you defined in (ii) above. You can create all these SQLfor the 6 tables by running your .sql file at the SQL prompt with the command:

@userid_universityschema.sql. After creating your tables successfully, you load your data with the .sql file you created in (iii) above by running @userid_universitydata.sql. If there are errors and you need to correct them, you might want to delete the tuples and drop the tables first using the .sql file you created in (iv) above as with @userid_universitydroptable.sql before re-creating the schema and re-loading the data.Then, using a script file, show the contents of all 6 tables in the database by selecting * from each of the table and saving on script file called username_assn3que1. You can do this using the following sequence of Unix/Linux commands after you have created the database and inserted data.

(Total for que 1 is 15 marks)

Fig 1.1: An Example Database State of Parts of a University Database

STUDENT

Sid	Sname	Class	Smajor
1551	Battan	3	CS
3112	Mapples	1	CS

COURSE

Cid	Cname	Chrs	Dept
60-140	Intro to C	2	CS
60-254	Data Structures	3	CS
62-190	Math Foundation	3	Math
60-315	Database	4	CS

SECTION

Sectid	Cid	Term	Year	Prof
01	62-190	Fall	16	Black
30	60-140	Fall	16	Peters
02	60-254	Winter	17	Zhu
91	62-190	Fall	17	Gooda
03	60-140	Fall	16	Tang
05	60-315	Fall	16	Eze

ENROLL

Sid	Sectid	Grade
1551	91	A
1551	02	B
3112	01	A
3112	30	B
3112	02	C
3112	05	B

PREREQ

CSectid	Prereq_Sectid
05	02
05	01
02	03

Fig 1.2: Schema of the UNIVERSITY database of Figure 1.1

STUDENT

Sid

Sname

Class

Smajor

COURSE

Cid

Cname

Chrs

Dept

SECTION

Sectid

Cid

Term

Year

Prof

ENROLL

Sid

Sectid

Grade

PREREQ

CSectid

Prereq_Sectid