1 / 26

Data Modeling with ERD

Data Modeling with ERD. BUS 782. Entities. An entity is a person, place, object, event, or concept in the user environment about which the organization wishes to maintain data. Person: Employee, Student, patient Place: Warehouse, Store Object: Product, Machine.

london
Download Presentation

Data Modeling with ERD

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Modeling with ERD BUS 782

  2. Entities • An entity is a person, place, object, event, or concept in the user environment about which the organization wishes to maintain data. • Person: Employee, Student, patient • Place: Warehouse, Store • Object: Product, Machine. • Event: Registration, Sale, Renewal • Concept: Account, Course • Physical existence: • Customer, student, product, etc. • Conceptual existence: • Bank accounts, sale

  3. Entity Type • A collection of entities that share common properties or characteristics. • An entity type represents a collection of entities. • A business environment may involve many entity types. • University: Faculty, Student, Course • Department, Employee, Dependent • Sales person, Customer, Order

  4. Relationship • Relationship: Interaction between entity types. • Faculty teach Course, Faculty advise Student • Customer open Account, Customer purchase Product. • Binary relationship: A relationship involves two entity types. • Three types of binary relationship: • 1:1, 1:M, M:M

  5. Entity-Relationship Diagram • ER modeling begins by identifying the entities and relationships between entities that must be represented in the model. • In an ERD, an entity type is represented by a rectangle labeled with a singular name. • A relationship has a verb phrase name: • Faculty teach Course, Faculty advise Student

  6. M:M Relationship Peter Paul John Woody Alan Mary Linda Nancy Mia Pia Girl Boy A boy may date 0, 1, or many girls. A girl may date 0, 1, or many boys. Note: “Many boys date many girls” is not a correct interpretation.

  7. 1:1 Relationship Man Peter Paul John Woody Alan Mary Linda Nancy Mia Pia Woman A man may marry 0 or 1 woman. A woman may marry 0 or 1 man.

  8. 1:M Relationship Father Peter Paul John Woody Alan Mary Brian Linda Aron Nancy Ronald Mia Pia Child A father has 1 or many children. A child has 1 father.

  9. Other Examples • 1:1 • State, State Governor • Order, Invoice • 1:M • Department, Employee • Customer, Order • M:M • Bank customer, Bank account • Student, Student organization

  10. ERD Notations 1 1 Student Has Account M M M Enroll Advise 1 M Faculty 1 Teach Course

  11. Alternative Notations Has Student Account Enroll Advise Faculty Teach Course

  12. Attributes • Properties of an entity or a relationship. • Simple attributes vs Composite attributes • Address:Street address, City, State, ZipCode • Single-valued attributes vs multivalued attributes • Derived attributes • Primary key

  13. FullName DateOfBirth SID Lname Fname Age Student Major

  14. Domains of Attributes • The set of allowable values for one or more attributes. • Input validation • Examples: • Sex: F, M • EmpHourlyWage: Between 6 and 300 • EmpName: 50 charcters

  15. Introduction to Relational Database • Data is logically structured within relations. • Each relation is a table (file) with named columns (attributes, fields) and rows (records).

  16. Properties of a Relation • Simple attribute • No composite, no multivalued attribute • Each relation must have a primary key: • Simple or composite key • May have other keys (candidate keys) • Key cannot be null • Cannot be duplicated

  17. Relational Database Design • Create a table for each entity that includes all simple attributes • Relationship: • 1:1, 1:M • Relationship table • Foreign key • M:M: relationship table

  18. Database Design Example • University: • 1:1 Relationship: • Student and Account • What is Peter’s account balance • Find students that owe more than 2000 dollars. • 1:M Relationship • Faculty Advisor and student • Who is Peter’s advisor? • How many students advised by Chao? • M:M Relationship • Student and course • How many units Peter takes? • Find students that are taking 363.

  19. Database Design Example • Bank: • Customer, Account, bank employee

  20. Database Design Example • Order Processing: • Customer, Order, Product

  21. Phone CID Phone Cname Aname M M Attorney Case AID M Hours 1 M 1 Judge Court JID Jname CtID Room

  22. Advanced Topics • Composite key • Multivalued attribute • Student’s Major attribute • Faculty’s DegreeEarned attribute • Vehicle’s Color attribute • Others: PhoneNumber, EmailAddress • Create a table for each multi-valued attribute • Key + attribute • Attributes on a relation

  23. Online Shopping Cart Addr CartID Date CID Cname Phone Has M ShoppingCart Customer 1 M Has M Product Price PID Pname

  24. Order Form

  25. Online Shopping Cart Addr CartID Date CID Cname Phone Has M ShoppingCart Customer 1 M Qty Has M Product Price PID Pname

  26. Attributes on Relationship • Examples: • Student/Course: Grade • Order/Product: Quantity

More Related