1 / 35

Conceptual Modeling with ER Diagrams

Conceptual Modeling with ER Diagrams. Peter Chen introduced ER Diagrams. E-R Modeling. E-R Modeling is a design methodology for modeling the Entities Relationships using a diagram with specified shapes for entities, relationships, roles of entities in these relationships, and cardinality.

anchoret
Télécharger la présentation

Conceptual Modeling with ER Diagrams

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. Conceptual Modelingwith ER Diagrams Peter Chen introduced ER Diagrams

  2. E-R Modeling E-R Modeling is a design methodology for modeling the • Entities • Relationships using a diagram with specified shapes for entities, relationships, roles of entities in these relationships, and cardinality.

  3. E-R Diagram Conventions Symbol Meaning Entity Type Weak Entity Type Relationship Identifying Relationship Attribute

  4. ER-Diagram Conventions - CONTINUED Symbol Meaning Key Attribute Multivalued Attribute Composite Attribute

  5. Entities, Relationships and Attributes • A entity is a object or event which needs to be tracked. Attributes characterize entity sets and relationships. Relationships relate two entity sets Employee Name SSNum Assigned Employee Project Date Name

  6. Payroll Application A contractor does custom work (projects). He keeps track of employeetime for each project. Besides materials, the customer is charged by invoice for employee time on his job. To keep track of employee time, the employee submits a time sheet with entries of project number, hours, description. These are entered into the system along with the employee ID. The hourly rate for the employee is used for each entry to generate a labor charge. The labor charges are totaled for each project and used as a basis for the customer invoice.

  7. Understanding the Requirements Conference Management End Users Analysts • Management – know high level business rules • End users – have used paper system or previous software system and have expectations • Analysts – have understanding of what can / cannot be done and must requirements into system

  8. ER Diagrams for Payroll • Step 1. Identify the Entity Sets with Attributes • Customers : Name, Address,Phone,CustID • Projects : Name,Description,ProjID,Total,CustID • Employees : Name,SSNo,PayRate,EmpID • LaborCharges : EmpID,ProjID,Description,Hours,Amount

  9. ER Diagrams for Payroll • Step 1. Identify Relationships between Entity Sets with any Attributes • Customers Has Project … Project is Weak Entity Set • Employee Works_On Project • Employee Has_Labor_Charge For Project … Labor_Charge is Weak Entity Set

  10. E-R Modeling – Entity Sets, Attributes, RelationshipsRacquet Club Example • A Racquet Club has members with one year or two year contracts. • Members pay contract amount up front, quarterly, or monthly. • Members may reserve a court for the next day.

  11. E-R Modeling – Entity Sets, Attributes, RelationshipsRacquet Club Example Continued • Entity Sets • Members : Name, Start, Contract_Amount, Contract_Period, Initial_Balance, Member_ID • Payments : PayerID, Amount, Paid_Date, Payment_ID • Reservations : Member_ID, CourtNumber, Hour • Relations : • Member - Makes – Payment • Member - Makes - Reservation

  12. Data Modeling– Racquet Club Example

  13. Keys, Cardinality Constraints and Participation Constraints • A key of a relation is a minimal set of attributes and roles which uniquely determine each entity. • A cardinality constraint m..n constrains the number of times a single entity can participate in a role of a relationship : m <= times <= n. • A participation constraint ensures that an entity will particpate at least this many times.

  14. ER-Diagram Cardinality Conventions Symbol Meaning Calculated Attribute Cardinality Constraints and Participation Constraints 0 .. * 0 .. 1 1 .. * 1 .. 1

  15. Cardinality and Participationin the E-R model. 1 .. 2 means that at least 1 d must participate in the A relation with C and that not more that 2 d’s can participate. If A is represented by a table, then there is at least one row and not more than 2 rows with a single c value.

  16. Many-to-one, one-to-one, and many-to-many correspondences

  17. Cardinality & Participation Constraints

  18. Cardinality & Participation Constraints

  19. Example of an E-R diagram with an ISA hierarchy.

  20. Using IsA for data partitioning. New York Customer Pennsylvania Customer

  21. Participation Constraints A participation constraint is a lower bound on the number of times an entity can particpate in a relation. Examples: An employee belongs to ONE department. This is a participation and a cardinality constraint 1 .. 1. Employee Department 0 .. * 0 .. 1 1 .. * 1 .. 1

  22. Participation constraints.

  23. Relationship Types – Two Entity Set Relations An employee is assigned to at least one project (participation) A customer will pay for at least one project Assigned To Project Employee Num-Hours Will Pay For Project Customer Fixed-Cost

  24. Relationship Types – One & Three Entity Set Relations One Set : ReportsTo( SupID, SubID ) Three Sets : Sale( ProdID, CustID,SuppID;Date) subordinate Reports_To Employee supervisor Sold Product Customer Price Date Supplier

  25. ER Diagrams for Payroll Phone Name Customer CustID Address Has Name CustID Projects Description ProjID Total

  26. ER Diagrams for Payroll PayRate Name Employee EmpID SSNo Has Year EmpID TimeSheet Month TSID Has Has TSID TSID . . . Labor Charges PayCheck . . . NetPay Amount

  27. The IS-A Relationship Employee SSNo Name Is-A disjoint Hourly Employee Salaried Employee PayRate Salary

  28. Construct an ER Diagram • Lawn Furniture Manufacturer • Stores Information for Catalog of products and parts • Stores Information for Manufacturing, including quantity on hand • Identify product entities (picnic table and lawn chair) with attributes • Identify relationships IsA and PartOf

  29. The Part-Of Relationship OutDoorProducts IsA Lawn_Chair Picnic_Table Price ProdNo Price ProdNo Part_Of Seats Top Base Quantity Price ProdNo

  30. From E-R Diagrams toRelational Database Schema • Converting entities into relations (tables) • Each entity becomes a relation • Each attribute of the entity becomes an attribute of that relation (column) • If attributes K1 ... Kn form a key of the entity, then K1 ... Kn form a candidate key of the relation.

  31. From ER Diagramsto Relational Database Schema • Translating Entities Employee SSNo Name Type Termination HireDate Create Table Employee ( Name char(20), Type char(1), HireDate date, Termination date, SSNo char(9), Primary Key (SSNo) )

  32. From E-R Diagrams toRelational Database Schema • Representing relationships in the database schema • R is a relationship between entity set A and weak entity set B • A becomes a relation with primary key K and B+K becomes a relation with K being a foreign key – e.g. employees and dependents • R is a relationship between entity sets A,B and C • A,B and C become relations with KA , KB , and Kc as keys. R becomes a relation with foreign keys KA , KB , and Kc

  33. From ER Diagramsto Relational Database Schema SaleID • Translating Relationships Sold Product Customer Price SaleDate Supplier Create Table Sold ( SaleDate Date, Price Currency, SaleID autonumber, Customer Integer, Supplier Integer, Product Integer, Primary Key (SaleID), Foreign Key ( Customer ) References Customers( CustID), Foreign Key ( Supplier ) References Suppliers( SuppID ), Foreign Key ( Product ) References Products ProdID ) )

  34. Translate into Schema Employee SSNo Name Is-A disjoint Hourly Employee Salaried Employee PayRate Salary

  35. Translate Into Schema OutDoorProducts IsA Lawn_Chair Picnic_Table Price ProdNo Price ProdNo Part_Of Seats Top Base Quantity Price ProdNo

More Related