E-R Diagram: Weak Entity, Subclass

2y ago
53 Views
2 Downloads
1.01 MB
38 Pages
Last View : 1d ago
Last Download : 2m ago
Upload by : Joanna Keil
Transcription

E-R Diagram:Weak Entity, SubclassCS 4750Database Systems[A. Silberschatz, H. F. Korth, S. Sudarshan, Database System Concepts, Ch.6][Ricardo and Urban, Database Illuminated, Ch.3]Spring 2020 – University of Virginia Praphamontripong1

Recap: Self-Referential, Multi-WayGiven the following E-R diagram. Discuss with yourneighbors, come up with an example that can berepresented by the diagram.vendorbuysproduct“Microsoft buys a printer from HP”Spring 2020 – University of Virginia Praphamontripong2

Recap: Multi-Way RelationshipsSuggest how we may convert the 4-way relationshipinto an E-R diagram with binary sgenrestarscontractsstudioof starproducingstudiostudiosnameSpring 2020 – University of Virginia Praphamontripongaddress3

Recap: Multi-Way RelationshipsSuggest how we may convert the 4-way relationshipinto an E-R diagram with binary relationshipsSpring 2020 – University of Virginia Praphamontripong4

revisitE-R Diagram: Building Blocks(string) Entity setAttributeRelationshipWeak entitySubclassis aNote: colors are not part of E-R Diagram. They simply are used to increase readability.Spring 2020 – University of Virginia Praphamontripong5

Strong and Weak Entity SetsStrong entity set Entities can be identified by the values of their attributes(a primary key) (what we have been discussing so far)Weak entity set Entities cannot be identified by the values of their attributes There is no primary key made from its own attributes An entity can be identified by a combination of theirattributes (“discriminator”) and the relationship they havewith another entity set (“identifying relationship”)Spring 2020 – University of Virginia Praphamontripong6

Weak Entity SetBy definition, there must be totalparticipation between the weak entity setand its identifying relationshipDiscriminatorpayment numberdateTotalparticipationpaymentpayment amountWeakentity setl Strongentity setDoes not have sufficient attributes to form a primary key.Depends on the strong entity set it is associated with.Needs a discriminator and a primary key of the strong entity set.Spring 2020 – University of Virginia Praphamontripong7

Let’s try: Weak Entity SetWhat can be concluded from the following E-R diagram?hw numberdue datehomeworkc numberhavecoursetitletotal scoresHomework cannot exist without a course.Every homework must belong to a single class.A course can have many homework.Different courses may have the same homework number.To identify a homework, we need c number and hw number.Spring 2020 – University of Virginia Praphamontripong8

Let’s try: Weak Entity SetDraw an E-R diagram for the following scenarioA movie studio might have several film crews.The crews might be designated by a crew’s number as crew1,crew2, and so on.Each crew has multiple phone numbers.Other studios might use the same designations for crews, so theattribute crew’s number is not a key for crews.To name a crew uniquely, we need to give both the name of thestudio to which it belongs and the crew’s number.Spring 2020 – University of Virginia Praphamontripong9

Let’s try: Weak Entity SetDraw an E-R diagram for the following scenariocnumberphonecrewsnameunit ofstudiosaddressA movie studio might have several film crews.The crews might be designated by a crew’s number as crew1, crew2, and so on.Each crew has multiple phone numbers.Other studios might use the same designations for crews, so the attribute crew’snumber is not a key for crews.To name a crew uniquely, we need to give both the name of the studio to which itbelongs and the crew’s number.Spring 2020 – University of Virginia Praphamontripong10

Subclassing An entity set may contain entities that have special propertiesnot associated with all members of the set Subclasses special-case entity sets Isa (or Is-a) special kind of relationship (one-to-one)pricenamecategoryproductSimilar to classes in OO(super-classes and sub-classes)is ais aedu productsoftwareage groupSpring 2020 – University of Virginiaplatforms Praphamontripong11

SubclassingSubclasses aremutually exclusivenamepricecategorySubclasses implicitlyinherit superclass keyand attributesproductis anameis acategoryedu productsoftwarepricepricecategoryage groupplatformsnameGeneralization / SpecializationThe triangle points to the specializationSpring 2020 – University of Virginia Praphamontripong12

Done with the building blocksLet’s transition to design decisionand converting E-R diagram into Relational designsSpring 2020 – University of Virginia Praphamontripong13

Recap: Entity vs. AttributeWhat are main differences between entities andattributes? Entities can model situations that attribute cannotmodel naturally Entities can participate in relationships Entities can have attributes Attributes cannot do any of theseSpring 2020 – University of Virginia Praphamontripong14

Design DecisionFor each tuple(person, product, vendor),there is a value of vendorIDShould “price” be an entity or an attribute?Spring 2020 – University of Virginia Praphamontripong15

Design Decision (2)“price” as an oductIDvendorIDSince “price” is just the actual amount, treating it as an attributeis adequate. No need to make it an entity“price” as an attributepersonIDSpring 2020 – University of VirginiapersonpricebuysproductvendorvendorID PraphamontripongproductID16

