Use SQL:2003 (with the MULTISET construct, if necessary) to complete the schema partially de?ned in Section 14.4. Include UDTs for the following tables: Person, Student, Course, Professor, Teaching,andTranscript. Your solution should followthe object-oriented design methodology. Repeating the statements from Chapters 3 and 4, which use SQL-92, is not acceptable.
What will be an ideal response?
The objective of this exercise is to have the students use an object-oriented design and not merely repeat the SQL-92 statements from Chapters 3 and 4.
CREATE TYPE PersonType AS (
Name CHAR(20),
Address ROW(Number INTEGER, Street CHAR(20), ZIP CHAR(5)) );
-- We make transcripts accessible from Student, only
-- because this is howtranscript records are usual ly accessed
-- Note: we are not us ing re ferences for TranscriptRecordType,
-- because we are not going to gain much by sharing these objects
CREATE TYPE StudentType UNDER PersonType AS (
Id INTEGER,
Status CHAR(2),
Transcript TranscriptRecordType MULTISET );
CREATE TABLE Student OF StudentType;
CREATE TYPE ProfessorType UNDER PersonType AS (
Id INTEGER,
DeptId CHAR(4) );
-- Note: we de?ne an extra oid attribute, prof_oid, using REF IS
-- because Teaching references professor objects. Without the
-- explicit oid attribute we will not be able to populate Teaching
-- with tuples that have correct references to Professor objects
CREATE TABLE Professor OF ProfessorType
REF IS prof_oid;
CREATE TYPE CourseType AS (
CrsCode CHAR(6),
DeptId CHAR(4),
CrsName CHAR(20),
Description CHAR(50) );
-- Note: we de?ne an extra oid attribute, course_oid, using REF IS
-- because Teaching and TranscriptRecordType re ferences course
-- objects. Without the explicit oid attribute we will not be able to populate
-- Teaching and Student (which uses TranscriptRecordType)
-- with tuples that have correct references to Course objects
CREATE TABLE Course OF CourseType
REF IS course_oid;
CREATE TYPE TranscriptRecordType AS (
Course REF(CourseType) SCOPE Course,
Semester CHAR(6),
Grade CHAR(2) );
-- We could make teaching records accessible from Professor
-- but they are more often used to print catalogs, which justi?es
-- having them as a separate table
CREATE TABLE Teaching (
Professor REF(ProfessorType) SCOPE Professor,
Course REF(CourseType) SCOPE Course,
Semester CHAR(6),
Grade CHAR(1) );
You might also like to view...
Webware’s WebKit package acts as a Python servlet engine that produces________ .
a) WML. b) XML. c) XHTML. d) None of the above.
Before you can change the characteristics of a graphic object on the Stage, you must select the object.
Answer the following statement true (T) or false (F)
_____________ is a network diagnostic tool that shows you each router and server that your data encounters as it travels over the Internet.
Fill in the blank(s) with the appropriate word(s).
Determine the amount of liability insurance premiums for the following automobile insurance problems using the tables.Automobile Liability Insurance Base Annual Premium
Bodily Injury Coverage Territory 1 Territory 2 Territory 3 50/100 $90 $103 $129 100/100 $91 $104 $131 100/200 $94 $108 $136 Automobile Liability Base Annual Premiums Property Damage Coverage Territory 1 Territory 2 Territory 3 $15,000 $83 $95 $100 $20,000 $85 $97 $103 $25,000 $86 $99 $104 $50,000 $87 $101 $107 $100,000 $90 $103 $108 Automobile Insurance Multiples of Base Annual Premium Pleasure; Less Than 3 Miles to in Work Each Way Drives to Work 3 to 9 Miles Each Way Drives to Work 10 Miles or More Each Way Used in Business Mature drivers All others 1.00 1.10 1.40 1.50 Young females Age 16Age 20 1.401.05 1.501.15 1.801.45 1.901.55 Young males (married) Age 16Age 20Age 21Age 24 1.601.451.401.10 1.701.551.501.20 2.001.851.801.50 2.101.951.901.60 Young males (unmarried not principal operator/owner) Age 16Age 20Age 21Age 24 2.051.601.551.10 2.151.701.651.20 2.452.001.951.50 2.552.102.051.60 Young males (unmarried and owner) Age 16Age 20Age 21Age 24Age 26Age 29 2.702.552.501.901.501.10 2.802.652.602.001.601.20 3.102.952.902.301.901.50 3.203.053.002.402.001.60 Driver classification: Unmarried male, 21, drives 5 miles to work, principal operatorLiability coverage: 100/200/50Territory: 2The answer is $__________. Fill in the blank(s) with the appropriate word(s).