Instructions : Save this file as Lab8_YourID_LabSection (replace YourID with you
ID: 3833280 • Letter: I
Question
Instructions:
Save this file as Lab8_YourID_LabSection (replace YourID with your ID and LabSection with your section).
Re-implement the one-to-many relationship between Courses and Section in Lab7 using a Varray instead of a nested table. To complete this lab, do the following steps:
Using the DROP TABLE command, drop the table Courses.
Ensure that you still have the Section_ADT since you are going to use it again in this lab. If for some reason, you do not have it anymore, create it again.
Use the CREATE TYPE command to create a Varray called Sections_VA of of Section_ADT and set the size to 5.
Create the Course table and include it the following attributes (the underlined attribute refers to the primary key):
Attribute
Data Type
CourseID
VARCHAR(10)
Title
VARCHAR(20)
Sections
Sections_VA
Insert, in the table Courses, the following courses and their sections:
Course Details
Sections Details
Title=Computer Science I
SecID=1 Semester=Spring Year=2017
SecID=2 Semester=Spring Year=2017
Title=Computer Science II
SecID=1 Semester=Summer Year=2017
SecID=2 Semester=Summer Year=2017
Title=Professional Ethics
SecID=1 Semester=Fall Year=2017
SecID=2 Semester=Fall Year=2017
Title=Database I
SecID=1 Semester=Fall Year=2017
SecID=2 Semester=Fall Year=2017
Title=Database II
SecID=1 Semester=Spring Year=2017
SecID=2 Semester=Spring Year=2017
SecID=3 Semester=Spring Year=2017
Using the populated Courses table, write a query to display the sections of the course GEIT1411 like this:
SecID Semester Year
1 Spring 2017
2 Spring 2017
Write an anonymous block to display the following content
Course ID: GEIT1411
Course Title: Computer Science I
Section ID=1
Section ID=2
Course ID: GEIT1412
Course Title: Computer Science II
Section ID=1
Section ID=2
Course ID: GEIT2291
Course Title: Professional Ethics
Section ID=1
Section ID=2
Course ID: GEIT3341
Course Title: Database I
Section ID=1
Section ID=2
Course ID: ITAP3371
Course Title: Database II
Section ID=1
Section ID=2
Section ID=3
Hand in:
Full definition of the Courses table.
The insert commands used to fill the Courses table.
The select query in step 6 and its output.
The anonymous block in step 7.
Attribute
Data Type
CourseID
VARCHAR(10)
Title
VARCHAR(20)
Sections
Sections_VA
Explanation / Answer
First, WHERE section_id is null doesn't make much sense,
where does section_id comes from?
If it's from section_user, then it cannot be 3 and null at the same time.. ;)
If it's from User then it's a one to many relation instead of many to many...
Maybe a bit clarification on what you are trying to accomplish.
Basically it works like this: (left Doctrine, right Sql)