1 / 28

Data Modeling with ERD

Data Modeling with ERD. ISYS 363. Database Examples. Sales database: Customer table Orders table LineItem table Products table University database: Student table Account table Faculty table Course table Registration table. Entities.

chaela
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 ISYS 363

  2. Database Examples • Sales database: • Customer table • Orders table • LineItem table • Products table • University database: • Student table • Account table • Faculty table • Course table • Registration table

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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.

  8. 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.

  9. 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.

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

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

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

  13. Attributes • Properties of an entity or a relationship. • Single-valued attributes vs multivalued attributes • Derived attributes • Primary key

  14. FullName DateOfBirth SID Lname Fname Age Student Major

  15. 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

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

  17. 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

  18. 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

  19. 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.

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

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

  22. Phone CID Email Cname Aname M M Attorney Case AID M Hours 1 M 1 Judge Court JID Jname CtID Address

  23. 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

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

  25. Order Form

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

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

  28. Suggested Readings • http://www.youtube.com/watch?v=u2QqjofJvGo • http://en.wikipedia.org/wiki/Entity-relationship_model • http://faculty.babson.edu/osborn/mis7510/readings/erd.htm • http://dhdurso.org/articles/database-design.html

More Related