9.8 Converting a UML Diagram to an Object-Relational (OR) Database Model

The UML diagram for the revised University example shown in Figure 9.5 mapped directly to both the ODL schema in Figure 9.6 and to the Iris schema shown in Figure 9.8. ORDBMSs are limited in their ability to express all of the semantic information in a UML diagram, but the basic elements of classes, hierarchies, and relationships are easily represented using the OO features of an ORDBMS such as Oracle.

For each class in a UML diagram, we create a UDT, which can include single attributes of the class using built-in data types, user defined types, structured attributes using nested UDTs, multivalued attributes using arrays, and multivalued structured attributes using nested tables. Some special types, such as enumerated types, can be simulated using constraints, triggers, or stored procedures. Class hierarchies are defined by creating subtypes UNDER previously defined types, creating a single substitutable table for all subtypes, as described in Section 9.7.6. Shared subclasses cannot be represented directly and there is no way to represent specialization constraints. Class member methods can be implemented directly by creating them in the UDT body.

Binary one-to-one, one-to-many, and many-to-many associations without descriptive attributes are represented using references. If an association is bidirectional, references are required in both tables, but if the association is unidirectional, references are needed only in the table on the stem side of the arrow. For the one-to-one bidirectional case, each table must have a single attribute that references the other, but for the one-to-many case, An array of references can be used to store the multiple references on the “one” side, and a single reference on the “many” side. A many-to-many bidirectional association without a descriptive attribute can be represented by arrays of references in both tables. Higher-order relationships and many-to-many relationships with descriptive attributes require a separate relationship type with references or arrays of references from the individual types. Aggregation can be represented in the same way as association. Object tables are then created for the UDTs.

We will use the revised University example to illustrate the mapping process. The following is an OR schema for this example

Line 1. Person open parentheses p I d comma Name comma address comma phone comma birthdate comma find Age open parentheses method close parentheses close parentheses . Line 2. Student UNDER Person open parentheses credits comma G P A comma get Status open parentheses method close parentheses comma add Credits open parentheses method close parentheses comma has Advisor open parentheses R E F to Faculty close parentheses comma takes Class open parentheses REFs to Class close parentheses comma earned Grade open parentheses R E F s to Grade close parentheses close parentheses. Line 3. Undergrad UNDER Student open parentheses major comma change Major open parentheses method close parentheses close parentheses . Line 4. Graduate UNDER Student open parentheses program comma change Program open parentheses method close parentheses close parentheses. Line 5. Teach A s s t UNDER Graduate open parentheses funding Source comma annual Stipend comma assists open parentheses R E F to Class Section close parentheses. Line 6. Faculty UNDER Person open parentheses rank comma salary comma give Raise open parentheses method close parentheses comma has Evaluation open parentheses R E F to Evaluation close parentheses comma belongs To open parentheses REF to Dept close parentheses comma chairs open parentheses open parentheses R E F to Dept close parentheses comma teaches open parentheses R E F s to Class Section close parentheses close parentheses. Line 7. Evaluation open parentheses eval Date comma rater Name comma rating comma is Eval Of open parentheses R E F to Faculty close parentheses close parentheses. Line 8. Dept open parentheses dept Code comma dept Name comma dept Office comma phone comma has Faculty open parentheses REFs to Faculty close parentheses comma has Chair open parentheses R E F to Faculty close parentheses comma offers open parentheses R E F s to Course close parentheses close parentheses. Line 9. Course open parentheses c N o comma c Title comma credits comma description comma change Desc open parentheses method close parentheses comma is Offering open parentheses R E F to Dept close parentheses comma has Prerequesites open parentheses R E F s to Course close parentheses comma is Prereq Of open parentheses R E F s to Course close parentheses close parentheses. Line 10. Class Section open parentheses section Code comma schedule comma room comma is Section open parentheses R E F to Course close parentheses comma has Student open parentheses REFs to Student close parentheses comma has Teacher open parentheses R E F to Faculty close parentheses comma has T A open parentheses R E F to Teach A s s t close parentheses comma given Student open parentheses R E F s to Grade close parentheses close parentheses. Line 11. Grade open parentheses grade comma s t u open parentheses R E F to Student close parentheses class Sect open parentheses R E F to Class Section close parentheses close parentheses

