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

Download Presentation
Download Policy: The content available on the website is offered to you 'AS IS' for your personal information and use only. It cannot be commercialized, licensed, or distributed on other websites without prior consent from the author. To download a presentation, simply click this link. If you encounter any difficulties during the download process, it's possible that the publisher has removed the file from their server.

Recommend


More recommend