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) );