The DDL for an OR Oracle database for this example is shown in FIGURE 9.17.

In FIGURE 9.17(A) we create the UDTs to correspond to the UML classes. First, we create UDTs for AddressType, NameType, and PhoneType to use later as column objects. We create a PersonType, making sure it is NOT FINAL, which will allow us to create subtypes. We create subtype StudentType UNDER PersonType, and the subtypes GraduateType and UndergraduateType UNDER StudentType, with TeachAsstType UNDER GraduateType. We create FacultyType UNDER PersonType. In creating these types, we defer entering any REF attributes that refer to types not yet defined. We will add these relationships later.

For the weak entity Evaluation, we created a type EvalType that included the discriminator evalDate and a reference attribute, is EvalOf, to the faculty member being evaluated. For simplicity, we chose the name given to the relationship in the UML diagram as the name of the reference attribute.

For the DeptType, we need an array to store the many references to the faculty members for each department, so we first created a RefFacArrayType as a VARRAY of REF FacultyType, and then used that type for the attribute hasFaculty. For the reference to the chairperson, the attribute hasChair is a single reference because that is a one relationship.

For CourseType we have a reference attribute isOffering to the department, a VARRAY of REF CourseType for hasPrerequisites, and a VARRAY of REF CourseType for isPrereqOf.

We create a type for ClassSection, with single references for CourseType, FacultyType, and TeachAsstType, an array of references to StudentType to represent the relationship hasStudent, and an array of references to Grade to represent givenStudent.

For Grade, which is a descriptive attribute of the many-to-many relationship between Student and classSection, we create a type that includes references to both Student and ClassSection named stu and classSect, respectively.

To ensure that we have represented all associations, we then return to add single references or arrays of references for all the types created earlier for which we omitted reference attributes originally, altering the types to include them, as shown in FIGURE 9.17(B). The CASCADE option in the ALTER TYPE command propagates the change to dependent types and tables

After creating all the required UDTs, including their relationships, we create the tables, identifying primary keys, foreign keys, and any other constraints for them. We cannot use a REF attribute as a primary key or unique attribute, so in cases where such an attribute would normally be part of the primary key, we will use the NOT NULL constraint and limit the scope of the attribute to the referenced table, which is not a completely satisfactory solution.

We decide not to create a table for Person, but to begin with Faculty and Student, which serves as the base type for the hierarchy consisting of Student, Graduate, Undergraduate, and TeachAsst. We use a substitutable table for all students, using the type constructors to insert the more specialized records, as described earlier. The other tables, Depts, Evaluations, Courses, ClassSections, and Grades, are straightforward object tables for their corresponding types. It is important to read the completed schema carefully and to compare it with the UML diagram to make sure that all classes and all relationships shown on the diagram are represented in the database before executing the code. Also recall that when inserting records, it may be necessary to temporarily disable some constraints. FIGURE 9.17(C) shows code for creating the object tables.

FIGURE 9.17(D) shows some DML commands for inserting data and querying the tables. Note that when inserting records, it may be necessary to insert null values initially, especially for references, or to disable some constraints temporarily.