Design Decision (3)How about this?buypersonIDproductIDvendorIDprice A “person” is an attribute of “buy” A “vendor” is an attribute of “buy” A “product” is an attribute of “buy” Cannot model something about a “person” (or “vendor” or“product”) such as date-of-birth, address A “person” will involve in any relationship “buy” is associated withSpring 2020 – University of Virginia Praphamontripong17

Decisions to Make Entity set vs. attributes Has more dataà entity set Is the dataà attribute Entity set vs. relationship set Entity setà nouns (students, faculty, loads, ) Relationshipà possession verbs (teaches,advises, owns, works for, ) Binary vs. n-ary relationship sets Specialization / generalizationSpring 2020 – University of Virginia Praphamontripong18

Rules of Thumb Keep it simple Don’t over complicate things Choose the right elements (entities vs. attributes) Choose the right relationships Follow the specification of the application Avoid NULL value Avoid redundancy Consider small number of tablesSpring 2020 – University of Virginia Praphamontripong19

E-R Diagrams to RelationsThere is a unique table which is assigned the name of thecorresponding entity set or relationship setnamecnamedescriptionpidproductmakesproduct(pid, name, description)company(cname, address)addresscompanySchemastatementmakes(cname, pid)Spring 2020 – University of Virginia Praphamontripong20

Strong Entity SetDirect map:entity name à relation nameAttributes à columnsPrimary key: same as mpanyproduct(pid, name, description)company(cname, address)Spring 2020 – University of Virginia Praphamontripong21

Strong Entity Setwith Composite AttributeCreate separate attributes for each componentDon’t include the higher level attributefirstnamepidmiddlelastpersonperson(pid, first name, middle name, last name)Spring 2020 – University of Virginia Praphamontripong22

Strong Entity Setwith Multivalued AttributeCreate a separate table for the multivalued attributeName the table with the concatenation, separated by “ ”entityname attributenamePrimary key: all attributesnamepidphonepersonperson(pid, name)person phone(pid, phone number)Spring 2020 – University of Virginia Praphamontripong23

Weak Entity SetLet A be a weak entity set and B be the identifying strongentity set on which A dependsCreate a table with primary key of B and all A’s attributesPrimary key: primary key of B (strong entity) anddiscriminator of Ahw numberdue datehomeworkc numberhavecoursetitletotal scoreshomework(c number, hw number, due date, total scores)Spring 2020 – University of Virginia Praphamontripong24

Relationship Set: Many-to-ManyTable: primary keys of both participating entity sets andany attributes on the relationship itselfPrimary key: primary keys of both participating entity anyquantitymakes(pid, cname, quantity)Spring 2020 – University of Virginia PraphamontripongPrimary keys of bothentities25

Relationship Set:Many-to-One / One-to-ManyTable: primary keys of both participating entity sets andany attributes on the relationship itselfPrimary key: primary keys of the entity set on the ddresscompanyquantitymakes(pid, cname, quantity)Spring 2020 – University of Virginia PraphamontripongPrimary key of the“many” side26

Relationship Set: Total ParticipationBecause the total participation requires all entity to beparticipated in the relationshipà add the primary key of the “one” side to the “many”side entity set, no table for relationship neededPrimary key: primary keys of the entity set on the ddresscompanyquantityproduct(pid, name, description, cname, quantity)Spring 2020 – University of Virginia Praphamontripong27

Relationship Set: One-to-OneTable: Either side can be used as the main table(Which side? doesn’t matter. Pick the one that makes themost sense)Add the other side’s primary key to itPrimary key: primary keys of the entity set you anyquantitycompany(cname, address, pid, quantity)Spring 2020 – University of Virginia PraphamontripongPrimary key of thechosen entity28

Recap: E-R to Relations (1)Convert the following E-R diagram into iesstudiosnamelengthownsaddressstars(name, address)stars-in(title, year, starsName)studios(name, address)owns(title, year, studioName)movies(title, year, genre, length)Spring 2020 – University of Virginia Praphamontripong29

Recap: E-R to Relations (2)Convert the following E-R diagram into relationsnumberdayaircraftto flightflightsrowbookingscustIDcustomersto custseatnamephoneaddressflights(number, day, aircraft)customer(custID, name, phone, address)bookings(number, day, custID, row, seat)Spring 2020 – University of Virginia Praphamontripong30

Subclass (Option 1)namepriceKeep everythingcategoryPrimary key of the lower levelentity set: from thehigher levelproductis aedu productage groupis aDrawback: need to access moretables to get info aboutthe lower levelssoftwareplatformsproduct(name, price, category)edu product(name, age group)software(name, platforms)Spring 2020 – University of Virginia Praphamontripong31

Recap: Subclasses (option 1)Convert the following E-R diagram into relationsnamedept chairnumberdeptsgiven bycoursesroomcomputerallocationIs aIs alabcoursesseminarcoursesresearchdepts(name, dept chair)courses(deptName, number, room)labCourses(deptName, number, computerAllocation)seminarCourses(deptName, number, research)Spring 2020 – University of Virginia Praphamontripong32

