the entity relationship
play

The Entity-Relationship Requirements analysis Model Conceptual - PDF document

Overview of Database Design The Entity-Relationship Requirements analysis Model Conceptual design data model Logical design Schema refinement: Normalization Physical tuning


  1. Overview of Database Design The Entity-Relationship � Requirements analysis Model � Conceptual design � data model � Logical design � Schema refinement: Normalization � Physical tuning �������������������������������������������� ������������������� �������������������������������������������� ������������������� Entities Conceptual Design � Conceptual design : (ER Model is used at this stage.) � What are the entities and relationships in the name enterprise? ssn lot � What information about these entities and relationships should we store in the database? Employees � What are the integrity constraints or business rules that hold? � A database `schema ’ in the ER Model can be represented pictorially ( ER diagrams ). � Can map an ER diagram into a relational schema. �������������������������������������������� ������������������� �������������������������������������������� �������������������

  2. ER Model Basics Relationships � Entity: Real-world object distinguishable from other objects. An entity is described (in DB) using a set of since name dname attributes ssn lot did budget � Entity Set : A collection of similar entities. Works_In Employees Departments E.g., all employees � All entities in an entity set have the same set of attributes � Each entity set has a key � Each attribute has a domain �������������������������������������������� ������������������� �������������������������������������������� ������������������� ER Model Basics (Contd.) Relationships (Contd.) � Relationship : Association among two or name more entities. ssn lot � E.g., Attishoo works in Pharmacy Employees department. super- � Relationship Set : Collection of similar subor- visor dinate relationships. Reports_To � An n-ary relationship set R relates n entity sets E1 ... En � Want to capture supervisor-subordinate � Each relationship in R involves entities e1 in relationship E1, ..., en in En �������������������������������������������� ������������������� �������������������������������������������� �������������������

  3. Relationships (Contd.) Ternary Relationship id name name id Parts Parts name id name id name name id id Suppliers Departments Suppliers Departments Contract � Want to capture information that a Supplier s supplies Part p to Department d �������������������������������������������� ������������������� �������������������������������������������� ������������������� How are these different? Key Constraints since � An employee can to name from dname name dname work in many ssn lot did budget ssn lot did budget departments; a dept Departments Works_In Employees Works_In2 Employees Departments can have many employees � Each dept has at since name dname most one manager, name dname ssn lot did budget ssn lot did budget according to the key constraint on Works_In3 Departments Employees Manages. Employees Manages Departments Duration to from �������������������������������������������� ������������������� �������������������������������������������� �������������������

  4. Participation Constraints Key Constraints: Examples since � Example Scenario 1: An inventory database name dname � An employee can contains information about parts and ssn lot did budget work in many manufacturers. Each part is constructed by departments; a dept Works_In Employees Departments can have many exactly one manufacturer. employees � Example Scenario 2: A customer database contains information about customers and sales � Each employee persons. Each customer has exactly one primary works in at least since sales person. name dname one department ssn lot did budget according to the participation � What do the ER diagrams look like? Employees Works_In Departments constraint on Works_In �������������������������������������������� ������������������� �������������������������������������������� ������������������� Participation Constraints: Examples Exercise: What does this mean? � Example Scenario 1 (Contd.): Each part is constructed by exactly one or more since since name name dname dname manufacturer. ssn lot did did budget budget � Example Scenario 2: Each customer has Employees Departments Manages exactly one primary sales person. Works_In since �������������������������������������������� ������������������� �������������������������������������������� �������������������

  5. name ssn lot Weak Entities ISA (`is a’) Hierarchies Employees � ������������������������� � A weak entity can be identified uniquely only by hourly_wages hours_worked ������������������������� ISA considering the primary key of another ( owner ) entity. contractid � � �!����"#������ ��� $���%������ � Owner entity set and weak entity set must participate in a one- �����������#���"������������������$� Contract_Emps to-many relationship set (one owner, many weak entities). Hourly_Emps �������� � Weak entity set must have total participation in this identifying relationship set. � Overlap constraints : Can Joe be an Hourly_Emps as well as a Contract_Emps entity? ( Allowed/disallowed ) � Covering constraints : Does every Employees entity also name cost have to be an Hourly_Emps or a Contract_Emps entity? pname age ssn lot (Yes/no) � Reasons for using ISA: Employees Policy Dependents � To add descriptive attributes specific to a subclass. � To identify entities that participate in a relationship. �������������������������������������������� ������������������� �������������������������������������������� ������������������� name ssn lot Class Exercise Aggregation Employees � Used when we � Give two real-life examples where each of have to model a Monitors until the following would occur: relationship � A key constraint involving (entitity since started_on dname sets and) a � A participation constraint pid pbudget did budget relationship set . � A weak entity set Sponsors � Aggregation allows Projects Departments us to treat a relationship set as � ������������������������������������ &�� an entity set for � ��������������������"����#��������'�� purposes of !���������"��'��%������������ participation in � �#����"��������������"���'��������'� (other) ������������������������������'#����� relationships. �������������������������������������������� ������������������� �������������������������������������������� �������������������

Recommend


More recommend