ER2 1The Entity-Relationship Model 2 • Advanced concepts for ER Modelling–Multiway relationships–Weak entity set–Subclass structures–Aggregations and compositions•Map ER models into relational database schemas.•Reading: Chapter 4 “High-level Database Models” (Sections 4.4, 4.7 and 4.8) of “A First Course in Database Systems”.ER2 2What we discussed in ER1In ER1, we have discussed:•An ER model is a high-level data model closer to the real-world information system than the relational data model. •The ER model:• Keys for entity sets• Binary relationships only– 1:1, M:1, M:N•The UML class diagram for the ER modelThe relational database design 3ER1: Example4Aggregation and Composition: elaboration on the M:1 and 1:1 relationshipsThe M:1 (including 1:1) relationships can be elaborated using special notations to stipulate on how the model should be mapped to relations in the relational database schema (discussed later). ● The Aggregation is a M:1 association with an diamond on one end, indicating that the class has zero or one multiplicity. ● A Composition is a M:1 association with a solid diamond on one end, indicating that the class has an exact one multiplicity. Both Aggregation and Composition associations do not need to have a name, as it will never be mapped into a separate relation in the relational database schema (discussed later). 5Aggregation: Example6The production M:1 relationship models that a movie has one production studio and it is possible that such information is not available. The Production relationship is elaborated as an aggregation relationship. The open diamond indicates 0..1. The name “Production” is not needed.Composition: ExampleThe Coordination relationship models that a course has exactly one coordinator. 7The Coordination relationship is elaborated as a composition. The solid diamond Subclasses in the ER Model• An entity set includes some entities that have special attributes not associated with all members. – Some employees are academics having the special attribute “specialty”. • A special entity set, or subclass, is defined with its own attributes and possibly relationships. The subclass has the “isa” (in other words “is a”, or specialisation) relationship with the original entity set. • An entity set can have several subclasses. But a subclass entity set has only one direct superclass entity set• Data for entities of a subclass entity set comprise its own attributes as well as all attributes from its superclass entity sets. 8Subclasses in the ER model: Example9The subclass concept is about “specialisation”. Subclasses in UMLUML is an object-oriented modelling language. The subclass concept is based on “inheritance”, different from “specialisation” of the ER model.● A subclass inherits attributes and associations from its superclass. ● An object belongs to only one class (and also conceptually “belongs” to its superclass due to inheritance). UML has four types of subclasses:● Complete or Partial. If every object in a class is a member of some, subclass the subclasses are complete; otherwise partial. ● Disjoint or Overlapping. If an object can be in two or more subclasses, the subclasses are overlapping; otherwise disjoint.○ Typically the object-oriented modelling only allows disjoint subclasses. 10Subclasses in UML: Examples11Complete and disjoint subclasses Partial subclassER2 12Weak Entity Sets• Occasionally, entities of an entity set need “help” to identify them uniquely.• Entity set E is said to be weak if in order to identify entities of E uniquely, we need to follow relationships from E to some other supporting entity sets and include the key of entities of the connected entity sets.ER2 13Weak Entity Set: ExampleA TV-show like The Simpsons, South Park, or Neighbours has many episodes. • Some show episodes do not even have a title, and so title can not, and usually is not used to uniquely identify a show episode. • Season number and Episode number are certainly not a key, since two episodes from different shows can have the same Season and Episode numbers.• But season and episode numbers together with the show name where the episode belongs to should be unique.The UML representation for weak entity sets14● A special “supporting” composition relationship notation can be used to represent the “supporting” relationship between the weak entity set and the supporting entity set. ○ A “PK” box indicates “supporting” composition.● The key for a weak entity set comprises of its own PK attributes as well as PK attributes of its supporting entity set(s). The UML representation for weak entity sets: Example15The Episode weak entity set is associated with the supporting “Show” entity set via the supporting composition relationship.● The “PK” box at the left end indicates that the complete key for Episode comprises of PK attributes of Episode as well as the PK attribute of “Show”. ● The solid diamond at the right end indicate “exactly one”.Weak entity sets can have regular relationships: Example• Aggregation from the Episode weak entity set to Studio. ER2 16ER2 17Multiway Relationships• Multiway relationships connect more than two entity sets.• Example: “A customer service consultant often books a delivery appointment with a delivery company for a customer”, which is a ternary relationship among entity sets Consultant, Delivery-company and Customer:– There do not exist any binary “booking” relationships between any two entity sets. Converting multiway relationships to binary• The ER model allows multiway relationships conceptually, but UML only has binary relationships. • Converting multiway relationships to binary: create a weak entity set and then relationships from it to the entity sets in the original multiway relationship.18ER2 19Example: Converting a ternary relationship to binaryThe ternary relationship “A customer service consultant often books with a delivery company for a customer”:- A weak entity set “Booking” is created- Binary relationships between Booking and entity sets Consultant, Company and Customer.Note that the weak entity set “Booking” does not have attributes of its own.ER2 20ER Diagram Design Principles• An ER diagram is a database schema and should only include entities and relationships for which data will be kept. – Entities keep data.– Relationships also keep data. • Avoid redundancy. – One piece of information is modelled only once.• Simpler is better.– Don’t use an entity set when an attribute will do.A bad designThe below ER model (UML diagram) has redundancy and is unnecessarily complex. 21A good design22ER2 23Mapping UML Diagrams to Relational Database Schemas: Basics• Classes to Relations: – attributes → attributes.– PK: PK attributes of classes• Compositions or Aggregations are NOT mapped to relations. Instead, add the PK attributes of the diamond-end class to the relation for the class at the other end.– The added attributes are foreign keys and can be null for aggregation. • Other associations to Relations: – PK attributes of the connected classes.– If an association class, add the attributes.– PK: PK attributes of classes Basic Mapping: ExampleMovie(mvID, title, rating, rel_date, length)Director(directorID, firstname, lastname, DOB)Actor(actorID, firstname, lastname, DOB)Cast(mvID*, actorID*)Direct(mvid*, directorID*) 24Basic Mapping: ExampleStudent(studentNo, firstname, lastname)Course(cno, title)Take(studentNo*, cno*, grade)25Mapping UML Diagrams to Relational Database Schemas: Advanced• The UML subclass hierarchy is inherently disjoint. For each level:– each (sub)class is mapped to a relation. – If complete: only the leaf level is mapped.– PK: PK attributes of the root class• Weak entity class to a relation:– PK attributes of the supporting class(es)– Add any attributes of the weak entity class– PK: PK attributes of the supporting classes and its own PK attributes (if any) 26Mapping a complete subclass hierarchy:
ExampleProfessional(professional_empID, givename, surname, deptID*)Academic(academic_empID, givename, surname, specialty, deptID*)Department(deptID, name, address)Field(fieldID, title, desc)Research(academic_empID*, fieldID*)Note: academicID are professionID are renamed to avoid confusion. 27ER2 28ER Constraints represent FDs • The constraints in ER diagrams can be represented as FDs on attributes, including– Key of entity sets– The multiplicity of relationship sets– Weak entity sets• There may exist other FDs not represented in the ER model. ER2 29Relational Database DesignRelational database design practice:1. Design an ER diagram. 2. Map the ER diagram into a relational database schema (following the mapping rules). • As most FDs are represented in ER models, if mapped correctly, the resulting relational model is most likely in BCNF or 3NF. 3. Examine the normal form of resulting relations, using FDs derived from the ER diagrams and any FDs not in the ER diagram. 4. If a relation is not in BCNF/3NF, decompose it into BCNF/3NF. ER2 30Relational database design: an example- The subclass hierarchy is partial.FDs• The ER model represents FDs:cNo→ cName, streetAddr, suburb, postcodepNo → pTitle, type, costingtypeeNo → deptName…• There are also other FDs:Suburb → postcodeType → costingtype31ER2 32Mapping into a relational database schemaClient(cNo, cName, streetAddr, suburb, postcode)Project(pNo, pTitle, type, costingtype)Department(deptName, location)Employee(eNo, givename, famname, deptName*)Contract_Emp(c_eNo, c_givename, c_famname, deptName*, contractNo*)Contract(contractNo, sign_date)WorkOn(cNo*, pNo*, eNo*)Mapping into a relational schema …Is each relation in BCNF/3NF?- Client(cNo, cName, streetAddr, suburb, postcode) is not in BCNF or 3NF, due to suburb→ postcode. Decompose:Client(cNo, cName, streetAddr, suburb*) ClientSuburb(suburb, postcode)- Project(pNo, pTitle, type, costingtype) is not in BCNF or 3NF, due to Type → costingtype. Decomposition:Project(pNo, pTitle, type) ProjectType(type, costingtype)33
