Define a nested table to represent the one-to-many relationship between course a
ID: 3826173 • Letter: D
Question
Define a nested table to represent the one-to-many relationship between course and section. To complete this lab, do the following steps:
Attribute
Data Type
SecID
NUMBER
Semester
VARCHAR(20)
Year
NUMBER
Attribute
Data Type
CourseID
VARCHAR(10)
Title
VARCHAR(20)
Sections
Section_NT
Note: Do not forget to name the physical table which will store the data for the nested table Sections.
Course Details
Sections Details
CourseID=GEIT1411
Title=Computer Science I
SecID=1 Semester=Spring Year=2017
SecID=2 Semester=Spring Year=2017
CourseID=GEIT1412
Title=Computer Science II
SecID=1 Semester=Summer Year=2017
SecID=2 Semester=Summer Year=2017
CourseID=GEIT2291
Title=Professional Ethics
SecID=1 Semester=Fall Year=2017
SecID=2 Semester=Fall Year=2017
CourseID=GEIT3341
Title=Database I
SecID=1 Semester=Fall Year=2017
SecID=2 Semester=Fall Year=2017
CourseID=ITAP3371
Title=Database II
SecID=1 Semester=Spring Year=2017
SecID=2 Semester=Spring Year=2017
SecID=3 Semester=Spring Year=2017
SecID Semester Year
==================================
1 Spring 2017
2 Spring 2017
Hand in:
Attribute
Data Type
SecID
NUMBER
Semester
VARCHAR(20)
Year
NUMBER
Explanation / Answer
/* for building schema */
CREATE TYPE Section_ADT
(
SecID NUMBER ,
Semester VARCHAR(20),
Year NUMBER
)
CREATE TYPE Section_NT AS TABLE OF Section_ADT;
/* Definition for the Course Table */
CREATE TABLE Course
(
CourseID VARCHAR(10) ,
Title VARCHAR(20),
Sections Section_ADT
)NESTED table Sections store as Sections_tab
/* The insert commands used to fill the Coursestable. */
insert into Course values (
'GEIT1411', 'Computer Science',
Section_NT(Section_ADT(1,'Spring', 2017),
Section_ADT(2,'Spring', 2017)
)
);
COMMIT;
insert into Course values (
'GEIT1412', 'Computer Science',
Section_NT(Section_ADT(1,'Summer', 2017),
Section_ADT(2,'Summer', 2017)
)
);
COMMIT;
insert into Course values (
'GEIT2291', 'Professional Ethics',
Section_NT(Section_ADT(1,'Fall', 2017),
Section_ADT(2,'Fall', 2017)
)
);
COMMIT;
insert into Course values (
'GEIT3341', 'Database',
Section_NT(Section_ADT(1,'Fall', 2017),
Section_ADT(2,'Fall', 2017)
)
);
COMMIT;
insert into Course values (
'ITAP3371', 'Database',
Section_NT(Section_ADT(1,'Spring', 2017),
Section_ADT(2,'Spring', 2017),
Section_ADT(3,'Spring', 2017)
)
);
COMMIT;
/* The select query in step 5 and its output. */
select SecID,Semester,Year from Section_NT (
select Course.Sections from Course
where Course.Title='GEIT1411'
);
/* The update query in step 6. */
UPDATE TABLE(SELECT Course.Sections
FROM Course
WHERE Course.Title = 'ITAP3371 ')
SET Semester = 'Fall',
WHERE SecID = 3;
COMMIT;
/* The delete query in step 7 */
DELETE TABLE(SELECT Course.Sections
FROM Course
WHERE Course.Title = 'ITAP3371 ')
WHERE SecID = 3
COMMIT;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.