Entity Relationship Diagram Chapter 4 PART 1 pp

Entity Relationship Diagram Chapter 4 PART 1 pp. 93 -102 1

Outline Domain Modeling ERD - defined Entity, Attributes, Primary Keys Relationship Types § 1: 1, 1: M, M: M Associative Entities Multivalued Attributes Degrees of Relationships § Unary, Binary, Ternary ERD Rules & Tips

Domain Modeling Define system requirements by: § understanding “things” (within the problem domain) that needs to be stored Domain Modeling Techniques § Entity Relationship Diagram (ERD) – Ch 9 part 1 § Domain Class Diagram – Ch 9 part 2 3

Entity Relationship Diagram ERD § a data modeling technique § Used to later build the database 4

ERD: Entity § A noun - person, place, object, event, etc. • Within our scope & we need to remember it! 5

ERD: Attributes Attribute • A characteristic • Information about the entity that is within the scope Attribute Type Examples Atomic/Simple • Job. Title, Salary Composite • Name, Address Derived • Age, Total. Pay Separate Address: Street. Addr, City, State, Zip But don't separate Street. Addr house. No & street. Name 6

ERD: Primary Key An attribute (or a group of attributes) that uniquely identifies an instance § Every entity must have a Primary Key! § 3 Rules for a good Primary Key is… • Unique - no duplicate values • Required - cannot be null • Static - does not change (e. g. build name – poor PK) Tip: Try to match the PK name with the Entity Name • Order. No • Dept. Code 7

ERD: Other Terms Candidate Key § Multiple unique attributes may exist, if so pick the attribute that is used most often. Natural Key vs. Surrogate Key § e. g. Building – Building. ID, Build. Name, Sq. Feet, Floors, etc. § Building. Name is unique but its value may change over time. Thus, we should add a surrogate key (i. e. Building. ID) • Surrogate Key - an auto-generated integer defined as the Primary Key. • Non-Key Attribute § Any attribute that is not part of the primary key 8

ERD: Relationships Relationship § An association between entities § Represents a "NEED TO KNOW the relationship" between the instances of each entity § Occurs in two directions Left to Right: A Department employs _? _ Employees Right to Left: An Employee is employed by _? _ Department 9

ERD: Relationships Cardinality - # of associations between instances Min: • zero - optional • one - mandatory Max: • one • many 10

ERD: Relationships Type of Relationship § Determined by the _______ Cardinalities • One to One • One to Many • Many to Many 11

ERD: Relationships Be careful with mandatory 1’s on both sides Employee Contract Product Item Can have >1 relationship between the same 2 entities § Thus, use precise relationship names! What are the missing cardinalities? 12

Practice: ERD Academic Department "employs" Student "is member of" Student Organization is "sponsored by" Notes: • Faculty is anyone who teaches and/or does research Course is "offered by" Course "has" Faculty "teaches" Student "registers for" Student Organization Student • While Faculty may teach courses for different departments, they have only one “home” department and all decisions and performance evaluations are handled by the home department. • Student organizations are required to have at least one faculty sponsor. Academic Department Course Faculty Course. Section CRN -PK 13

Associative Entity Where do we place the following attributes? § Example 1: Employee and Project • § Example 2: Employee and Skill. Type • § Add Start. Date – the date that an employee started working on a particular project Add Prof. Level – how proficient is the employee at that skill Example 3: Student and Course. Section • Note: Course. Section’s PK is CRN (and assume CRN is unique & never reused) • Add Grade – the grade that a student made in a particular section 14

Associative Entity – the steps Now, let’s add the “the date that an employee started working on a particular project”: Start. Date Adding an Associative Entity § Know when to add an associative entity 1. 2. 3. 4. Name the new entity appropriately Determine the concatenated PK Determine the cardinalities Add any non-key attributes

Associative Entity The awards are annual awards & a student can receive a particular award more than once. Next, we want to keep a historical record of how much was awarded so we model the associative entity. What is the difference between Award. Amount & Amount. Awarded? 16

Associative Entity Does our current design allow a student to receive a specific award (e. g. Leadership) more than once? YES NO 17

Associative Entity We can resolve this design issue by just adding a date attribute to the concatenated Primary Key. This design resolves the issue: • A student can receive a particular annual award more than once. You may need this concept of adding a date field to a concatenated primary key for Historical Accounts/Logs. Date-related fields include: • Year • Month • Semester • Quarter • Date - for daily logs • Date Timestamp - for more precise entries Note: We are using “School. Year” because these are annual awards. If we erroneously used “Award. Date” instead, it would mean that a student could receive the award every day.

