Entity Relationship Model Ch. 3 - Chen

Entity Relationship Model Ch. 3 - Chen

Conceptual Data Models Chapter 3 Conceptual Data Model Requirement collection/analysis results in well-formed requirements Can be used to create conceptual data model diagram Useful in understanding DB Used to map to DB model of DBMS Examples are: ER/EER model UML

Conceptual Mapping Conceptual model can then be mapped to: Relational NoSQL key value, graphical Data warehouses What are the basic aspects of a DB? Must identify objects in DB, characteristics of the objects and the relationships among them ER model Entity-Relationship ER model Chen

High-level conceptual model Concepts Entity basic object from real-world Entity type defines collection/set of entities with same attributes box in diagram Entity set collection of entities of entity type (instances) Attributes Describes property of entity set Oval in ER diagram

Atomic vs. composite (levels of ovals) Single-valued vs. multivalued (double oval) Stored vs. derived (dashed-line oval) Key attribute (name underlined) Example of multivalued composite Attributes contd

Every entity has a value for each attribute Null valued Not applicable Missing Not known/not applicable or missing Attribute associated with domain of values Relationship Relationship one entity type refers to another entity type Relationship type set of associations between entities Relationship set collection of all

relationships of relationship type Diamond in ER diagram Relationship contd Relationship types of different degrees: Binary (typical) two entity types participate Ternary three entity types participate Higher degrees (rare) Relationship instance associates an entity from each set that participates Can participate in > 1 relationship Recursive relationship same entity participates in

relationship Role names (on lines) Relationships contd Structural constraints on relationship types Cardinality ratio - # of instances 1:1 relationship 1:N relationship M:N relationship Participation constraint Total participation Partial participation Relationship attributes

Structural Constraints 2 options: 1. Separate notation One for cardinality ratio One for participation constraints 2. Combine notation ERD Tool Also notation from textbook Min, Max ERD Tool Notation Notation structural constraints One: | Many (N) : > (outer notation)

Mandatory: | Optional: 0 (inner notation) NOTE: N is on same side as previous model but participation is on the opposite side Employee must work for one and only one department Employee works for one or no department EID Name

dname dnum Address Employ ee EID dname Name

location Department Works_f or Department has one to many employees working for it dnum

