1 / 42

ADVANCED DATABASE CONCEPTS

ADVANCED DATABASE CONCEPTS. Mapping EER Conceptual Model and UML Class Diagrams to the Relational Data Model Suzanne W. Dietrich and Susan D. Urban Department of Computer Science and Engineering Arizona State University Tempe, AZ 85287-5406. OUTLINE. Relationships and Associations

holly
Download Presentation

ADVANCED DATABASE CONCEPTS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ADVANCED DATABASE CONCEPTS Mapping EER Conceptual Model and UML Class Diagrams to the Relational Data Model Suzanne W. Dietrich and Susan D. Urban Department of Computer Science and Engineering Arizona State University Tempe, AZ 85287-5406

  2. OUTLINE • Relationships and Associations • Class Hierarchies • Creating a table for each class • Creating a table for each subclass • Flattening the hierarchy • Shared Subclasses • Categories • Interface Classes

  3. RELATIONAL DATA MODELExtensional and Intensional Schema When mapping to the relational data model, some components of the conceptual model will be explicitly stored in tables while other semantics will be provided by views. • Extensional Schema: • relations that are explicitly stored • Intensional Schema: • relations defined as views

  4. RELATIONAL SCHEMA NOTATION The specification of an extensional schema summarizes a table definition by • Listing the attribute names, • Underlining the candidate keys, and • Listing the additional table constraints tableName(keyAttribute, attr1, attr2, foreignKeyAttr) foreign key (foreignKeyAttr) reference primaryKeyTable(primaryKeyAttr) An explicit primary key table constraint is included only when there is more than one candidate key shown.

  5. NAVIGATION OF ASSOCIATIONS • By default, binary associations can be traversed in both directions in UML. • 1:N and 1:1 associations can be unidirectional in UML, though (as is the default in EER), and have an alternative mapping approach that does not require creating a separate table for the association. • M:N associations are inherently bidirectional, since a separate table must be created to represent the association.

  6. NAVIGATION1:N UML EER A(keyOfA, attrOfA, attrA1, attrA2, attrA3, keyOfB, attrOfBA) foreign key (keyOfB) references B(keyOfB), constraint totalParticipationAinBA not null (keyOfB) B(keyOfB, attrOfB) Include the key from the 1 side of the association and any relationship attribute in the relation on the N side of the association.

  7. NAVIGATION1:1 UML EER B(keyOfB, attrOfB, keyOfC) primary key (keyOfB), foreign key (keyOfC) references C(keyOfC), constraint notNullCandidateKey not null (keyOfC), constraint candidateKeyConstraint unique (keyOfC) C(keyOfC, attrOfC) Include the key on the partial side of the association and any relationship attribute in the table on the total side of the association.

  8. N-ARY ASSOCIATIONS Create a table with the primary key attributes of each class involved in the association and any descriptive attributes of the association itself. finance(inventoryId, ssn, bankId,loanAmount) foreign key (inventoryId) references car(inventoryId) foreign key (ssn) references person(ssn) foreign key (bankId) references bank(bankId)

  9. WEAK OR QUALIFIED ASSOCIATION Create a table for the weak class with a composite key that includes the key of the owner class. A(keyOfA, attrOfA, attrA1, attrA2, attrA3) Weak(keyofA, partialKey, attrOfWk) EER UML

  10. RELATIONAL SCHEMA OF GENERIC EXAMPLE

  11. HOLLYWOOD SCHEMA: EER

  12. HOLLYWOOD SCHEMA: UML

  13. CLASS HIERARCHIES There are alternative strategies for mapping superclass/subclass hierarchies to relations: Create a table for each class. Create a table for subclasses only, including the attributes inherited from the superclass. Flattening the hierarchy: create a single table to represent the hierarchy with type fields to indicate class membership.

  14. MAPPINGTERMINOLOGY Recall that a relational schema consists of the specification of the extensional schema (explicitly stored tables), the intensional schema (view definitions) and integrity constraints. The term relation refers to either a table or a view. Throughout the mapping of classes, the name of a class refers to a relation that describes the attributes of that class. The name of a table that is created to assist with the definition of a class will use a suffix to differentiate it from the relation for the class.

  15. TABLE FOR EACH CLASS Create a separate table for each class in the hierarchy. Include the primary key of the root class in the table for each subclass (inclusion dependency). An equijoin must be performed to view all (specialized and inherited) attributes of a subclass.

  16. TABLE FOR EACH CLASS Example: Extensional Schema person (ssn, gender, name, isMarriedTo, phone, address) movieProfessionalEDB (ssn, company) foreign key (ssn) references person (ssn) celebrityEDB (ssn, agent, birthDate) foreign key (ssn) references person (ssn) • The primary key and referential integrity constraints enforce the “isa” constraint. • The EDB suffix on the movieProfessionalEDB and celebrityEDB tables shown above indicate that these tables, which are part of the Extensional DataBase, store the key and specialized attributes for the subclasses only. A view must be defined to include the inherited attributes from person.

  17. TABLE FOR EACH CLASS Example: Intensional Schema A view must be provided for each subclass with its inherited attributes: create view movieProfessional as select p.ssn, p.gender, p.name, p.address, p.IsMarriedTo, p.phone, m.company from person p, movieProfessionalEDB m where p.ssn=m.ssn; A similar view must be defined for the celebrity subclass.

  18. // enforce constraint create assertion disjointSpecialization check (not exists (select * from person P where P.ssn in (select M.ssn from movieProfessional M) and P.ssn in (select C.ssn from celebrity C) ) ) // find violations create view disjointViolation as select P.ssn from person P where P.ssn in (select M.ssn from movieProfessional M) and P.ssn in (select C.ssn from celebrity C) TABLE FOR EACH CLASSDisjoint Specialization Constraint

  19. // enforce constraint create assertion totalSpecialization check (not exists (select * from movieProfessional M where M.ssn not in (select C.ssn from critic C union select A.ssn from agent A) ) ) // find violations create view totalViolation as select M.ssn from movieProfessional M where M.ssn not in (select C.ssn from critic C) and M.ssn not in (select A.ssn from agent A) TABLE FOR EACH CLASSTotal Specialization Constraint

  20. // enforce constraint create assertion attrDefinedSpecialization check (not exists (select * from project P where P.pid in (select F.pid from filmProject F) and P.type <> ‘F’) or P.type = ‘F’ and P.pid not in (select F.pid from filmProject F) ) ) // find violations create view attrDefinedViolation as select P.pid from project P where P.pid in (select F.pid from filmProject F) and P.type <> ‘F’) or P.type = ‘F’ and P.pid not in (select F.pid from filmProject F) TABLE FOR EACH CLASSAttribute-Defined Specialization

  21. TABLE FOR EACH CLASSSpecialization Constraints Summary

  22. TABLE FOR EACH SUBCLASS Create a table for each subclass. Include inherited attributes from the superclass in the table for the subclass. Create a view to represent the superclass.

  23. TABLE FOR EACH SUBCLASSExample • Extensional Schema filmProject (pid, title, cost, type, location) modelingProject (pid, description, cost, type, location) • Intensional Schema create view project as (select pid, cost, type, location from filmProject) union (select pid, cost, type, location from modelingProject)

  24. TABLE FOR EACH SUBCLASSConstraints • ISA: The isa constraint is enforced by the intensional schema since the superclass is defined as the union of its subclasses. Since each class is defined by a relation name in the schema, the specifications for the following specialization constraints do not require modification: • Disjoint Specialization • Total Specialization • Attribute-defined Specialization

  25. TABLE FOR EACH SUBCLASSSpecialization Constraints Summary

  26. FLATTENING THE HIERARCHY Create a single table to represent the hierarchy. Include all attributes of each superclass/subclass in the table. Include type fields to indicate the (sub)classes to which a tuple belongs. If a tuple does not belong to a subclass, then the corresponding specific attributes of that subclass will have null values. Not recommended if there are many specific attributes for the subclasses.

  27. FLATTENING THE HIERARCHYExample: Single Type Indicator MovieProfessional Hierachy: Critic and Agent Subclasses • Extensional Schema mpCriticAgentHierarchy (ssn, company, popularity, agentFee,subtype) • Intensional Schema (agent view not shown) create view movieProfessional as select H.ssn, H.company, P.gender, P.name, P.address, P.isMarriedTo, P.phone from person P natural join mpCriticAgentHierarchy H; create view critic as select M.ssn, M.gender, M.name, M.address, M.isMarriedTo, M.phone M. company, H. popularity from movieProfessional M natural join criticAgentHierarchy H where H.subtype = ‘c’;

  28. FLATTENING THE HIERARCHY Example: Multiple Type Indicators Celebrity Hierachy: MovieStar and Model Subclasses Extensional Schema celebrityHierarchy (ssn, birthDate, agentSsn, movieStar, movieType, model, preferences) where the boolean attributes movieStar and model indicate membership of the celebrity in the corresponding subclass. Intensional Schema (model view not shown) create view movieStar as select C.ssn, C.gender, C.name, C.address, C.isMarriedTo, C.phone, C.birthdate, C.agentSsn, E.movieType from celebrity C natural join celebrityEDB E where C.movieStar=‘TRUE’

  29. FLATTENING THE HIERARCHY Example: Attribute-Defined Subclasses For attribute-defined subclasses, there is no need for an additional type field since there already exists an attribute that defines the type of the subclass. • Extensional Schema projectHierarchy (pid, location, cost, type, title, description) where type can be either "f" for filmProject or "m" for modelingProject • Intensional Schema (modelingProject not shown) create view project as select P.pid, P.location, P.cost, P.type from projectHierarchy P; create view filmProject select P.pid, P.location, P.cost, P.type, P.title from projectHierarchy P where P.type = ‘f’;

  30. FLATTENING THE HIERARCHY Constraints • ISA: The isa constraint is enforced by the intensional schema. Since each class is defined (either extensionally or intensionally) in the schema, the following constraint specifications already presented can be reused: • Disjoint Specialization • Total Specialization • Attribute-defined Specialization

  31. FLATTENING THE HIERARCHY Specialization Constraints Summary

  32. SHARED SUBCLASSES If the superclasses of the shared subclass all have the same key attribute, any of the previous options for superclass/subclass mapping can be used.

  33. SHARED SUBCLASSESstarModel Example: Table for each class Extensional Schema celebrityEDB (ssn, birthDate) movieStarEDB (ssn, movieType) modelEDB (ssn, preferences) starModelEDB (ssn) Intensional Schema (assuming views for model, movieStar & celebrity) create view starModel as select S.ssn, C.birthdate, MS.movieType, M.preferences from (((starModelEDB S natural join model M) natural join movieStar MS) natural join celebrity C)

  34. SHARED SUBCLASSESstarModel Example: Flattening the hierarchy Extensional Schema celebrityHierarchy (ssn, birthDate, movieStar, movieType, model, preferences) Intensional Schema create view starModel as select C.ssn, C.birthDate, C.movieType, C.preferences from celebrityHierarchy C where movieStar = ‘TRUE’ and model = ‘TRUE’

  35. CATEGORIES • Define a surrogate key attribute. • Define a relation for the category class that includes the surrogate key. • Each superclass relation must contain the surrogate key.

  36. CATEGORIES Example: EER

  37. CATEGORIESExample: UML In UML, the category mapping also applies to XOR Constraints

  38. CATEGORIES Example: Sponsor Mapping person (ssn, name, phone, gender, address, sponsorCode) company (cid, cname, sponsorCode) modelingProject (pid, description, sponsorCode) sponsor (sponsorCode) sponsorCode is used as a surrogate key.

  39. // enforce constraint create assertion category check (not exists (select * from sponsor S where S.sponsorCode not in (select P.sponsorCode from person P union select C.sponsorCode from company C) ) ) // find violations create view categoryViolation as select S.sponsorCode from sponsor S where S.sponsorCode not in (select P.sponsorCode from person P union select C.sponsorCode from company C) CATEGORY CONSTRAINT

  40. // enforce constraint create assertion totalSponsorCategory check (not exists (select * from person P where P.sponsorCode not in (select S.sponsorCode from sponsor S) or (select * from company C where C.sponsorCode not in (select S.sponsorCode from sponsor S))); // find violations create view totalSponsorPersonViolation as select * from person P where P.sponsorCode not in (select S.sponsorCode from sponsor S) ; create view totalSponsorCompanyViolation as select * from company C where C.sponsorCode not in (select S.sponsorCode from sponsor S) ; RELATION FOR EACH CLASSTotal Category Constraint

  41. INTERFACE CLASSES • Since an interface class defines behavior inheritance, a relation is not required for an interface class. • Appropriate procedures that implement the operations specified in an interface class should be implemented in the concrete subclasses of an interface class.

  42. ONLINE SHOPPING EER SCHEMA

More Related