INFO1103 Lab 2
Due: Friday September 28, 2018 at 4:00 pm
Part 1:
Create a COURSE table and DEPARTMENT table that match the ER diagram created in Lab 1
Reminders about the DEPARTMENT table:
o PrimarykeyisDepartment_ID,anintegerwhichistobegenerated
o Other attributes include: Department Name, Main Office, and Website
Reminders about the COURSE table:
o Primary key is Course_ID, an integer which is to be generated
o Other attributes include: Course Title, Course Description, and Credit hours
o Make sure that credit hours is within the range of 0 to 6 and has a default value of 3 o The COURSE table should also include an foreign key attribute named
Department_ID which must exist in DEPARTMENT table
- Write an SQL Insert statement to successfully add a new row to the department table.
- Write an SQL Insert statement to successfully add a new row to the course table.
- Write an SQL Insert statement that violates the range of credit hours.
- Write an SQL Insert statement that violates the foreign key constraint.
Part 2:
You are to create a relational schema using Oracle DDL that corresponds to the ER diagram created in lab 1. The DEPARTMENT and COURSE tables from part 1 will form the basis of that schema. The completed schema should include the following tables: Department, Course, Student, Section, and Registers_In.
Test your schema with the following INSERT statements:
- Add 2 rows to the DEPARTMENT table
- Add 2 valid rows to the COURSE table
- Add 2 rows to the STUDENT table
- Add 2 valid rows to SECTION table
- Add 2 valid rows to REGISTERS_IN table
- Attempt to add a row to REGISTERS_IN table that does not match an existing student
- Attempt to add a row to REGISTERS_IN table that does not match an existing section
Finally, you must use SELECT statements to show contents of all tables
Submission Instructions:
1) Submit a printout of the SQL file for both parts, with a signed copy of the standard INFO1103 cover page, to the course bin on E level of Head Hall
2) Submit the electronic version of your SQL file for both parts through Desire To Learn. Page 1 of 1