sql 代写 INFO1103 Lab 2

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

  1. Write an SQL Insert statement to successfully add a new row to the department table.
  2. Write an SQL Insert statement to successfully add a new row to the course table.
  3. Write an SQL Insert statement that violates the range of credit hours.
  4. 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