Order Form A business object is not always = 1 Entity. It may require more! ABC Office Furniture - Order Form For office use only: 1001 Order no: ______ 1/1/2019 Date: _____ 19

Determine what each attribute describes? That is, each attribute is a characteric of a(n) ______. Customer number - Subtotal - Name - Sales Tax - Address - Total - Phone # - Shipping - Order no - Amount Due - Date - Card type - Quantity - Name on card - Item # - Expiration date - Desc - Price - Total -

Order Form - ERD Solution

Any type of order should have these 2 entities…

What is wrong with this ERD? Textbook Fig 4 -8 24

Multi-Valued Attributes Rule of Atomic Values - no multi-valued attributes Assume we need to store data about dependents for medical benefits purposes. Employee Table Emp. ID Last. Name First. Name Title Dep. Names 111 Smith Robert Accountant Bobbie, Sue 222 Jones Leo Programmer 333 Lopez Trent Sales Rep Trent Jr. , Trevor, Sue 444 Miller Anne Accountant Mike Why are multi-valued attributes considered poor design? 25

Multi-Valued Attributes Resolution! Assume a dependent is carried on only one parent/guardian/spouse’s policy. Employee Table Emp. ID Last. Name First. Name Title 111 Smith Robert Accountant 222 Jones Leo Programmer 333 Lopez Trent Sales Rep 444 Miller Anne Accountant What is the primary key? Emp. Dep Table Emp. ID Dep. Name 111 Bobbie 111 Sue 333 Trent Jr. 333 Trevor 333 Sue 444 Mike 26

Multi-Valued Attributes Sometimes we resolve the multi-valued attribute… …and then later notice that there actually more attributes of interest. 27

Understand these entities … general specific 28

Know how to name entities… An entity’s name should represent one instance/record in the table!

ERD: Degree of the Relationship Refers to how many ______ participate in the relationship. § Unary Relationship • aka: Recursive Relationship • aka: Self-Referencing Entity § Binary Relationship § Ternary Relationship § n-ary Relationship 30 30

Unary Relationships manages has prereqs contains (is a prereq for) (is managed by) Employee (is a component of) Course Item $873 A 1 Bob John C 1 Taylor C 3 B Sue Mike C 2 C 4 Jen C 5 C 6 C 3 $100 E C 7 C 8 D 1 $400 1 $270 1 $200 F G 2 $50 E 1 $200 1 $225 F 1 $50 I 2 $100 31

ERD Rules & Tips - a Summary

ERD Rules & Tips Reminder & Tips For example… Entities Entity name - use a singular name • Employee not Employees An entity with only one instance should not be modeled! • We would model an entity called Student which would have over 17, 000 instances (i. e. students) • We would NOT model an entity called Baylor as it would just have one instance. Primary Keys Primary keys are Required!! A good primary key… 1. must be unique 2. must always have a value 3. doesn't change over time A good primary key name: • should match the entity name • All entities must have a primary key attribute denoted. These are not good PKs: • Name may have duplicates • SSN everyone may not have a SSN • Building. Name it may be renamed Use the Entity name and append: ID, No, Code, etc • Employee. No • Student. ID 33

ERD Rules & Tips Reminder & Tips For example… Attributes A good attribute name… • specific name • don’t add entity in the name • start. Date not date • start. Date not employee. Start. Date No Composite attributes! • No: address • No: name Yes: address, city, state, zip Yes: first. Name, last. Name No Derived attributes! • No: total. Pay • No: age Yes: hours. Worked, pay. Rate Yes: Birth. Date No Redundancies! • Someone’s birthdate should appear in only one place in the entire ERD. Repeating that fact is redundancy! 34

ERD Rules & Tips Reminder & Tips For example… Relationships In Analysis, only resolve a many-to-many when there is a non-key attribute. • Otherwise, the model will be unnecessarily busy. In Design, however, we will resolve all many-tomany’s. Name all Relationships (even on associatives) Be careful with mandatory one-to-one relationships • In this case A & B may be the same concept. If so, use one entity! Miscellaneous Report/Form is usually >1 entity • Order Form may need 4 entities 35