Database Fundamentals Lecture: Data Models and ER Diagrams PDF

Lecture 2. Data Models and ER Model Course BUAN 4320.501 Course Title Database Fundamentals for Analytics Instructor Dr. Lidong Wu Term Spring 2025 Meetings We 7:00 PM - 9:45 PM @ ECSW 1.365 01 RELATIONAL DATABASES authors...

Lecture 2. Data Models and ER Model Course BUAN 4320.501 Course Title Database Fundamentals for Analytics Instructor Dr. Lidong Wu Term Spring 2025 Meetings We 7:00 PM - 9:45 PM @ ECSW 1.365 01 RELATIONAL DATABASES authors books name title Eva Baltasar Boulder Han Kang The White Book Gauz Olga Standing Heavy Tokarczuk Flights  name title Eva Baltasar Boulder Han Kang The White Book Gauz Olga Standing Heavy Tokarczuk Flights  name title Eva Baltasar Boulder Han Kang The White Book Gauz Olga Standing Heavy Tokarczuk Flights Tokarczuk The Books of Jacob author book author book ONE-TO-ONE ONE-TO-MANY MANY-TO-MANY 02 ER DIAGRAMS (ENTITY RELATIONSHIP DIAGRAMS)  Relational Data Model How is data structured within a relational database? Representation of entities and their relationships in a database structure Entity relationship diagram (ERD): uses graphic representations to model database components  Chen’s Notation Crow's Foot UML Class Diagram A One-to-Many (1:M) Relationship: a PAINTER can paint many PAINTINGS; each PAINTING is painted by one PAINTER. 1 M PAINTER PAINTING PAINTER 1..1 1..* PAINTING PAINTER paints PAINTING paints paints painted by A Many-to-Many (M:N) Relationship: an EMPLOYEE can learn many SKILLS; each SKILL can be learned by many EMPLOYEES. M N EMPLOYEE SKILL EMPLOYEE SKILL learns 1..* 1..* EMPLOYEE learns SKILL learns learned by A One-to-One (1:1) Relationship: an EMPLOYEE manages one STORE; each STORE is managed by one EMPLOYEE. 1 1 EMPLOYEE STORE EMPLOYEE STORE EMPLOYEE manages STORE manages 1..1 1..1 manages managed by The Birthday Party by Laurent Mauvignier by Laurent Mauvignier in paperback ISBN by Laurent Mauvignier 978-1-80427-022-6 ◦ Primary Key (PK) - is a column (or a combination of columns) in a table that uniquely identifies each record. It ensures that no two rows have the same value and that 978-1-80427-022-6 the value is not NULL. ◦ There can be only one primary key per table. ◦ Often used as a reference in other tables as a foreign key. 978-1-883723-1111 ISBN by Laurent Mauvignier 978-1-80427-022-6 ratings ◦ Foreign Key (FK) – is a column (or a set of columns) in one table that creates a relationship with the Primary 978-1-80427-022-6 5 Key in another table. It helps maintain referential 978-1-883723-1111 2 integrity. ◦ References a Primary Key in another table. 978-1-883723-1111 3 ◦ Allows duplicate values (unless restricted). ◦ Can contain NULL values (if optional). ◦ Ensures data consistency between related tables. ER DIAGRAM  There are a wide variety of notations for E-R Diagrams. Tools of drawing ER Diagrams: Microsoft PowerPoint, LucidChart Microsoft Visio, and draw.io. In almost all variations, entities are depicted as rectangles with either pointed or rounded corners. The entity name appears inside. Relationships can be displayed as (original Chen’s notation) or can be simply between two entities. – For Relationships, need to convey: Relationship name, degree, cardinality, optionality (minimal cardinality)  Example COMPANY Database Requirements of the Company (oversimplified for illustrative purposes) – The company is organized into DEPARTMENTs. Each department has a name, number and an employee who manages the department. We keep track of the start date of the department manager. A department may have several locations. – Each department controls a number of PROJECTs. Each project has a name, number and is located at a single location. – Store each EMPLOYEE’s social security number, address, salary, sex, and birth date. 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 employee.  ER-DIAGRAM CHEN’S NOTATION Symbol Meaning ENTITY TYPE associative entity WEAK ENTITY TYPE RELATIONSHIP TYPE IDENTIFYING RELATIONSHIP TYPE attr ATTRIBUTE key KEY ATTRIBUTE MULTIVALUED ATTRIBUTE … COMPOSITE ATTRIBUTE DERIVED ATTRIBUTE TOTAL PARTICIPATION OF E2 IN R E1 R E2 PARTIAL PARTICIPATION OF E1 IN R 1 N E1 R E2 CARDINALITY RATIO 1:N FOR E1:E2 IN R (min, max) CONSTRAINT (min, max) ON Chen’s E1 (min, max) R E2 PARTICIPATION OF E1 IN R  ER-DIAGRAM CROW'S FOOT NOTATION WORKS FOR MANAGES -or -ee Crow's Foot  ER-DIAGRAM UML NOTATION associative entity UML  Entity Weak Entity 03 Attribute Key Composite Multivalued ER MODEL CONCEPTS Derived Relationship One-to-One One-to-Many Many-to-One Many-to-Many  ER Model Concepts Entities: objects or things in the mini-world that are represented in the database. – E.g., the STUDENT John Smith, the Research DEPARTMENT, the COMPANY 3M, etc. Attributes: properties used to describe an entity – A STUDENT entity may have a Name, NetID, Address, Class, Major A specific entity will have a value for each of its attributes – For example, a specific student entity may have Name=‘John Smith’, NetID=‘jxs123456’, Address=‘731 Renner, Richardson, TX’, Class=‘Master’, Major=‘BUAN’  Weak Entity Types Elmasri/Navathe definition: Weak Entity - an entity that does NOT have a key attribute of their own. A weak entity must participate in an identifying relationship type with an owner or identifying entity type Weak Entities are identified by composite keys - the combination of: – A partial key of the weak entity type, and – The particular entity they are related to in the identifying relationship type DEPENDENT EMPLOYEE Example: – Suppose that a DEPENDENT entity is identified by the dependent’s firstname and birthdate, and the specific EMPLOYEE that the dependent is related to. DEPENDENT is a weak entity type with EMPLOYEE as its identifying entity type via the identifying relationship type DEPENDENT_OF  Types of Attributes Simple: Each entity has a single atomic value for the attribute – E.g., StudentID or Class Composite: An attribute may be composed of several components – Address (Apt#, House#, Street, City, State, ZipCode, Country) – Name (FirstName, MiddleName, LastName) Figure A hierarchy of composite attributes.  Types of Attributes (cont.) Multi-valued: An attribute may have multiple values – e.g., Color of a CAR denoted as {Colors} – e.g., PreviousDegrees of a STUDENT denoted as {PreviousDegrees} In general, composite and multi-valued attributes may be nested arbitrarily to any number of levels although this is rare. – e.g., PreviousDegrees of a STUDENT is a composite multi-valued attribute denoted as {PreviousDegrees(College, Year, Degree, Field)}  Types of Attributes (cont.) Derived: An attribute is calculated or derived from other attributes It is not physically stored in the database but is instead computed when needed, based on existing attributes. – e.g., Age derived from 𝐂𝐮𝐫𝐫𝐞𝐧𝐭 𝐃𝐚𝐭𝐞 − 𝐃𝐎𝐁 – e.g., TotalPrice derived from 𝐐𝐮𝐚𝐧𝐭𝐢𝐭𝐲 ∗ 𝐔𝐧𝐢𝐭𝐏𝐫𝐢𝐜𝐞 – e.g., FullName derived from 𝐅𝐢𝐫𝐬𝐭𝐍𝐚𝐦𝐞&&𝐋𝐚𝐬𝐭𝐍𝐚𝐦𝐞 – e.g., Area derived from 𝟑. 𝟏𝟒 ∗ 𝐑𝐚𝐝𝐢𝐮𝐬 ∗ 𝐑𝐚𝐝𝐢𝐮𝐬  Entity Types and Key Attributes Entity type: Entities grouped with the same basic attributes – e.g., Mary Lee, John Smith, entities of type EMPLOYEE – e.g., CIA, FBI, entities of type GovernmentAgency Key attribute: An attribute of an entity type for which each entity must have a unique value. – e.g., SSN of EMPLOYEE, StudentID of STUDENT – A key may be composite. – Registration combining (State, Number) – An entity may have more than one key. – e.g., the CAR entity type may have two keys: VehicleIdentificationNumber (popularly called VIN) and Registration (Number, State), also known as license_plate number. FIGURE The CAR entity type with two key attributes, VIN Registration and VIN number. (a) ERD Notation (b) Entity set with three objects Registration (Number, State) , VIN, Make, Model, Year, {Color}  Relationships A relationship relates entities with a specific meaning – STUDENT John Smith takes BUAN6380 COURSE – EMPLOYEE Franklin Wang manages Research DEPARTMENT Relationships of the same type are grouped into a relationship type. – The WORKS_ON relationship type in which EMPLOYEEs and PROJECTs participate ATTRIBUTES OF RELATIONSHIP TYPES: – e.g., HoursPerWeek of WORKS_ON which describes the number_of_hours_per_week that an EMPLOYEE works on a PROJECT.  Recursive Binary Ternary Degree CUSTOMER TEACHER of Relationship 1 1 N MAKES CANCELS teaches M O M N -or -ee The number of ORDER COURSE STUDENT entity types that participate degree 1 degree 2 degree 3 (associate) in a Both MAKES and CANCELS relationship are relationships More than one relationship type can exist between the same entities  Multi-way Relationships There are some cases where three or more entities must be connected by one relationship. – Example: Relationship among students, courses, and professors. Students Taking Courses Teaching Possibly, this E/R diagram is OK: Professors students courses courses professors name code code name Eva Baltasar ITSS 1100 ITSS 1100 Ann Han Kang ITSS 4380 ITSS 4380 Bob Gauz Olga ITSS 1100 BUAN 4300 Tim students courses courses professors name code code name Eva Baltasar ITSS 1100 ITSS 1100 Ann Han Kang ITSS 4380 ITSS 4380 Bob Gauz Olga ITSS 1100 ITSS 1100 Tim  Example The above binary relationships OK if each course has only one professor who is teaching all students in that course. i.e., no other sections But what if students were divided into sections, ̶ Then, a student in ITSS 1100 would be related to only one of the professors for ITSS 1100. Which one? Need a 3-way relationship to tell!  Example (cont.) Courses students courses professors name code name Eva Baltasar ITSS 1100 Ann Students Enrolls Han Kang ITSS 4380 Bob Gauz Olga ITSS 1100 Tim Professors TERNARY VS. BINARY RELATIONSHIPS  Cardinality Relationship Cardinality refers to the number of entity instances involved in the relationship. For example: – One CUSTOMER may be placing ORDERS – many STUDENTS may be signing up for CLASSES – one EMPLOYEE may be receiving PAYCHECK – one SALESPERSON must be assigned COMPANY_CAR 1:1 1:N M:N – 1:1 relationships, also called HAS-A relationship, the two entities involved might be coalesced into one. – M:N relationships: typically split these into two 1:N relationships with an intersection entity.  Cardinality (cont.) Participation of instances in a relationship may be mandatory or optional. For example, CUSTOMER may place CUSTOMER ORDERS EMPLOYEE must fill out PAY_SHEETS This is also called “cardinality” or the “optionality” of a relationship. (optional participation, not existence-dependent) (mandatory, existence-dependent) COMPANY ER Diagram Using (min, max) notation BANK ER Diagram  PROBLEM with ER Notation THE ENTITY RELATIONSHIP MODEL IN ITS ORIGINAL FORM DID NOT SUPPORT THE SPECIALIZATION/ GENERALIZATION ABSTRACTIONS 04 ENHANCED E-R DIAGRAMS Incorporates Set-subset relationships Incorporates Specialization/Generalization Hierarchies  Subtype Entities Attributes of two or more Entities may overlap significantly but not completely. Consider: – FullTimeEmployee(EID,Name,Address,Phone,AnnualSalary,BenefitsCategory) – PartTimeEmployee(EmployeeID,Name,Address,Phone,HourlyRate,MaxHoursP erWeek) One approach would be to put all the attributes into a single entity. – Employee(EmployeeID,Name,Address,Phone,HourlyRate,MaxHoursPerWeek, AnnualSalary,BenefitsCategory)  Subtype Entities (cont.) Second approach, put common attributes into a parent or supertype entity and then have two subtype entities. Relationship is called an IS-A relationship. Phone EID Name Employee Address d Benefits PartTimeEmployee FullTimeEmployee Category Hourly Annual Rate MaxHours PerWeek Salary  Conceptual Design Overview What are the entities and relationships in the enterprise? What information about these entities and relationships should we store in the database? What are the integrity constraints or business rules that hold? Represent this information pictorially in ER diagrams, then map ER diagram into a relational schema.  Use Schema to Enforce Constraints The design schema should enforce as many constraints as possible. – Don't rely on future data to follow assumptions. Example If registrar wants to associate only one instructor with a course, – don‘t allow sets of instructors and – don‘t count on departments to enter only one instructor per course. 05 DESIGN ISSUES  Design Issues Setting: Client has (possibly vague) idea of what they want. You must design a database that represents these thoughts and only these thoughts. Avoid Redundancy = saying the same thing more than once. And Encourages Inconsistency Example Good: addr name name (1,N) (1,1) Manfs Manf. Beers  Design Issues Bad: repeats manufacturer address for each beer they manufacture Manf manf addr name Beers Bad: manufacture’s name said twice. manf addr name name Manfs Manf Beers Design Issues: Entity Sets VS. Attributes You may be unsure which concepts are worthy of being entity sets, and which are handled more simply as attributes. Don’t create needless entity sets to make project “larger”. name Bad: name Manfs Manf Beers Good: manf name Beers Design Issues: Entity Sets VS. Attributes Make an entity set only if it either: 1. is more than a name of something; i.e., it has nonkey attributes or relationships with a number of different entity sets, or 2. is the “many” in a many-one relationship. Example The following design illustrates both points: addr name name (1,N) (1,1) Manfs Manf. Beers Manfs deserves to be an E.S. because we record addr, a nonkey attribute. Beers deserves to be an E.S. because it is at the “many” end. – If not, we would have to make “set of beers” an attribute of Manfs – something we avoid doing, although some may tell you it is OK in E/R model.  Design Issues: Don’t Overuse Weak E.T. There is a tendency to feel that no E.T. has its entities uniquely determined without following some relationships. However, in practice, we almost always create unique ID's to compensate: social-security numbers, VIN's, etc.  Beers-Bars-Drinkers Example addr manf name name Bars sells Beers license Bars sell some beers. Frequents Likes Drinkers like some beers. Drinkers frequent some bars. Drinkers name addr Q: Can we convert the above 3-binary relationships into a ternary?  Exercise: Create the E-R Model The X dealership sells both new and used cars. It operates the following business rules. Draw an ER/EER diagram to represent the E-R Model for X car dealership. A salesperson may sell many cars, but each car is sold by only one salesperson. A customer may buy many cars, but each car is bought by only one customer. A salesperson creates a single invoice for each car he or she sells. A customer gets an invoice for each car he or she buys. A customer may come in just to have his or her car serviced; that is, a customer need not buy a car to be classified as a customer. When a customer takes one or more cars in for repair or service, one service ticket is written for each car. The car dealership maintains a service history for each of the cars serviced. The service records are referenced by the car’s serial number. A car brought in for service can be worked on by many mechanics, and each mechanic may work on many cars. A car that is serviced may or may not need parts (e.g., adjusting a carburetor or cleaning a fuel injector nozzle does not require providing new parts).