Subclass (Option 2)namepriceKeep specialization entity setscategoryNo table for generalization entitysetproductis ais aedu productsoftwareage groupPrimary key of the lower levelentity set: from thehigher levelplatformsDrawback: redundancy if entitieshave more than onespecializationedu product(name, price, category, age group)software(name, price, category, platforms)Spring 2020 – University of Virginia Praphamontripong33

Recap: Subclasses (option 2)Convert the following E-R diagram into relationsnamedept chairnumberdeptsgiven bycoursesroomcomputerallocationIs aIs alabcoursesseminarcoursesresearchdepts(name, dept chair)labCourses(deptName, number, room, computerAllocation)seminarCourses(deptName, number, room, research)Spring 2020 – University of Virginia Praphamontripong34

Subclass (Option 3)namepriceKeep generalization entity setcategoryNo table for specialization entitysetsproductis ais aedu productsoftwareage groupDrawback: NULL in attributesfrom specializationentity setsplatformsAlthough less duplication of data,need to handle NULL valueproduct(name, price, category, age group, platforms)Spring 2020 – University of Virginia Praphamontripong35

Recap: Subclasses (option 3)Convert the following E-R diagram into relationsnamedept chairnumberdeptsgiven bycoursesroomcomputerallocationIs aIs alabcoursesseminarcoursesresearchdepts(name, dept chair)courses(deptName, number, room, computerAllocation, research)Spring 2020 – University of Virginia Praphamontripong36

Subclass: Design DecisionDepending on the number of attributes of thegeneralization entity set and specialization entity set If balanced à do option 1 (create all) If more attributes in specialization à do option 2 Ifmore attributes in generalization à do option 3In general, design decision depends on The number of attributes DBadministrator’s decisionOverall goal: minimize duplication(there is no one correct way)Spring 2020 – University of Virginia Praphamontripong37

Wrap-Up Weak entity sets Subclasses Converting from E-R diagrams to relational designs Turn each entity set into a relation with the the same set ofattributes Replace a relationship by a relation whose attributes are thekeys for the connected entity sets Weak entity sets cannot be translated straightforwardly torelations “Is a” relationships and subclasses require careful treatmentWhat’s next? Apply the concept to database scenariosSpring 2020 – University of Virginia Praphamontripong38

Let’s try: Weak Entity Set homework have course c_number title hw_number total_scores due_date Homework cannot exist without a course. Every homework must belong to a single class. A course can have many homework. Different courses may have the same homework number

Related Documents:

WIRING DIAGRAM DIRECTORY WARNING: Wiring direct to battery not recommended. Install actuator after ignition switch, or master power switch. Diagram 1 Diagram 2 Diagram 3 Diagram 4 Diagram 5 Diagram 6 Diagram 7 Diagram 8 Diagram 9 Diagram 10 Diagram 11 Diagram 12 Diagram 13 Diagram 14 Diagram 15 On/Off, 12VDC, 3-wire/3-pin, SPST On/Off, 12VDC, 3 .

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity. Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner, many weak entities). Weak entity set must have total participation in this identifying relationship set. Employees ssn name lot

1 IV. Entity Relationship Modeling 2 Entity-Relationship Model (ERM) Basis of an Entity-Relationship Diagram (ERD) A design technique Diagrams entities sets (with attributes) and the relationship between the entity sets. Recall previous definitions Entityrefers to the entity set and not a single entity occurrence E-R diagrams are the deliverablesof the

Structured analysis Object-oriented analysis System boundary Context diagram Use case diagram Functionality Data flow diagram Activity diagram Interaction diagrams Data Entity-relationship diagram Class diagram Object diagram Control State diagram State diagram Structured methods System as a set of nested processes accessing system data.

Note: The diagram shown above is for reference only. Use the instructions beginning on the next page to draw your Package diagram. Creating a Package Diagram from the Use Case Diagram The package diagram is a type of class diagram and can be created from a use case diagram. 1. Make sure the Use Case diagram c

Figure 2: A sequence diagram that has incoming and outgoing messages Notice that in Figure 2 the diagram's label begins with the letters "sd," for Sequence Diagram. When using a frame element to enclose a diagram, the diagram's label needs to follow the format of: Diagram Type Diagram Name UML basics: The sequence diagram Page 3 of 24 http .

Wiring Diagram (Code 9983) M17050 1 Wiring Diagram (Code 10021) M17053 1 Wiring Diagram (Code 10110, 10341) M17464 1 Wiring Diagram (Code 10373, 10526, 10535, 10541) M18331 1 Wiring Diagram (Code 10274, 10377, 10536) M18332 1 Wiring Diagram (Code 10375, 10537) M18333 1 Wiring Diagram (Code 10376, 10538) M18334 1 Wiring Diagram (Code 10886) M19988 1

Windstar Fuse Diagram · 1996 Honda Civic Stereo Wiring Diagram . 1988 Ford F250 Fuse Box Diagram · Chrysler Rear View Mirror Wiring Diagram . Diagram · Wiring Diagram For Jeep · Yamaha Bruin 350 4x4 Wiring Diagram . 1997 Audi A6 Quattro Engine Diagram · Yamaha Timberwolf Wiring .