Cơ sở dữ liệu - Chapter 2: Entity relationship model

Chapter 2 Entity Relationship Model Introduction  Modeling databases using a graphical technique is proposed so that humans can relate to easily.  ER Model is based on a perception of a real world that consists of collection of basic objects called entities and relationships among these objects. Overview of Database Design  Conceptual design: (ER Model is used at this stage.)  A database “schema” in the ER Model can be represented pictorially (ER diagrams).  The next step in DB design is the actual implementation of the database, using a commercial DBMS  By using the DBMS, conceptual design from high-level data model will be transformed into the implementation data model. COMPANY example  The COMPANY database keeps track of company’s employee, departments, and projects  Suppose that after the requirement collection and analysis phase, the database designers provide the following description: COMPANY example  The company is organized into DEPARTMENTs.  Each department has a name, number and an employee who manages the department.  The start date of the department manager will be kept track. A department may have several locations.  Each department controls a number of PROJECTs.  Each project has a unique name, unique number and is located at a single location. COMPANY example  We store each EMPLOYEE’s name social security number, address, salary, sex, and birthdate.  Each employee works for one department but may work on several projects.  We keep track of the number of hours per week that an employee currently works on each project. We also keep track of the direct supervisor of each employee.  Each employee may have a number of DEPENDENTs.  For each dependent, we keep track of their name, sex, birthdate, and relationship to the employee. ER Model Basics  ER model describes data as entities, relationships, and attributes  Entity: an object that exists and is distinguishable from other objects. In other words, the entity can be uniquely identified. ◦ Ex:  A particular person, for example Dr. Alain is an entity.  A particular department, for example CSE faculty.  A particular place, for example HCM city can be an entity. ER Model Basics  Entity type or entity set: collection of similar entities ◦ Ex:  All students in CSE, say STUDENT.  All courses in CSE, say COURSE  An entity may belong to more than one entity type. ◦ Ex: The same person is a LECTURER at one instance and STUDENT at another instance. ER Model Basics  Relationship: associations among two or more entities  Ex: ◦ An Dang works in Pharmacy department. ◦ Teaches is the relationship type between LECTURER and STUDENT. Symbols Used in ER Diagram Entity sets Attributes Relationships ER Model Basics  Attributes: properties of entity  Ex: Roll number, name, and grade are the attributes of STUDENT. Attribute classification 1 Attribute classification  Single Value Attribute: only one value associated with that attribute. ◦ Symbol for Single Value Attribute: ◦ Ex FullName STUDENT Attribute classification  Multivalued Attribute: more than one value will be associated with that attribute. ◦ Symbol: ◦ Ex: EMPLOYEE Skills Name Attribute classification  Derived Attribute: ◦ Can be computed from other attributes ◦ Symbol: ◦ Ex: Age of a person can be derived from the date of birth of the person PERSON Age Name Attribute classification  Composite Attribute ER Diagram Key attribute  An important constrain on the entities of an entity type is the KEY on attributes  An attribute of an entity type for which each entity must have a unique value is called a key attribute of the entity type.  For example, SSN of EMPLOYEE.  Each key is underlined Relationship degree  Unary Relationship (recursive relationship): ◦ In the unary relationship the number of associated entity is one. An entity related to itself is known as recursive relationship. PLAYER Captain of Relationship degree  Binary Relationship: two entities are involved. Staff Department Is assigned Relationship degree  Quaternary Relationship: two entities are involved. Staff Department Is assigned Location Relationship degree  Quaternary Relationships: involve 4 entities COURSE PROFESSOR STUDENT Teaches SLIDES Relationship Classification  One-to-Many Relationship Type: ◦ The relationship that associates one entity to more than one entity is called one-to-many relationship. ◦ Symbol: ◦ Example: parent–child relationship. For one parent there can be more than one child. DEPARTMENT EMPLOYEE Relationship Classification  One-to-One Relationship Type: ◦ a special case of one-to-many relationship. ◦ True one-to-one relationship is rare. ◦ Symbol: ◦ Ex: The relationship between the President and the country is an example of one-to-one relationship. PRESIDENT COUNTRY Relationship Classification  Many-to-Many Relationship Type ◦ Ex: The relationship between students and courses. ◦ Symbol: Relationship Classification  Many-to-One Relationship Type ◦ Ex: The relationship between EMPLOYEE and DEPARTMENT is an example of many- to-one relationship. There may be many EMPLOYEES working in one DEPARTMENT. Relationship Classification Reducing ER Diagram to Tables  Mapping Entities: ◦ Regular entities  Entity name = Name of the relation or table.  Attributes of ER diagram=Column name of the table. Reducing ER Diagram to Tables  Mapping Composite Attribute: CUSTOMER (Customer-ID, Customer name, Street, City, State, Zip) Reducing ER Diagram to Tables  Mapping Multivalued Attribute:  EMPLOYEE (Employee-ID, Name, Address)  EMPLOYEE-SKILL (Employee-ID, Skill) Reducing ER Diagram to Tables  Mapping one-to-Many Relationship:  EMPLOYEE (Customer-ID, Name, Address)  ORDER (Order-ID, Order-Date, Customer-ID) Reducing ER Diagram to Tables  Mapping one-to-Many Relationship:  EMPLOYEE (Customer-ID, Name, Address)  ORDER (Order-ID, Order-Date, Customer-ID) Reducing ER Diagram to Tables  Mapping Many-to-Many Relationship:  EMPLOYEE(Customer-ID, Name, Other attributes)  SHIPMENT(Shipment-No, Customer-ID, Vendor-ID, Date Amount)  ORDER (Vendor-ID, Address, Other Attributes) Relationship Classification  Ex: ◦ Employee – Dependent people ◦ Customer – Orders ◦ Goods – Vendors