Adress Employee Works_f or Department location Separate notation Cardinality ratio( # on line) Participation constraint (double line for total) e.g. If N employees work for 1 department, put N on line from employee to works_for and 1 on line from

department to works_for If must work for a department, double line, else single line Name dnum dname Address EID Employee N

1 Works_f or location Department Min,Max (min, max) where each entity participates in at least min and at most max relationships captures both cardinality ratio and participation constraints If min = 0, means partial, if min >= 1, total participation

e.g. (1, 1) Employee works for 1 and only 1 department (1, N) Departments have working for it at least 1 and at most N employees On which side to put min,max? Text book uses opposite side of ERD tool. We will be consistent with ERD tool. EID dname Name dnum

Adress Employee (0,1) Works_f or (1,N) Department location Different ways to represent structural constraints

Textbook Notations Name dnum dname Address EID Employee EID 1

N location Works_f or Department dname Name dnum

Adress Employee (0,1) Works_f or (1,N) Department location

ERD Tool Name dnum dname Address EID location Employee Works_f or

Department 1:M relationship, where participation on the 1 side is optional and participation on the M side is mandatory. - A health care organization keeps track of its doctors and outpatient locations For each doctor it keeps track of the DoctorID (unique), DoctorName, and DoctorYearOfMDGraduation (year of graduating from medical school) For each outpatient location it keeps track of the OLID (unique) and OLName Each doctor works at either one outpatient location or at none (strictly working in the main hospital) and each outpatient location must have at least one but can have many doctors working at it

1:M relationship, where participation on the 1 side is optional and participation on the M side is mandatory. 1:M relationship, where participation on the 1 side is optional and participation on the M side is mandatory. - A health care organization keeps track of its doctors and outpatient locations -- For each doctor it keeps track of the DoctorID (unique), DoctorName, and A health care organization keeps track of its doctors andmedical outpatient locations

DoctorYearOfMDGraduation (year of graduating from school) For each each outpatient doctor it keeps trackit of the DoctorID (unique), DoctorName, and For

location keeps track of the OLID (unique) and OLName DoctorYearOfMDGraduation (year of graduating school)working in the Each doctor works at either one outpatient locationfrom or atmedical

none (strictly For each outpatient location it keeps track ofmust the OLID main hospital) and each outpatient location have at(unique) least oneand

butOLName can have many Each doctor works at either one outpatient location or at none (strictly

working in the doctors working at it main hospital) and each outpatient location must have at least one but can have many doctors working at it Weak entity sets Weak entity sets have no key attributes of their own (double box) Always related to identifying owner via identifying relationship (double diamond) Total participation (double line) Partial key (dashed underline) ER diagram

Fig. 7.7 shows all ER components Mini-world is company DB (textbook) The database will keep track of employees and departments. For each employee we will keep track of their name and their unique employee ID. For each department we will keep track of the unique department ID and location. Each employee reports to exactly one department. A department may have many employees reporting to it. Mini-world is company DB (textbook) initial requirements Company has departments and department

managers Employees in company work for a department and work on project Projects are associated with a department Employee has name, SSN, address, salary, sex, birth date For each project keep track of number of hours worked on project Maintain information about dependents for insurance Company DB requirements refined http://cs457.cs.ua.edu/Company DB Requirem ents.pdf

Company is organized into departments Each department has a unique name, unique number and an employee who manages the department Keep track of the start date when employee began managing department Department has several locations Department controls a number of projects Each project has a unique name, unique number and a single location Company DB requirements Contd Each employee has a name, SSN, address, salary, sex, and birth date Employee is assigned to one department but works on several

projects which are not necessarily controlled by the same department Keep track of the number of hours per week an employee works on each project Keep track of supervisor of each employee Want to keep track of the dependents of each employee for insurance Keep each dependents first name, sex, birth date, relationship to employee Company DB What were the 1:1 relationships? What were the 1:N relationships? What were the N:M relationships?

Clarification to requirements assumptions? Missing information in requirements Planned use? User groups? Typical operations? ER diagram ER diagram (ERD) for the company database in the 2 different styles follows Company DB using ERD Tool Company Minit

( Name ) Salary Department Sex Bdate Works_For Employee Start_date

SSN Manages Superv ision Controls Dependents_Of Hours Works_on Project

Dependent Name Relationship Sex Number Name Address Fname

Locations Lname Name Number Birth_date Location Num_of _Employees 7.2

How would the diagram be different if location were a separate entity? Ternary Useful for many-to-many-to-many relationships When should one use a ternary? Example of Ternary Relationship: a lecturer recommends a certain text for a certain course. Objects lecturer, text, course Could try to capture this ternary relationship with binary relationships

Lecturer teaches one or more courses Text used in course Lecturer recommends a text In Fig. 3.8(b) an attempt has been made to replace the ternary relationship type with two binary relationship types. LECTURERs 'recommend' TEXTs and TEXTs 'are_used_on' COURSEs. The fact that a lecturer recommends a text and that text is used on a course does not necessarily mean that that lecturer recommended that text for that course. The text might be used on the course and recommended by someone else, whereas our lecturer does recommend that text but for a different course. In Fig. 3.8(c) it is possible to tell which texts a lecturer recommends

and which courses he or she teaches on, but not which texts are used on a course or which courses use a given text. The fact that a lecturer recommends a text and teaches a course does not imply that he or she recommends that text for that course. In Fig. 3.8(d) it is possible to tell which courses a lecturer teaches and which texts a course uses but not which texts a teacher recommends. Only if every course had only one lecturer would (d) be satisfactory because then the fact that a course used a text implies who recommended it. Otherwise (d) is unsatisfactory. If many-to-many-to-one, can just use binary relationships (depending on requirements)

Not true of many-to-many-to-many In Fig. 3.8(e) it is possible to tell who recommends which texts, who teaches which courses, and which texts are used on which courses. However it is still not possible to ascertain, in general, the answers to questions like: 'Which text does Mr Smith recommend for the 4th year Database course?' The reason is that even though Mr Smith may recommend text1 and Mr Smith teaches on 4th year Database, it is not known whether it was Mr Smith who recommended the text for the course, because he may have recommended the text for another course, and another lecturer on the 4th year Database course may have recommended text1. The only satisfactory relationship type is that shown in Fig. 3.8(a).

UML UML Notation Universal Modeling Language Objects (entities) are classes (box) box contains the following separated by lines: Object name Attributes method names Attributes in UML Composite attribute modeled as structured domain e.g. Name: NameDom

Fname Minit Lname Can specify domain of attribute by using : e.g. Sex: {M, F} Multivalued attribute modeled as a separate class e.g. separate box, with aggregation notation using a diamond Relationships - associations Relationship types are associations (lines) Can be named (name on line)

Relationship instances are called links (min, max) notation used - same as ERD tool * indicates no limit Relationship (link) attributes Dashed line to box Box with association name and attribute Weak entities Weak entity qualified association placed in box directly attached to owner class Partial key in box

Remaining attributes considered an aggregation (has a) Multi-valued attributes represented by aggregation notation Issues Primary keys Typically added as a tag next to attribute name

Recently Viewed Presentations

  • ARTTALK Chapter 5 Line Before we start, here

    ARTTALK Chapter 5 Line Before we start, here

    Blessing Rain Chant. 1992. Acrylic on canvas. 78 x 120". Niman Fine Art , Santa Fe, New Mexico. ... Edward Hopper. Early Sunday Morning. 1930. Oil on canvas. 35 x 60". ... Thomas Hart Benton. Country Dance. 1929. Oil on...
  • Bellringer

    Bellringer

    Bell Ringer. Describe one way estuaries are degraded: Sedimentation (excess sediment due to erosion and deposition) ... Geysers - boiling hot springs that erupt at regular intervals, gushing hot water and steam. Old Faithful - geyser in Yellowstone National Park...
  • Stats in a Hurry

    Stats in a Hurry

    Stats in a Hurry. Mean, Median, Standard Deviation, and more. A WUTUG presentation by Cindy Traub and Maggie Konich
  • Creating a thinking culture - Nha Trang University

    Creating a thinking culture - Nha Trang University

    Getting back into it… What questions do you have about making it happen? What was the most significant learning concept? the practice of teaching goes beyond just being competent to being able to make the lesson interesting and lively as...
  • Diapositive 1 - Overblog

    Diapositive 1 - Overblog

    Partez avec lui dans les eaux tumultueuses pour découvrir comment il réalise ces magnifiques photographies. Ray Collins est un jeune homme tout ce qu'il y a de plus normal. Il y a quelques années, cet Australien travaillait encore en plein...
  • A Proposed Mentoring Program - stc-siliconvalley.org

    A Proposed Mentoring Program - stc-siliconvalley.org

    A Proposed Mentoring Program. for STC Silicon Valley. Philip Powers - 2018. Based on content and methods developed by . Dan Voss, Bethany Bowles, & Sally Henschel
  • The rationale for arbitration on debt

    The rationale for arbitration on debt

    Die Rolle der G8 im internationalen Finanzsystem Philipp Hersel Berliner Landesarbeitsgemeinschaft Umwelt und Entwicklung (BLUE 21) [email protected]
  • Week 19: Sweet and Sour (Revelation 10) 7

    Week 19: Sweet and Sour (Revelation 10) 7

    Rainbow- covenant promise accompanies judgment. Holding a 'little' scroll. Option #1 of what the scroll is- unsealed scroll opened by the Lamb (Rev 6-8) ... Psalm 119:103How sweet are your words to my taste, sweeter than honey to my mouth!...