Database Systems-1 (IS301) Lecture 3 PDF

1 University of Sadat City Faculty of Computers and Artificial Intelligence (FCAI) Database Systems-1 (IS301) Lecture 3 Presented By: Dr. Ahmed Tealeb Information Systems Department 1s...

1 University of Sadat City Faculty of Computers and Artificial Intelligence (FCAI) Database Systems-1 (IS301) Lecture 3 Presented By: Dr. Ahmed Tealeb Information Systems Department 1st Semester 2022-2023 Fundamentals of Database Systems Seventh Edition Chapter 3 Data Modeling Using the Entity-Relationship (ER) Model Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Learning Objectives (1 of 2) Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Learning Objectives (2 of 2) Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Overview of Database Design Process (1 of 2) – – – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Overview of Database Design Process (2 of 2) Figure 3.1 A simplified diagram to illustrate the main phases of database design. Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Methodologies for Conceptual Design Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Example COMPANY Database (1 of 2) – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Example COMPANY Database (2 of 2) – ▪ ▪ ▪ – ▪ Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved ER Model Concepts (1 of 2) – ▪ – ▪ Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved ER Model Concepts (2 of 2) – ▪ – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Types of Attributes (1 of 3) – – ▪ ▪ ▪ Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Types of Attributes (2 of 3) – ▪ – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Types of Attributes (3 of 3) – – ▪ Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Example of a Composite Attribute Figure 3.4 A hierarchy of composite attributes. Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Entity Types and Key Attributes (1 of 2) – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Entity Types and Key Attributes (2 of 2) – – ▪ ▪ Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Entity Set – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Value Sets (Domains) of Attributes – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Attributes and Value Sets A :E PV PV A(e). Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Displaying An Entity Type – – – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Notation for ER Diagrams Figure 3.14 Summary of the notation for ER diagrams. Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Entity Type CAR with Two Keys and a Corresponding Entity Set Figure 3.7 The CAR entity type with two key attributes, Registration and Vehicle_id. (a) ER diagram notation. (b) Entity set with three entities. Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Initial Conceptual Design of Entity Types for the COMPANY Database Schema – – – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Initial Design of Entity Types: EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT Figure 3.8 Preliminary design of entity types for the COMPANY database. Some of the shown attributes will be refined into relationships. Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Refining the Initial Design by Introducing Relationships – – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Relationships and Relationship Types – – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Relationship Instances of the WORKS_FOR N:1 relationship between EMPLOYEE and DEPARTMENT Figure 3.9 Some instances in the WORKS_FOR relationship set, which represents a relationship type WORKS_FOR between EMPLOYEE and DEPARTMENT Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Relationship Instances of the M:N WORKS_ON Relationship between EMPLOYEE and PROJECT Figure 3.13 An M:N relationship, WORKS_ON. Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Relationship Type V s Relationship Set (1 of 2) ersu – – – – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Relationship Type V s Relationship Set (2 of 2) ersu – – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Refining the Company Database Schema by Introducing Relationships By examining the requirements, six relationship types are identified All are binary relationships( degree 2) Listed below with their participating entity types: – WORKS_FOR (between EMPLOYEE, DEPARTMENT) – MANAGES (also between EMPLOYEE, DEPARTMENT) – CONTROLS (between DEPARTMENT, PROJECT) – WORKS_ON (between EMPLOYEE, PROJECT) – SUPERVISION (between EMPLOYEE (as subordinate), EMPLOYEE (as supervisor)) – DEPENDENTS_OF (between EMPLOYEE, DEPENDENT) Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved ER Diagram – Relationship Types Are: WORKS_FOR, MANAGES, WORKS_ON, CONTROLS, SUPERVISION, DEPENDENTS_OF Figure 3.2 An ER schema diagram for the COMPANY database. The diagrammatic notation is introduced gradually throughout this chapter and is summarized in Figure 3.14 (see slide 51). Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Discussion on Relationship Types – → – → – → – – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Constraints on Relationships – – ▪ ▪ ▪ – ▪ ▪ Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Many-To-One (N:1) Relationship Figure 3.9 Some instances in the WORKS_FOR relationship set, which represents a relationship type WORKS_FOR between EMPLOYEE and DEPARTMENT. Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Many-To-Many (M:N) Relationship Figure 3.13 An M:N relationship, WORKS_ON. Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Recursive Relationship Type – – – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Displaying a Recursive Relationship – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved A Recursive Relationship Supervision` Figure 3.11 A recursive relationship SUPERVISION between EMPLOYEE in the supervisor role (1) and EMPLOYEE in the subordinate role (2). Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Recursive Relationship Type is: Supervision (Participation Role Names Are Shown) Figure 3.2 An ER schema diagram for the COMPANY database. The diagrammatic notation is introduced gradually throughout this chapter and is summarized in Figure 3.14. (see slide 51) Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Weak Entity Types (1 of 2) – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Weak Entity Types (2 of 2) – – – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Attributes of Relationship Types – – ▪ – ▪ Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Example Attribute of a Relationship Type: Hours of WORKS_ON Figure 3.2 An ER schema diagram for the COMPANY database. The diagrammatic notation is introduced gradually throughout this chapter and is summarized in Figure 3.14. (see slide 51) Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Notation for Constraints on Relationships – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Alternative (Min, Max) Notation for Relationship Structural Constraints: (1 of 2) min  max, min  0, max  1 Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Alternative (Min, Max) Notation for Relationship Structural Constraints: (2 of 2) – ▪ ▪ – ▪ ▪ Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved The (Min,Max) Notation for Relationship Constraints Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Company ER Schema Diagram Using (Min, Max) Notation Figure 3.15 ER diagrams for the company schema, with structural constraints specified using (min, max) notation and role names. Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Alternative Diagrammatic Notation Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Summary of Notation for ER Diagrams Figure 3.14 Summary of the notation for ER diagrams. Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved UML Class Diagrams – – – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved UML Class Diagram for Company Database Schema Figure 3.16 The COMPANY conceptual schema in UML class diagram notation. Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Other Alternative Diagrammatic Notations Figure A.1 Alternative notations. (a) Symbols for entity type/class, attribute, and relationship. (b) Displaying attributes. (c) Displaying cardinality ratios. (d) Various (min, max) notations. (e) Notations for displaying specialization/generalization. Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Relationships of Higher Degree Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Discussion of N-Ary Relationships (n > 2) (1 of 2) Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Example of a Ternary Relationship Figure 3.17 Ternary relationship types. (a) The SUPPLY relationship. (b) Three binary relationships not equivalent to SUPPLY. (c) SUPPLY represented as a weak entity type. Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Discussion of N-Ary Relationships (n > 2) (2 of 2) Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Another Example of a Ternary Relationship Figure 3.18 Another example of ternary versus binary relationship types. Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Displaying Constraints on Higher-Degree Relationships – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Another Example: A University Database Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved University Database Conceptual Schema Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Chapter Summary Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Data Modeling Tools (Additional Material ) – – – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved  Some of the Automated Database Design Tools Company Tool Functionality Embarcadero ER Studio Database Modeling in ER and IDEF1X Technologies Embarcadero DB Artisan Database administration, space and security Technologies management Oracle Developer 2000/Designer 2000 Database modeling, application development Popkin System Architect 2001 Data modeling, object modeling, process modeling, Software structured analysis/design Platinum Enterprise Modeling Suite: Data, process, and business component modeling (Computer Erwin, BPWin, Paradigm Plus Associates) Persistence Inc. Pwertier Mapping from O-O to relational model Rational (IBM) Rational Rose UML Modeling & application generation in C++/JAVA Resolution Ltd. Xcase Conceptual modeling up to code maintenance Sybase Enterprise Application Suite Data modeling, business logic modeling Visio Visio Enterprise Data modeling, design/reengineering Visual Basic/C++ (Note: Not All May Be on the Market Now) Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Extended Entity-Relationship (EER) Model (in the Next Chapter) – – – Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved Copyright Copyright © 2016, 2011, 2007 Pearson Education, Inc. All Rights Reserved