Download
data modeling with erd n.
Skip this Video
Loading SlideShow in 5 Seconds..
Data Modeling with ERD PowerPoint Presentation
Download Presentation
Data Modeling with ERD

Data Modeling with ERD

131 Views Download Presentation
Download Presentation

Data Modeling with ERD

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