Line 1. CREATE OR REPLACE TYPE Address Type AS OBJECT open parentheses Line 2. Street VAR CHAR 2 open parentheses 50 close parentheses comma Line 3. MEMBER PROCEDURE find Age close parentheses Line 4. INSTANTIABLE Line 5. NOT FINAL semicolon Line 6. forward slash Line 7. CREATE OR REPLACE TYPE Student Type UNDER Person Type open parentheses Line 8. Credits NUMBER open parentheses 3 close parentheses comma Line 9. GPA REAL comma Line 10. MEMBER PROCEDURE add Credits open parentheses num Credits IN NUMBER close parentheses comma Line 11. MEMBER PROCEDURE get Status close parentheses Line 12. INSTANTIABLE Line 13. NOT FINAL semicolon Line 14. forward slash Line 15. CREATE OR REPLACE TYPE Undergrad Type UNDER Student Type open parentheses Line 16. major VAR CHAR 2 open parentheses 25 close parentheses comma Line 17. MEMBER PROCEDURE change Major open parentheses new Major IN VAR CHAR 2 close parentheses close parentheses Line 18. INSTANTIABLE Line 19. FINAL semicolon Line 20. forward slash Line 21. CREATE OR REPLACE TYPE Graduate Type UNDER Student Type open parentheses Line 22. program VAR CHAR 2 open parentheses 25 close parentheses comma Line 23. MEMBER PROCEDURE change Program open parentheses new Program IN VAR CHAR 2 close parentheses close parentheses Line 24. INSTANTIABLE Line 25. NOT FINAL semicolon Line 26. forward slash Line 27. CREATE OR REPLACE TYPE Teach A s s t Type UNDER Graduate Type open parentheses Line 28. Funding Source VAR CHAR 2 open parentheses 25 close parentheses comma Line 29. Annual Stipend REAL close parentheses Line 30. INSTANTIABLE Line 31. FINAL semicolon Line 32. forward slash Line 33. CREATE OR REPLACE TYPE Faculty Type UNDER Person Type open parentheses Line 34. MEMBER PROCEDURE give Raise open parentheses amount Raise IN REAL close parentheses close parentheses Line 35. INSTANTIABLE Line 36. FINAL semicolon Line 37. forward slash Line 38. City VAR CHAR 2 open parentheses 15 close parentheses comma Line 39. State CHAR open parentheses 2 close parentheses comma Line 40. Zip VAR CHAR 2 open parentheses 10 close parentheses close parentheses semicolon Line 41. forward slash Line 42. CREATE OR REPLACE TYPE Phone Type AS OBJECT open parentheses Line 43. Area Code CHAR open parentheses 3 close parentheses comma Line 44. Telephone Number CHAR open parentheses 7 close parentheses close parentheses semicolon Line 45. forward slash Line 46. CREATE OR REPLACE TYPE Name Type AS OBJECT open parentheses Line 47. First Name VAR CHAR 2 open parentheses 15 close parentheses comma Line 48. Last Name VAR CHAR 2 open parentheses 20 close parentheses close parentheses semicolon Line 49. forward slash Line 50. CREATE OR REPLACE TYPE Person Type AS OBJECT open parentheses Line 51. P i d NUMBER open parentheses 5 close parentheses comma Line 52. Name Name Type comma Line 53. Address Address Type comma Line 54. Phone Phone Type comma Line 55. Birthdate DATE comma Line 56. rank VAR CHAR 2 open parentheses 15 close parentheses comma Line 57. salary REAL comma Line 58. forward slash Line 59. CREATE OR REPLACE TYPE Eval Type AS OBJECT open parentheses Line 60. Eval Date DATE comma Line 61. hyphen hyphen for Dept Type we need an array to hold R E F s to Faculty Type Line 62. CREATE OR REPLACE TYPE Ref F a c Array Type AS VARRAY open parentheses 30 close parentheses of REF Faculty Type semicolon Line 63. forward slash Line 64. CREATE OR REPLACE TYPE Dept Type AS OBJECT open parentheses Line 65. CREATE OR REPLACE TYPE Course Type AS OBJECT open parentheses Line 66. MEMBER PROCEDURE change Desc open parentheses new Desc IN CLOB close parentheses close parentheses Line 67. INSTANTIABLE Line 68. FINAL semicolon Line 69. forward slash Line 70. CREATE OR REPLACE TYPE Ref S t u Array Type AS VARRAY open parentheses 50 close parentheses OF REF Student Type semicolon Line 71. CREATE OR REPLACE TYPE Class Section Type AS OBJECT open parentheses Line 72. Section Code VAR CHAR 2 open parentheses 3 close parentheses comma Line 73. CREATE OR REPLACE TYPE Grade Type AS OBJECT open parentheses Line 74. grade VAR CHAR 2 open parentheses 2 close parentheses comma Line 75. forward slash Line 76. Rater Name Name Type comma Line 77. rating NUMBER open parentheses 2 close parentheses comma Line 78. Is Eval Of REF Faculty Type close parentheses Line 79. INSTANTIABLE Line 80. FINAL semicolon Line 81. forward slash Line 82. Dept Code VAR CHAR 2 open parentheses 3 close parentheses comma Line 83. Dept Name VAR CHAR 2 open parentheses 35 close parentheses comma Line 84. Dept Office VAR CHAR 2 open parentheses 15 close parentheses comma Line 85. phone phone Type comma Line 86. Has Faculty Ref Fac Array Type comma Line 87. Has Chair REF Faculty Type close parentheses Line 88. INSTANTIABLE Line 89. FINAL semicolon Line 90. forward slash Line 91. C No VAR CHAR 2 open parentheses 6 close parentheses comma Line 92. C Title VAR CHAR 2 open parentheses 50 close parentheses comma Line 93. credits NUMBER open parentheses 2 close parentheses comma Line 94. description CLOB comma Line 95. Is Offering REF Dept Type comma Line 96. schedule VAR CHAR 2 open parentheses 10 close parentheses comma Line 97. room VAR CHAR 2 open parentheses 15 close parentheses comma Line 98. Is Section REF Course Type comma Line 99. Has Student Ref S t u Array Type comma Line 100. Has Teacher REF Faculty Type comma Line 101. Has T A REF Teach Asst Type close parentheses Line 102. INSTANTIABLE Line 103. FINAL semicolon Line 104. forward slash Line 105. S t u R E F Student Type comma Line 106. Class Sect R E F Class Section Type close parentheses Line 107. INSTANTIABLE Line 108. FINAL semicolon

