Conceptual DB Design Conceptual database design ERDs Relationship
Conceptual DB Design Conceptual database design ERD’s
Relationship l l Meaningful associations among entities We draw the “relationship type”—the abstract set of associations l l l Relationship occurrence (Relationship Instance) —the specific relationship between two entity instances l l Faculty teach Course Patient has_a Lab_result Sward teaches N 6803 ERD diagram shows the general (relationship type) but you might need to think about instances to decide the general cardinality
Diagramming a Relationship l l l One line between entities. Line does not have arrows Label with a verb or short phrase Relationships documented in both directions l UML typically labels in only one direction l l But sometimes it’s not clear which direction the verb is describing I want you to label verb BOTH directions
Diagramming a Relationship is ONE straight line, no arrows. Show both directions of relationship Above and below the line have name (verb) and arrow The numbers in parentheses are cardinality. I’ll explain that soon.
Relationship l Read the relationship in the direction of the arrows l l student takes class reads left to right: student takes class student has class is read right to left: “class has students”
Drawing tip l l Try to be consistent in drawing the relationship —for example, top of relationship read left to right and bottom read right to left; for vertical relationships might put left side read bottom to top and right side read top to bottom. The goal is clarity and readability of the design —don’t want to have to flip the diagram around multiple times in order to read it!
Structural Constraints l Cardinality l l Minimum and maximum number of instances that can participate in a relationship How many instances (rows) of an entity might relate to ONE instance of another entity
Cardinality - minimum l Think about person and vital signs in the SNDB database l l l Does every person HAVE to have a row in the vital signs table? If I think of ONE generic person, how many vital signs can they have? For ONE row in my vitals table, how many people can that set of vital signs belong to?
Cardinality - minimum l Person has Vitals l l Does every person HAVE to have a row in the vital signs table? If it is possible that a person might not have any vital signs recorded, then the minimum cardinality is 0. 0 means a person might not have a row in the vital signs table. If every person must have an entry in the vital signs table, then the minimum cardinality is 1. Person in SNDB has vital signs, minimum cardinality is 0
Cardinality - minimum l Vitals belong to Person l l Now think about the relationship the other direction. If you choose a row in the vital signs table is it possible that this row does not match any person? If you can have vital signs without belonging to a person, the minimum is 0 If every vital sign entry must belong to a person in the demog table, the minimum cardinality is 1. In the SNDB, vital signs belong to person relationship, minimum cardinality that direction is 1
Cardinality - maximum l Think about person and vital signs in the SNDB database l l If I think of ONE person, what is the most number of vital signs can they have? If I think of ONE row in my vitals table, what is the most number of people that set of vital signs can belong to?
Cardinality - maximum l Person has Vitals l l Is it possible the person could have more than one record in the vital signs table? If no, maximum cardinality is 1 If yes, maximum cardinality is Many (shown as M, N, or *) A person in SNDB can have multiple vital signs, so maximum cardinality is M
Cardinality - maximum l Vitals belong to Person l l Now think about the relationship the other direction. If you choose a row in the vital signs table is it possible that this row would match more than one person? If no, maximum cardinality is 1 If yes, maximum cardinality is Many (shown as M, N, or *) In the SNDB, vital signs belong to person relationship, maximum cardinality that direction is 1
Cardinalities review l Minimum Cardinality l l Maximum Cardinality l l l Fewest number that can be associated with other entity (your choices are 0 or 1) Largest number that can be associated with other entity (your choices are 1 or M) Establish BOTH directions of the relationship Notation is (min, max) note the COMMA here comma is read as “and”
Examples of Cardinalities l Person has vitals l l Person may not have any vital signs Person may have multiple vital signs Person has (0, M) vitals min =0 max =M Many is represented as * or M or N Vitals belong to person l l l A row in the vitals table must belong to a person Each row belongs to ONLY one person Vitals belong to (1, 1) person min = 1 max = 1
Cardinalities l On diagram, put in parentheses after the point of arrow l l Takes --> (0, N) Student could take no classes, might take many classes (0, N) <-- Has Class might have no students, could have many students
Details, details l Use a comma (X, Y) for our ERD l l l Your book uses two dots (X. . Y) Read as X and Y. You’d read (1, *) as “one and many” Place by the entity to which the cardinality refers (read the direction of the arrow) l Patient has (0, *) Allergy (1, 1) belongs to Each patient has (min of 0 and up to many) allergies Each allergy belongs to 1 and only 1 patient
Cardinality and Participation l l Unfortunately, the terminology can be confusing Some authors say minimum and maximum cardinality. We use this verbiage. Connolley and Begg use “cardinality” for the maximum and “participation” for the minimum Other authors use “optional” and “mandatory” for the minimum (0=optional, 1 = mandatory)
Cardinality Placement l Look Here Notation l Client has many visits Client (0, *) l has Visit Look Across Notation l Client has many visits Client has (0, *) Visit Classic ERD cardinality is placed by the “first” entity when you read the relationship Client has 0 and many visits, the 0, * goes near client UML and This class Cardinality is placed after the verb. Reads like English: subject, verb, [cardinality], object Client has 0 and many visits
Relationship Degree l Number of entities participating in a relationship l l l Binary = two entities Unary (AKA Recursive AKA Ring) = one entity Ternary (3 entities), N-ary (3 or more…)
Binary Relationships l Most common. Two entities Employee has (1, *) Employee. ID (1, 1) belongs to Name … Salary. History Employee. ID Date. Of. Change …
Recursive (Unary) l l Single entity Two rows in the same table are connected to each other in some manner, with different ROLES l l for example, an employees table might link individual employees to their manger Use one line, but two verbs and two cardinalities
Recursive Relationships l Different rows in same table related via different roles manages (0, *) (1, 1) reports to Employee. ID Name …
Dealing with Recursives l Think of the relationship “unfolded” ! Employee (Manager) Employee. ID Name … manages (0, *) (1, 1) reports to Employee (staff) Employee. ID Name … We only draw the entity ONCE on the ERD because of the principle of minimality
Ternary, N-ary l Sometimes it takes more than 2 entities to describe a relationship (books used by a teacher in a class) l Called an N-ary relationship (N = any number of entities; sometimes specified as ternary etc, but more often described as N-ary)
N-ary Cardinalities l Exception to our cardinality placement rules! Establish cardinality one way for each entity. Course Faculty (1, *) Empl. Id … (1, *) Course. ID … (0, *) Book ISBN Book. Title … Imagine you have one faculty and one course. How many books can you have (min and max)? Now imagine you have one book and one course, how many faculty match that? . . .
HW - Cardinalities 1. Each employee reports to 1 manager. A manager might not have any employees (new manager), or they might have several. Recursive. Employee reports to (1, 1) manager; manager has (0, *) employees 2. Each employee has at least 1 salary history record (they might have more, they get a new record every time they make a change). Each record in the Salary table belongs to 1 employee. Binary. Employee has (1, *) salary history; salary history belongs to (1, 1) employee Etc.
ERD Thoughts l Is it possible for an entity to not be related to any other entity? l l l Yes, but it’s rare Draw as an isolated entity with no relationships Can entities be related more than once? l l Yes, if the relationships have different meanings There are roles associated with the separate meanings
Two relationships, same entities Draw this way on the ERD— two relationship lines but each entity appears only once. Principle of Minimality Two relationship lines implies that these are two separate relationships! Each relationship will be dealt with individually in logical design.
- Slides: 29