Chapter 4 Data Modeling Using the Entity-Relationship (ER) Model PDF

Chapter 4 Data Modeling Using the Entity-Relationship (ER) Model Using High-Level Conceptual Data Models for Database Design  SUMMARY OF ER-DIAGRAM NOTATION FOR ER SCHEMAS Symbol Meaning ENTITY TYPE...

Chapter 4 Data Modeling Using the Entity-Relationship (ER) Model Using High-Level Conceptual Data Models for Database Design  SUMMARY OF ER-DIAGRAM NOTATION FOR ER SCHEMAS Symbol Meaning ENTITY TYPE WEAK ENTITY TYPE RELATIONSHIP TYPE IDENTIFYING RELATIONSHIP TYPE ATTRIBUTE KEY ATTRIBUTE MULTIVALUED ATTRIBUTE COMPOSITE ATTRIBUTE DERIVED ATTRIBUTE E1 R E2 TOTAL PARTICIPATION OF E2 IN R N CARDINALITY RATIO 1:N FOR E1:E2 IN R E1 R E2 (min,max) STRUCTURAL CONSTRAINT (min, max) ON PARTICIPATION R E OF E IN R  Using High-Level Conceptual Data Models for Database Design ⚫ functional requirements consist of the user defined operations (or transactions) that will be applied to the database, including both retrievals and updates. ⚫ The conceptual schema is a concise description of the data requirements of the users and includes detailed descriptions of the entity types, relationships, and constraints; ⚫ Most current commercial DBMSs use an implementation data model—such as the relational or the object-relational database model—so the conceptual schema is transformed from the high-level data model into the implementation data model. This step is called logical design or data model mapping; ⚫ The last step is the physical design phase, during which the internal storage structures, file organizations, indexes, access paths, and physical design parameters for the database files are specified. Example COMPANY Database ⚫ We list the data requirements for the database here, and then create its conceptual schema step-by-step as we introduce the modeling concepts of the ER model. ⚫ The COMPANY database keeps track of a company’s employees, departments, and projects. Suppose that after the requirements collection and analysis phase, the database designers provide the following description of the miniworld. ⚫ The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department. A department may have several locations. Example COMPANY Database (Cont.) ⚫ A department controls a number of projects, each of which has a unique name, a unique number, and a single location. ⚫ We store each employee’s name, Social Security number, address, salary, sex(gender), and birth date. An employee is assigned to one department, but may work on several projects, which are not necessarily controlled by the same department. We keep track of the current number of hours per week that an employee works on each project. We also keep track of the direct supervisor of each employee (who is another employee). ⚫ We want to keep track of the dependents of each employee for insurance purposes. We keep each dependent’s first name, sex, birth date, and relationship to the employee. Example COMPANY Database (Cont.) First step DEPARTMENT(unique name, unique number, particular employee who manages the department, the start date when that employee began man-aging the department, several locations, department controls a number of projects) PROJECT(unique name, a unique number, single location) EMPLOYEE(name, Social Security number, address, salary, sex(gender), birth date, An employee is assigned to one department, work on several projects, number of hours per week that an employee works on each project, supervisor “who is another employee”) DEPENDENT(dependents of each employee, first name, sex, birth date, relation-ship to the employee)  ER DIAGRAM – Entity Types are: EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT DEPARTMENT(unique name, unique number, particular employee who manages the department, the start date when that employee began man- aging the department, several locations, department controls a number of projects) PROJECT(unique name, a unique number, single location)  ER DIAGRAM – Entity Types are: EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT EMPLOYEE(name, Social Security number, address, salary, sex(gender), birth date, An employee is assigned to one department, work on several projects, number of hours per week that an employee works on each project, supervisor “who is another employee”) DEPENDENT(dependen ts of each employee, first name, sex, birth date, relation-ship to the employee) ER DIAGRAM – Entity  SUMMARY OF ER-DIAGRAM NOTATION FOR ER SCHEMAS Symbol Meaning ENTITY TYPE WEAK ENTITY TYPE RELATIONSHIP TYPE IDENTIFYING RELATIONSHIP TYPE ATTRIBUTE KEY ATTRIBUTE MULTIVALUED ATTRIBUTE COMPOSITE ATTRIBUTE DERIVED ATTRIBUTE E1 R E2 TOTAL PARTICIPATION OF E2 IN R N CARDINALITY RATIO 1:N FOR E1:E2 IN R E1 R E2 (min,max) STRUCTURAL CONSTRAINT (min, max) ON PARTICIPATION R E OF E IN R The (min,max) notation relationship constraints (1,1) (1,N) (0,1) (1,1) COMPANY ER Schema Diagram using (min, max) notation Break  MAPPING RULES Entity Types ⚫ Each entity type is implemented with a separate relation. Entity types are either strong entity types or weak entity types. Strong Entities ⚫ Strong, or regular, entity types are mapped to their own relation. The primary key (PK) is chosen from the set of keys available. Weak Entities ⚫ Weak entity types are mapped to their own relation, but the primary key of the relation is formed as follows. If there are any identifying relationships, then the PK of the weak entity is the combination of the PKs of entities related through identifying relationships and the discriminator of the weak entity type. Otherwise, the PK of the relation is the PK of the weak entity. Chapter 3-15  MAPPING RULES Relationship Types ⚫ The implementation of relationships involves foreign keys. Recall, as discussed in point 1) above. If the relationship is identifying, then the primary key of an entity type must be propagated to the relation for a weak entity type. We must consider both the degree and the cardinality of the relationship. In the following examples. examples 1 – 3 deal with binary relationships and example 4 concerns n-ary relationships. Binary One-To-One ⚫ In general, with a one-to-one relationship, a designer has a choice regarding where to implement the relationship. One may choose to place a foreign key in one of the two relations, or in both. Consider placing the foreign key such that nulls are minimized. If there are attributes on the relationship, those can be placed in either relation. Chapter 3-16  MAPPING RULES Binary One-To-Many ⚫ With a one-to-many relationship, the designer must place a foreign key in the relation corresponding to the ‘many’ side of the relationship. Any other attributes defined for the relationship are also included on the ‘many’ side. Binary Many-To-Many ⚫ A many-to-many relationship must be implemented with a separate relation for the relationship. This new relation will have a composite primary key comprising the primary keys of the participating entity types and any discriminator attribute, plus other attributes of the relationship if any. Chapter 3-17  MAPPING RULES n-ary, n>2 ⚫ A new relation is generated for an n-ary relationship. This new relation has a composite primary key comprising the n primary keys of the participating entity types and any discriminator attribute, plus any other attributes. There is one exception to the formation of the PK: if the cardinality related for any entity type is 1, then the primary key of that entity type is only included as a foreign key and not as part of the primary key of the new relation. Chapter 3-18 Exercise Chapter 3-19 Exercise Chapter 3-20 UML Class Diagrams  UML Class Diagrams ⚫ In UML class diagrams, a class (similar to an entity type in ER) is displayed as a box that includes three sections: – The top section gives the class name (similar to entity type name); – The middle section includes the attributes; – The last section includes operations that can be applied to individual objects (similar to individual entities in an entity set) of the class. ⚫ Consider the EMPLOYEE class, Its attributes are Name, Ssn,Bdate, Sex, Address, and Salary. The designer can optionally specify the domain of an attribute if desired, by placing a colon (:) followed by the domain name or description, as illustrated by the Name, Sex, and Bdate attributes of EMPLOYEE in. ⚫ A composite attribute is modeled as a structured domain, as illustrated by the Name attribute of EMPLOYEE. A multivalued attribute will generally be modeled as a separate class, as illustrated by the LOCATION class. ⚫ Relationship types are called associations in UML terminology, and relationship instances are called links. A binary association (binary relationship type) is represented as a line connecting the participating classes (entity types), and may optionally have a name. A relationship attribute, called a link attribute, is placed in a box that is connected to the association’s line by a dashed line. The (min, max) notation  UML Class Diagrams ⚫ Multiplicities are specified in the form min..max, and an asterisk (*) indicates no maximum limit on participation. However, the multiplicities are placed on the opposite ends of the relationship. ⚫ In UML, a single asterisk indicates a multiplicity of 0..*, and a single 1 indicates a multiplicity of 1..1. A recursive relationship is called a reflexive association in UML, and the role names—like the multiplicities—are placed at the opposite ends of an association. ⚫ In UML, there are two types of relationships: association and aggregation. Aggregation is meant to represent a relationship between a whole object and its component parts, and it has a distinct diagrammatic notation. we modeled the locations of a department and the single location of a project as aggregations. However, aggregation and association do not have different structural properties, and the choice as to which type of relationship to use is somewhat subjective. In the ER model, both are represented as relationships. ⚫ UML also distinguishes between unidirectional and bidirectional associations (or aggregations). In the unidirectional case, the line connecting the classes is displayed with an arrow to indicate that only one direction for accessing related objects is needed. If no arrow is displayed, the bidirectional case is assumed, which is the default.  UML Class Diagrams ⚫ Association (relationship) names are optional in UML, and relationship attributes are displayed in a box attached with a dashed line to the line representing the association/aggregation. ⚫ The operations given in each class are derived from the functional requirements of the application. ⚫ Weak entities can be modeled using the construct called qualified association (or qualified aggregation) in UML; this can represent both the identifying relationship and the partial key, which is placed in a box attached to the owner class  Binary and Ternary (or Higher-Degree) Relationships  Binary and Ternary (or Higher-Degree) Relationships  Exercise Consider the following set of requirements for a UNIVERSITY database that is used to keep track of students’transcripts: a. The university keeps track of each student’s name,student number,Social Security number,current address and phone number,permanent address and phone number,birth date,sex,class (freshman, sophomore,..., graduate),major department,minor department (if any), and degree program (B.A., B.S.,..., Ph.D.). Some user applications need to refer to the city, state, and ZIP Code of the student’s permanent address and to the student’s last name. Both Social Security number and student number have unique values for each student. b. Each department is described by a name, department code, office number, office phone number, and college. Both name and code have unique values for each department.  Exercise Con. c. Each course has a course name, description, course number, number of semester hours, level, and offering department. The value of the course number is unique for each course. d. Each section has an instructor, semester, year, course, and section number.The section number distinguishes sections of the same course that are taught during the same semester/year; its values are 1, 2, 3,...,up to the number of sections taught during each semester. e. A grade report has a student, section, letter grade, and numeric grade (0, 1,2,3,or 4). Design an ER schema for this application, and draw an ER diagram for the schema. Specify key attributes of each entity type, and structural constraints on each relationship type. Note any unspecified requirements, and make appropriate assumptions to make the specification complete.  Exercise STUDENT (name, number, Social Security number, current address and phone number, permanent address and phone number, birth date, sex, class (freshman, sophomore,..., graduate), major department, minor department (if any), and degree program (B.A., B.S.,..., Ph.D.). Note: Some user applications need to refer to the city, state, and ZIP Code of the student’s permanent address and to the student’s last name. Both Social Security number and student number have unique values for each student. DEPARTMENT (name, department code, office number, office phone number, college) COURSE (name, description, course number, number of semester hours, level, offering department) SECTION (instructor, semester, year, course, section number). Note: The section number distinguishes sections of the same course that are taught during the same semester/year; its values are 1, 2, 3,..., up to the number of sections taught during each semester. GRADE_REPORT (student, section, letter grade, numeric grade) Exercise Con. Exercise Con. STUDENT (name, number, Social Security number, current address and phone number, permanent address and phone number, birth date, sex, class (freshman, sophomore,..., graduate), major department, minor department (if any), and degree program (B.A., B.S.,..., Ph.D.). Note: Some user applications need to refer to the city, state, and ZIP Code of the student’s permanent address and to the student’s last name. Both Social Security number and student number have unique values for each student. Exercise Con. DEPARTMENT department code, (name, office number, office phone number, college) Exercise Con. COURSE (name, description, course number, number of semester hours, level, offering department) Exercise Con. SECTION semester, year, (instructor, course, section number). Exercise Con. GRADE_REPORT (student, section, letter grade, numeric grade) Exercise Con. Note: The section number distinguishes sections of the same course that are taught during the same semester/year; its values are 1, 2, 3,..., up to the number of sections taught during each semester. Exercise Con.