Line 1. CREATE OR REPLACE TYPE Address Type AS OBJECT open parentheses Line 2. Street VAR CHAR 2 open parentheses 50 close parentheses comma Line 3. MEMBER PROCEDURE find Age close parentheses Line 4. INSTANTIABLE Line 5. NOT FINAL semicolon Line 6. forward slash Line 7. CREATE OR REPLACE TYPE Student Type UNDER Person Type open parentheses Line 8. Credits NUMBER open parentheses 3 close parentheses comma Line 9. GPA REAL comma Line 10. MEMBER PROCEDURE add Credits open parentheses num Credits IN NUMBER close parentheses comma Line 11. MEMBER PROCEDURE get Status close parentheses Line 12. INSTANTIABLE Line 13. NOT FINAL semicolon Line 14. forward slash Line 15. CREATE OR REPLACE TYPE Undergrad Type UNDER Student Type open parentheses Line 16. major VAR CHAR 2 open parentheses 25 close parentheses comma Line 17. MEMBER PROCEDURE change Major open parentheses new Major IN VAR CHAR 2 close parentheses close parentheses Line 18. INSTANTIABLE Line 19. FINAL semicolon Line 20. forward slash Line 21. CREATE OR REPLACE TYPE Graduate Type UNDER Student Type open parentheses Line 22. program VAR CHAR 2 open parentheses 25 close parentheses comma Line 23. MEMBER PROCEDURE change Program open parentheses new Program IN VAR CHAR 2 close parentheses close parentheses Line 24. INSTANTIABLE Line 25. NOT FINAL semicolon Line 26. forward slash Line 27. CREATE OR REPLACE TYPE Teach A s s t Type UNDER Graduate Type open parentheses Line 28. Funding Source VAR CHAR 2 open parentheses 25 close parentheses comma Line 29. Annual Stipend REAL close parentheses Line 30. INSTANTIABLE Line 31. FINAL semicolon Line 32. forward slash Line 33. CREATE OR REPLACE TYPE Faculty Type UNDER Person Type open parentheses Line 34. MEMBER PROCEDURE give Raise open parentheses amount Raise IN REAL close parentheses close parentheses Line 35. INSTANTIABLE Line 36. FINAL semicolon Line 37. forward slash Line 38. City VAR CHAR 2 open parentheses 15 close parentheses comma Line 39. State CHAR open parentheses 2 close parentheses comma Line 40. Zip VAR CHAR 2 open parentheses 10 close parentheses close parentheses semicolon Line 41. forward slash Line 42. CREATE OR REPLACE TYPE Phone Type AS OBJECT open parentheses Line 43. Area Code CHAR open parentheses 3 close parentheses comma Line 44. Telephone Number CHAR open parentheses 7 close parentheses close parentheses semicolon Line 45. forward slash Line 46. CREATE OR REPLACE TYPE Name Type AS OBJECT open parentheses Line 47. First Name VAR CHAR 2 open parentheses 15 close parentheses comma Line 48. Last Name VAR CHAR 2 open parentheses 20 close parentheses close parentheses semicolon Line 49. forward slash Line 50. CREATE OR REPLACE TYPE Person Type AS OBJECT open parentheses Line 51. P i d NUMBER open parentheses 5 close parentheses comma Line 52. Name Name Type comma Line 53. Address Address Type comma Line 54. Phone Phone Type comma Line 55. Birthdate DATE comma Line 56. rank VAR CHAR 2 open parentheses 15 close parentheses comma Line 57. salary REAL comma Line 58. forward slash Line 59. CREATE OR REPLACE TYPE Eval Type AS OBJECT open parentheses Line 60. Eval Date DATE comma Line 61. hyphen hyphen for Dept Type we need an array to hold R E F s to Faculty Type Line 62. CREATE OR REPLACE TYPE Ref F a c Array Type AS VARRAY open parentheses 30 close parentheses of REF Faculty Type semicolon Line 63. forward slash Line 64. CREATE OR REPLACE TYPE Dept Type AS OBJECT open parentheses Line 65. CREATE OR REPLACE TYPE Course Type AS OBJECT open parentheses Line 66. MEMBER PROCEDURE change Desc open parentheses new Desc IN CLOB close parentheses close parentheses Line 67. INSTANTIABLE Line 68. FINAL semicolon Line 69. forward slash Line 70. CREATE OR REPLACE TYPE Ref S t u Array Type AS VARRAY open parentheses 50 close parentheses OF REF Student Type semicolon Line 71. CREATE OR REPLACE TYPE Class Section Type AS OBJECT open parentheses Line 72. Section Code VAR CHAR 2 open parentheses 3 close parentheses comma Line 73. CREATE OR REPLACE TYPE Grade Type AS OBJECT open parentheses Line 74. grade VAR CHAR 2 open parentheses 2 close parentheses comma Line 75. forward slash Line 76. Rater Name Name Type comma Line 77. rating NUMBER open parentheses 2 close parentheses comma Line 78. Is Eval Of REF Faculty Type close parentheses Line 79. INSTANTIABLE Line 80. FINAL semicolon Line 81. forward slash Line 82. Dept Code VAR CHAR 2 open parentheses 3 close parentheses comma Line 83. Dept Name VAR CHAR 2 open parentheses 35 close parentheses comma Line 84. Dept Office VAR CHAR 2 open parentheses 15 close parentheses comma Line 85. phone phone Type comma Line 86. Has Faculty Ref Fac Array Type comma Line 87. Has Chair REF Faculty Type close parentheses Line 88. INSTANTIABLE Line 89. FINAL semicolon Line 90. forward slash Line 91. C No VAR CHAR 2 open parentheses 6 close parentheses comma Line 92. C Title VAR CHAR 2 open parentheses 50 close parentheses comma Line 93. credits NUMBER open parentheses 2 close parentheses comma Line 94. description CLOB comma Line 95. Is Offering REF Dept Type comma Line 96. schedule VAR CHAR 2 open parentheses 10 close parentheses comma Line 97. room VAR CHAR 2 open parentheses 15 close parentheses comma Line 98. Is Section REF Course Type comma Line 99. Has Student Ref S t u Array Type comma Line 100. Has Teacher REF Faculty Type comma Line 101. Has T A REF Teach Asst Type close parentheses Line 102. INSTANTIABLE Line 103. FINAL semicolon Line 104. forward slash Line 105. S t u R E F Student Type comma Line 106. Class Sect R E F Class Section Type close parentheses Line 107. INSTANTIABLE Line 108. FINAL semicolon
Line 1. CREATE OR REPLACE TYPE Ref Course Array Type AS VARRAY open parentheses 300 close parentheses OF REF Course Type semicolon Line 2. forward slash Line 3. CREATE OR REPLACE TYPE Ref Class Section Array Type AS VARRAY open parentheses 20 close parentheses OF REF Class Section Type semicolon Line 4. forward slash Line 5. CREATE OR REPLACE TYPE Ref Has Eval Array Type AS VARRAY open parentheses 20 close parentheses OF REF Eval Type semicolon Line 6. forward slash Line 7. CREATE OR REPLACE TYPE Ref Grade Array Type AS VARRAY open parentheses 50 close parentheses OF REF Grade Type semicolon Line 8. forward slash Line 9. ALTER TYPE Course Type ADD ATTRIBUTE is Prereq Of Ref Course Array Type CASCADE semicolon Line 10. ALTER TYPE Course Type ADD ATTRIBUTE has Prereq Ref Course Array Type CASCADE semicolon Line 11. ALTER TYPE Course Type ADD ATTRIBUTE has Section Ref Class Section Array Type CASCADE semicolon Line 12. ALTER TYPE Faculty Type ADD ATTRIBUTE advises Ref S t u Array Type CASCADE semicolon Line 13. ALTER TYPE Faculty Type ADD ATTRIBUTE teaches Ref Class Section Array Type CASCADE semicolon Line 14. ALTER TYPE Faculty Type ADD ATTRIBUTE has Eval Ref Has Eval Array Type CASCADE semicolon Line 15. ALTER TYPE Faculty Type ADD ATTRIBUTE belongs To REF Dept Type CASCADE semicolon Line 16. ALTER TYPE Faculty Type ADD ATTRIBUTE chairs REF Dept Type CASCADE semicolon Line 17. ALTER TYPE Student Type ADD ATTRIBUTE has Advisor REF Faculty Type CASCADE semicolon Line 18. ALTER TYPE Student Type ADD ATTRIBUTE takes Class Ref Class Section Array Type CASCADE semicolon Line 19. ALTER TYPE Student Type ADD ATTRIBUTE earned Grade Ref Grade Array Type CASCADE semicolon Line 20. ALTER TYPE Dept Type ADD ATTRIBUTE offers Ref Course Array Type CASCADE semicolon Line 21. ALTER TYPE Class Section Type ADD ATTRIBUTE given Student Ref Grade Array Type CASCADE semicolon Line 22. ALTER TYPE Teach A s s t Type ADD ATTRIBUTE assists REF Class Section Type CASCADE semicolon Line 1. CREATE TABLE Faculty 4 OF Faculty Type open parentheses Line 2. CONSTRAINT F a c underscore p i d underscore pk PRIMARY KEY open parentheses p i d close parentheses comma Line 3. CONSTRAINT F a c underscore rank underscore c c CHECK open parentheses rank IN open parentheses single quote Instructor single quote comma single quote Assistant single quote comma single quote Associate single quote comma single quote Professor single quote close parentheses close parentheses close parentheses semicolon Line 4. CREATE TABLE Depts OF dept Type open parentheses Line 5. CONSTRAINT Dept underscore dept Code underscore p k PRIMARY KEY open parentheses dept Code close parentheses comma Line 6. CONSTRAINT Dept underscore chair underscore f k FOREIGN KEY open parentheses has Chair close parentheses REFERENCES Faculty 4 close parentheses semicolon Line 7. hyphen hyphen cannot use R E F type as PRIMARY KEY or unique a t t comma so use not null for is Eval of Line 8. CREATE TABLE Evaluations OF Eval Type open parentheses Line 9. CONSTRAINT Eval underscore F a c p i d underscore f k FOREIGN KEY open parentheses is Eval Of close parentheses REFERENCES Faculty 4 comma Line 10. CONSTRAINT Eval underscore F a c underscore n n is Eval of NOT NULL close parentheses semicolon Line 11. CREATE TABLE Students OF Student Type open parentheses Line 12. CONSTRAINT S t u s underscore s t u i d underscore pk PRIMARY KEY open parentheses p i d close parentheses comma Line 13. CONSTRAINT S t u s underscore F a c P i d underscore f k FOREIGN KEY open parentheses has Advisor close parentheses REFERENCES Faculty 4 close parentheses semicolon Line 14. CREATE TABLE Courses OF Course Type open parentheses Line 15. CONSTRAINT Courses underscore c N o underscore pk PRIMARY KEY open parentheses c N o close parentheses comma Line 16. CONSTRAINT Courses underscore is Offer underscore f k FOREIGN KEY open parentheses is Offering close parentheses REFERENCES Depts close parentheses semicolon Line 17. CREATE TABLE Class Sections OF Class Section Type open parentheses Line 18. CONSTRAINT C l Sect underscore is Sect underscore code underscore n n is Section NOT NULL comma Line 19. CONSTRAINT C l Sect underscore is Sect underscore f k FOREIGN KEY open parentheses is Section close parentheses REFERENCES Courses comma Line 20. CONSTRAINT C l Sect underscore has Teacher underscore f k FOREIGN KEY open parentheses has Teacher close parentheses REFERENCES Faculty 4 comma Line 21. CONSTRAINT C l Sect underscore has T A underscore f k FOREIGN KEY open parentheses has T A close parentheses REFERENCES Students close parentheses semicolon Line 22. CREATE TABLE Grades OF Grade Type open parentheses Line 23. CONSTRAINT Grades underscore s t u underscore n n s t u NOT NULL comma Line 24. CONSTRAINT Grades underscore Stupid underscore f k FOREIGN KEY open parentheses s t u close parentheses REFERENCES Students comma Line 25. CONSTRAINT Grades underscore c l sect underscore n n class Sect NOT NULL comma Line 26. CONSTRAINT Grades underscore c l Sect underscore f k FOREIGN KEY open parentheses class Sect close parentheses REFERENCES Class Sections close parentheses semicolon
UPDATE Students s set s dot earned Grade equals Ref Grade Array Type open parentheses open parentheses SELECT REF open parentheses g close parentheses FROM Grades g WHERE g dot s t u dot p i d equals 101 and g dot class Sect dot section code equals single quote A single quote AND single quote C S C 101 single quote IN g dot class Sect dot is Section. c No close parentheses comma open parentheses SELECT REF open parentheses g close parentheses FROM Grades g WHERE g dot s t u dot p i d equals 101 and g dot class Sect dot section Code equals single quote A single quote AND single quote M T H 101 single quote IN g dot class Sect dot is Section dot c No close parentheses close parentheses WHERE s dot p i d equals 101 semicolon UPDATE Class Sections c set c dot given Student equals Ref Grade Array Type open parentheses open parentheses SELECT R E F open parentheses g close parentheses FROM Grades g WHERE g dot s t u dot p i d equals 101 and g dot class Sect dot section code equals single quote A single quote AND single quote C S C 101 single quote IN g dot class Sect dot is Section. c N o close parentheses comma null close parentheses WHERE c dot section Code equals single quote A single quote AND c dot is Section dot c N o equals single quote C S C 101 single quote semicolon UPDATE Class Sections c set c dot given Student equals Ref Grade Array Type open parentheses open parentheses SELECT REF open parentheses g close parentheses FROM Grades g WHERE g dot s t u dot p i d equals 101 AND g dot class Sect dot section code equals single quote A single quote AND single quote M T H 101 single quote IN g dot class Sect dot is Section. c N o close parentheses comma null close parentheses WHERE c dot section Code equals single quote A single quote AND c dot is Section dot c N o equals single quote M T H 101 single quote semicolon hyphen hyphen some queries to demonstrate relationships SELECT s dot name comma deref open parentheses s dot has advisor close parentheses FROM students s WHERE p i d equals 190 semicolon SELECT s dot p i d comma s dot name dot last name comma s dot name dot first name comma s dot has advisor dot name comma s dot has advisor dot belongs To. Dept Name FROM students s WHERE s dot p i d equals 101 semicolon SELECT g dot s t u dot p i d comma g dot s t u dot name dot last Name comma g dot s t u dot name dot first Name comma g dot grade comma g dot class Sect dot is Section dot c N o comma g dot class Sect dot section Code FROM Grades g WHERE g dot s t u dot p i d equals 101 semicolon.
UPDATE Students s set s dot earned Grade equals Ref Grade Array Type open parentheses open parentheses SELECT REF open parentheses g close parentheses FROM Grades g WHERE g dot s t u dot p i d equals 101 and g dot class Sect dot section code equals single quote A single quote AND single quote C S C 101 single quote IN g dot class Sect dot is Section. c No close parentheses comma open parentheses SELECT REF open parentheses g close parentheses FROM Grades g WHERE g dot s t u dot p i d equals 101 and g dot class Sect dot section Code equals single quote A single quote AND single quote M T H 101 single quote IN g dot class Sect dot is Section dot c No close parentheses close parentheses WHERE s dot p i d equals 101 semicolon UPDATE Class Sections c set c dot given Student equals Ref Grade Array Type open parentheses open parentheses SELECT R E F open parentheses g close parentheses FROM Grades g WHERE g dot s t u dot p i d equals 101 and g dot class Sect dot section code equals single quote A single quote AND single quote C S C 101 single quote IN g dot class Sect dot is Section. c N o close parentheses comma null close parentheses WHERE c dot section Code equals single quote A single quote AND c dot is Section dot c N o equals single quote C S C 101 single quote semicolon UPDATE Class Sections c set c dot given Student equals Ref Grade Array Type open parentheses open parentheses SELECT REF open parentheses g close parentheses FROM Grades g WHERE g dot s t u dot p i d equals 101 AND g dot class Sect dot section code equals single quote A single quote AND single quote M T H 101 single quote IN g dot class Sect dot is Section. c N o close parentheses comma null close parentheses WHERE c dot section Code equals single quote A single quote AND c dot is Section dot c N o equals single quote M T H 101 single quote semicolon hyphen hyphen some queries to demonstrate relationships SELECT s dot name comma deref open parentheses s dot has advisor close parentheses FROM students s WHERE p i d equals 190 semicolon SELECT s dot p i d comma s dot name dot last name comma s dot name dot first name comma s dot has advisor dot name comma s dot has advisor dot belongs To. Dept Name FROM students s WHERE s dot p i d equals 101 semicolon SELECT g dot s t u dot p i d comma g dot s t u dot name dot last Name comma g dot s t u dot name dot first Name comma g dot grade comma g dot class Sect dot is Section dot c N o comma g dot class Sect dot section Code FROM Grades g WHERE g dot s t u dot p i d equals 101 semicolon.

FIGURE 9.17 UML to Oracle OR Example—Revised University Database

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.16.139.8