Relational Database Model vs Entity-Relationship Model PDF

The relational database model vs The Entity-Relationship (ER) model  Both foundational concepts in the field of database design, but they serve slightly different purposes and have different focuses. Relational Database Model Entity-Relation...

The relational database model vs The Entity-Relationship (ER) model  Both foundational concepts in the field of database design, but they serve slightly different purposes and have different focuses. Relational Database Model Entity-Relationship (ER) Model The relational database model organizes data into The ER model focuses on the conceptual representation of tables, where each table consists of rows and columns data and the relationships between entities in a system It establishes relationships between tables through It uses entities to represent real-world objects or concepts keys, primarily through primary and foreign keys and relationships to represent associations between these entities It emphasizes data integrity, normalization, and Entities are represented as rectangles, and relationships are efficient querying through operations like joins. represented as diamonds in ER diagrams (Chen model) The relational model is implemented in SQL Attributes describe the properties of entities, and cardinality (Structured Query Language), which is the standard specifies how many instances of one entity are related to language for interacting with relational databases. another The relational database model is concerned with the implementation and structure of data in terms of tables and relationships within those tables, the ER model focuses on the conceptual design of the data, representing entities, their attributes, and the relationships between them in a visual manner using ER diagrams. The ER model is often used in the initial stages of database design to capture requirements and relationships before transitioning to the relational model for implementation. Relational Database Model Entity-Relationship (ER) Model TransactionId Purchase Date ID Lname PerKG Fname  columns in a relational database table that uniquely identify items in the rows. like a primary key, but the main difference is that unique keys can have NULL value, but only ONE NULL. purpose is to prevent duplicate items in rows, but the items in the row also don't need to have a value. Unique key (cont.) Entity Relationship (ER) Modeling Learning Objectives In this chapter, you will learn: The main characteristics of entity relationship components How relationships between entities are defined, refined, and incorporated into the database design process How ERD components affect database design and implementation That real-world database design often requires the reconciliation of conflicting goals 7 Entities, attributes & relationships Entity Relationship Model (ERM) Basis of an entity relationship diagram (ERD) ERD depicts the: Conceptual database as viewed by end user Database’s main components Entities Attributes Relationships Entity - Refers to the entity set and not to a single entity occurrence 9  Entity - Refers to the entity set and not to a single entity occurrence Entity Entity Type Entity Set A thing in the real world with independent Set of all entities of a particular entity A category of a particular entity existence type. Any particular row (a record) in a The name of a relation (table) in RDBMS is All rows of a relation (table) in RDBMS is relation(table) is known as an entity. an entity type entity set 10  Attributes Characteristics of entities Required attribute: Must have a value, cannot be left empty Optional attribute: Does not require a value, can be left empty Domain - Set of possible values for a given attribute the grade point average (GPA) attribute is written (0,4) the gender attribute consists of only two possibilities: M or F Identifiers : One or more attributes that uniquely identify each entity instance In the relational model, such identifiers are mapped to primary keys (PKs) in tables. Identifiers are underlined in the ERD. Key attributes are also underlined in a frequently used table structure shorthand notation using the format: TABLE NAME (KEY_ATTRIBUTE 1, ATTRIBUTE 2, ATTRIBUTE 3,... ATTRIBUTE K) For example, a CAR entity may be represented by: CAR (CAR_VIN, MOD_CODE, CAR_YEAR, CAR_COLOR) (Each car is identified by a unique vehicle identification number, or CAR_VIN.) 11 Figure 4.1 - The Attributes of the Student Entity: Chen and Crow’s Foot required attribute optional attribute 12  Attributes (cont.) Composite identifier: Primary key composed of more than one attribute CLASS_CODE is the primary key, and the a proper candidate key combination of CRS_CODE and CLASS_SECTION is a proper candidate key. If the CLASS_CODE attribute is deleted from the CLASS entity, the candidate key (CRS_CODE and CLASS_SECTION) becomes an acceptable composite primary key. Represented in shorthand form by: an acceptable composite primary key CLASS (CLASS_CODE, CRS_CODE, CLASS_SECTION, CLASS_TIME, ROOM_CODE, PROF_NUM) On the other hand, if CLASS_CODE is deleted, and the composite primary key may be represented by: CLASS (CRS_CODE, CLASS_SECTION, CLASS_TIME, ROOM_CODE, PROF_NUM) Note that both key attributes are underlined in the entity notation. 13 Attributes (cont.) Composite attribute (not to be confused with a composite key): Attribute that can be subdivided to yield additional attributes For example: the attribute ADDRESS can be subdivided into street, city, state, and zip code. the attribute PHONE_NUMBER can be subdivided into area code and exchange number Simple attribute: Attribute that cannot be subdivided For example: age and marital status Single-valued attribute: Attribute that has only a single value For example: a person can have only one IC number a manufactured part can have only one serial number Multivalued attributes: Attributes that have many values For example: a person may have several college degrees 14 Figure 4.3 - A Multivalued Attribute in an Entity the multivalued attributes PK 15  Attributes (cont.) Multivalued attributes (cont.): Attributes that have many values and require creating: Option 1: Several new attributes, one for each component of the original multivalued attribute Although this solution seems to work, its adoption can lead to major structural problems in the table Option 2: A new entity composed of the original multivalued attribute’s components 16  Attributes (cont.) Multivalued attributes (cont.): Attributes that have many values and require creating: Option 1: Several new attributes, one for each component of the original multivalued attribute Option 2: A new entity composed of the original multivalued attribute’s components 17 Attributes Derived attribute/computed attributes : Attribute whose value is calculated from other attributes Derived using an algorithm 18 Advantages and Disadvantages of Storing Derived Attributes 19 Relationships Association between entities that always operate in both directions Participants: Entities that participate in a relationship The relationship name is an active or passive verb For example: a STUDENT takes a CLASS a PROFESSOR teaches a CLASS a DEPARTMENT employs a PROFESSOR a DIVISION is managed by an EMPLOYEE an AIRCRAFT is flown by a CREW. Relationships between entities always operate in both directions. Example, the relationship between the entities named CUSTOMER and INVOICE: A CUSTOMER may generate many INVOICEs. Each INVOICE is generated by one CUSTOMER. 20 Relationships (cont.) The relationship classification is difficult to establish if you know only one side of the relationship. For example: A DIVISION is managed by one EMPLOYEE. You don’t know if the relationship is 1:1 or 1:M. Therefore, you should ask the question “Can an employee manage more than one division?” If the answer is yes, the relationship is 1:M, and the second part of the relationship is then written as: An EMPLOYEE may manage many DIVISIONs. If an employee cannot manage more than one division, the relationship is 1:1, and the second part of the relationship is then written as: An EMPLOYEE may manage only one DIVISION. 21 Question An AGENT serves many CUSTOMERs 1:M relationship A CUSTOMER can be served by one AGENT AGENT serve CUSTOMER PK Agent_ID PK Customer_ID Agent_Fname FK Agent_ID Agent_Lname Table: ? Agent_ID Customer_ID 001 4001 001 4002 001 4003 002 4004 002 4005 002 4006  Table: ? Question 1:M relationship Agent_ID Customer_ID 001 4001 An AGENT can serve many CUSTOMERs 001 4002 A CUSTOMER can be served by one AGENT 001 4003 002 4004 AGENT serve CUSTOMER 002 4005 PK Agent_ID PK Customer_ID 002 4006 Agent_Fname FK Agent_ID Agent_Lname Table: CUSTOMER Customer_ID Agent_ID 4001 001 4002 001 4003 001 4004 002 4005 002 4006 002 Question An AGENT can serve many CUSTOMERs 1:M relationship A CUSTOMER can be served by one AGENT AGENT serve CUSTOMER PK Agent_ID PK Customer_ID Agent_Fname FK Agent_ID Agent_Lname Table: AGENT Agent_ID Agent_Fname Agent_Lname 001 John Doe 001 John Doe Correct? 001 John Doe 002 Wang Yan 002 Wang Yan 002 Wang Yan Question An AGENT can serve many CUSTOMERs 1:M relationship A CUSTOMER can be served by one AGENT AGENT serve CUSTOMER PK Agent_ID PK Customer_ID Agent_Fname FK Agent_ID Agent_Lname Table: AGENT Agent_ID Agent_Fname Agent_Lname 001 John Doe 002 Wang Yan Question An AGENT can serve many CUSTOMERs 1:M relationship A CUSTOMER can be served by one AGENT AGENT serve CUSTOMER PK Agent_ID PK Customer_ID Agent_Fname FK Agent_ID Agent_Lname Table: AGENT Table: CUSTOMER Customer_ID Agent_ID Agent_ID Agent_Fname Agent_Lname 4001 001 001 John Doe 4002 001 002 Wang Yan 4003 001 4004 002 4005 002 4006 002 Question An AGENT can serve many CUSTOMERs 1:M relationship A CUSTOMER can be served by one AGENT AGENT serve CUSTOMER PK Agent_ID PK Customer_ID FK Customer_ID FK Agent_ID Agent_Fname Agent_Lname Table: AGENT Correct? No. because introduced anomalies Agent_ID Customer_ID Agent_Fname Agent_Lname 001 4001,4002,4003 John Doe 002 4004,4005,4006 Wang Yan How to correct? Normalization Process (Week 6 & 7) Connectivity & cardinality Relationships Connectivity: Describes the relationship classification Cardinality: Expresses the minimum and maximum number of entity occurrences associated with one occurrence of related entity 29  Figure 4.7 - Connectivity and Cardinality in an ERD PROF_ID CLASS_ID PROF_ID 001 5001 001 002 5002 001 5003 001 5004 001 5005 002 each professor teaches up to four classes REMEMBER! Connectivities and cardinalities are established by very concise statements known as business rules 30  Existence Dependence Existence dependence Existence independence Some of parts are produced “in- house” and other parts are bought from vendors. Entity exists in the database Entity exists apart from all of only when it is associated its related entities At least some of the parts are not supplied by a vender. with another related entity Referred to as a strong entity occurrence or regular entity PART is existence-independent from VENDOR CLASS is existence-dependent on COURSE (parent entity) EMPLOYEE claims FAMILY MEMBERS — FAMILY MEMBERS is existence-dependent on EMPLOYEE 31 Relationship strength, participation & degree; Weak & composite entities; Recursive relationship Relationship Strength - based on how the primary key of a related entity is defined To implement a relationship, the primary key of one entity appears as a foreign key in the related entity Weak (non-identifying) relationship Primary key of the related entity does not contain a primary key component of the parent entity Strong (identifying) relationships Primary key of the related entity contains a primary key component of the parent entity 33  Figure 4.8 - A Weak (Non-Identifying) Relationship between COURSE and CLASS Primary key of the related entity does not contain a primary key component of the parent entity Cengage Learning © 2015 COURSE(CRS_CODE, DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT) CLASS(CLASS_CODE, CRS_CODE, CLASS_SECTION, CLASS_TIME, ROOM_CODE, PROF_NUM) 34  Figure 4.9 - A Strong (Identifying) Relationship between COURSE and CLASS Primary key of the related entity contains a primary key component of the parent entity Cengage Learning © 2015 COURSE(CRS_CODE, DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT) CLASS(CRS_CODE, CLASS_SECTION , CLASS_TIME, ROOM_CODE, PROF_NUM) 35 A weak relationship A strong relationship A Scenario: For example, suppose that the ABC Corporation uses parts to Existence independence produce its products. Furthermore, suppose that some of those parts are produced in-house and other parts are bought from vendors. Entity exists apart from all of its related In that scenario, it is quite possible for a PART to exist independently entities from a VENDOR in the relationship “PART is supplied by VENDOR,” because at least some of the parts are not supplied by a vendor. Referred to as a strong entity or regular Therefore, PART is existence-independent from VENDOR. entity Weak Entity Conditions Existence-dependent Has a primary key that is partially or totally derived from parent entity in the relationship Database designer determines whether an entity is weak based on business rules 37 Figure 4.10 - A Weak Entity in an ERD Relationship? Strong / weak A strong (identifying) relationship indicates that the related entity is weak. Such a relationship means that both conditions for the weak entity definition have been met: the related entity is existence- dependent the PK of the related entity contains a PK component of the parent entity. 38  Figure 4.11 - A Weak Entity in a Strong Relationship Strong/regular entity inherited Weak entity 39 Relationship Participation Optional participation One entity occurrence does not require a corresponding entity occurrence in a particular relationship Mandatory participation One entity occurrence requires a corresponding entity occurrence in a particular relationship 40 Figure 4.13 - CLASS is Optional to COURSE Optional Relationship Participation One entity occurrence does not require a corresponding entity occurrence in a particular relationship connectivity (1,1) (0,N) Cardinality? 41 Figure 4.14 - COURSE and CLASS in a Mandatory Relationship Mandatory Relationship Participation: One entity occurrence requires a corresponding entity occurrence in a particular relationship the minimum cardinality is at least 1 for the mandatory entity 42 Table 4.3 - Crow’s Foot Symbols 43 Relationship Degree Indicates the number of entities or participants associated with a relationship Unary relationship: Association is maintained within a single entity Recursive relationship: Relationship exists between occurrences of the same entity set Binary relationship: Two entities are associated Ternary relationship: Three entities are associated 44  Figure 4.15 - Three Types of Relationship Degree exists when two entities are associated in a the existence of the “manages” relationship relationship means that EMPLOYEE requires another EMPLOYEE to be the manager following business rules: A DOCTOR writes one or more a recursive relationship PRESCRIPTIONs. A PATIENT may receive one or more PRESCRIPTIONs. A DRUG may appear in one or more PRESCRIPTIONs. (To simplify this example, assume that the business rule states that each prescription contains only one drug. In short, if a doctor prescribes more than one drug, a separate prescription must be written for each drug.) 45 Figure 4.17 - An ER Representation of Recursive Relationships 1:1 unary relationship 1:M unary relationship M:N unary relationship an EMPLOYEE may a COURSE may be a an EMPLOYEE may be prerequisite to many manage many married to one and only other COURSEs, and EMPLOYEEs, and one other EMPLOYEE each COURSE may each EMPLOYEE is managed by one have many other EMPLOYEE. COURSEs as prerequisites. 46 Associative Entities Also known as composite or bridge entities Used to represent an M:N relationship between two or more entities Is in a 1:M relationship with the parent entities Composed of the primary key attributes of each parent entity May also contain additional attributes that play no role in connective process Bridge entity 47 Figure 4.23 - Converting the M:N Relationship into Two 1:M Relationships Bridge/Associate/Composite entity Bridge/Associate/Composite entity Bridge/Associate/Composite entity 48 Developing an ER diagram  Developing an ER Diagram Create a detailed narrative of the organization’s description of operations Identify business rules based on the descriptions Identify main entities and relationships from the business rules Develop the initial ERD Identify the attributes and primary keys that adequately describe entities Revise and review ERD 50 The 1st Tiny College ERD Segment Tiny College (TC) is divided into several schools: a school of business, a school of arts and sciences, a school of education, and a school of applied sciences. Each school is administered by a dean who is a professor. Each professor can be the dean of only one school, and a professor is not required to be the dean of any school 1:1 relationship exists between PROFESSOR and SCHOOL. Note that the cardinality can be expressed by writing (1,1) next to the entity PROFESSOR and (0,1) next to the entity SCHOOL The smallest number of departments operated by a school is one, and the largest number of departments Each school comprises several departments. For example, is indeterminate (N). On the other hand, each the school of business has an accounting department, a department belongs to only a single school; thus, the management/marketing department, an cardinality is expressed by (1,1). That is, the minimum economics/finance department, and a computer number of schools that a department belongs to is information systems department. one, as is the maximum number. 51 The 2nd Tiny College ERD Segment (cont.) Each department may offer courses. For example, the management/marketing department offers courses such as Introduction to Management, Principles of Marketing, and Production Management. Note that this relationship is based on the way Tiny College operates. If, for example, Tiny College had some departments that were classified as “research only,” those departments would not offer courses; therefore, the COURSE entity would be optional to the DEPARTMENT entity. 52 The 3rd Tiny College ERD Segment (cont.) A department may offer several sections (classes) of the same database course. Each of those classes is taught by a professor at a given time in a given place. In short, a 1:M relationship exists between COURSE and CLASS. However, because a course may exist in Tiny College’s course catalog even when it is not offered as a class in a current class schedule, CLASS is optional to COURSE. 53 The 4th Tiny College ERD Segment (cont.) Each department should have one or more professors assigned to it. One and only one of those professors chairs the department, and no professor is required to accept the chair position. Therefore, DEPARTMENT is optional to PROFESSOR in the “chairs” relationship. 54 The 5th Tiny College ERD Segment (cont.) Each professor may teach up to four classes; each class is a section of a course. A professor may also be on a research contract and teach no classes at all. 55  The 6th Tiny College ERD Segment (Cont.) A student may enroll in several classes but takes each class only once during any given enrollment period. For example, during the current enrollment period, a student may decide to take five classes—Statistics, Accounting, English, Database, and History—but that student would not be enrolled in the same Statistics class five times during the enrollment period! Each student may enroll in up to six classes, and each class may have up to 35 students, thus creating an M:N relationship between STUDENT and CLASS. Because a CLASS can initially exist (at the start of the enrollment period) even though no students have enrolled in it, STUDENT is optional to CLASS in the M:N relationship. This M:N relationship must be divided into two 1:M relationships through the use of the ENROLL entity. Note also that the ENROLL entity is weak: it is existence-dependent, and its (composite) PK is composed of the PKs of the STUDENT and CLASS entities. can add the cardinalities (0,6) and (0,35) next to the ENROLL entity to reflect the business rule constraints56  The 7th Tiny College ERD Segment (Cont.) Each department has several (or many) students whose major is offered by that department. However, each student has only a single major and is, therefore, associated with a single department. However, in the Tiny College environment, it is possible—at least for a while—for a student not to declare a major field of study. Such a student would not be associated with a department; therefore, DEPARTMENT is optional to STUDENT. It is worth repeating that the relationships between entities and the entities themselves reflect the organization’s operating environment. That is, the business rules define the ERD components. 57 The 8th Tiny College ERD Segment (Cont.) Each student has an advisor in his or her department; each advisor counsels several students. An advisor is also a professor, but not all professors advise students. Therefore, STUDENT is optional to PROFESSOR in the “PROFESSOR advises STUDENT” relationship. 58 The 9th Tiny College ERD Segment (Cont.) The CLASS entity contains a ROOM_CODE attribute. Given the naming conventions, it is clear that ROOM_CODE is an FK to another entity. Clearly, because a class is taught in a room, it is reasonable to assume that the ROOM_CODE in CLASS is the FK to an entity named ROOM. In turn, each room is located in a building. A BUILDING can contain many ROOMs, but each ROOM is found in a single BUILDING. It is clear that some buildings do not contain (class) rooms. For example, a storage building might not contain any named rooms at all. 59 Components of the ERM of the Tiny College 61 Database Design Challenges: Conflicting Goals Database design must conform to design standards Need for high processing speed may limit the number and complexity of logically desirable relationships Need for maximum information generation may lead to loss of clean design structures and high transaction speed 62 Various Implementations of the 1:1 Recursive Relationship Can have data anomaly if a divorce occurs. Annes Jones divorces Anton Shapiro Two records must be updated Contains nulls for employees who are not married to other employees within the same company (e.g. Delaney Robert) 63 Various Implementations of the 1:1 Recursive Relationship (Cont.) Eliminate the nulls for employees who are not married to other employees within the same company Still yield possible duplicate values “345,347” VERSUS “347, 345” “346,349” VERSUS “349, 346” Synonym (e.g. EMP_NUM & EMP_SPOUSE) 64 Various Implementations of the 1:1 Recursive Relationship (Cont.) This implementation requires an additional information such as marriage date (which may not be collected). 65