cm2020: introduction to database systems conceptual modelling with the entity relationship model...
TRANSCRIPT
![Page 1: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/1.jpg)
CM2020: Introduction to Database Systems
Conceptual Modelling with The Entity Relationship Model
Database Systems 4th edition
Connolly and Begg Chapter 11
Nirmalie Wiratunga
![Page 2: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/2.jpg)
Aim of Lecture
• Outline the steps involved in designing a database• Explain the first phase: Conceptual Modelling• Study a particular conceptual model:
– Entity Relationship (ER) Model
• By the end you should be able to– Explain what conceptual design is, and how it is used– Represent a real-world situation as an ER Model– Understand an ER model constructed by someone else
![Page 3: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/3.jpg)
Role of Conceptual Modelling within the design process
Real-World Organisation /Problem
Logical Data Model
Conceptual Data Model
Physical Model (via DBMS)
Identify key concepts and data needsCreate a conceptual model
Convert model to structures required by database (relational, object-oriented, etc.)
Implement using a DBMS (MSA, ORACLE):create tables, add data, constraints, etc.
![Page 4: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/4.jpg)
– Use human understandable terms, not computer termsTables, Combo Boxes, Foreign Keys…
The Conceptual Data Model
– Implementation independent– One form is the Entity Relationship (ER) model– Basis for the next step: the logical model
• Abstract view of situation– Identify important entities and relationships between them
Library: Books, Members, Carpets
Member borrows Book
![Page 5: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/5.jpg)
Simplicity of ER modelling
Staff
StaffIdNameTitle
WorksOn
StaffIdProjectId
Project
ProjectIdNameStartDate
11∞
∞
ER diagram
Tables
Suppose we wish to record details of staff working on project.
ER diagrams are simpler than the table representation
Works_onProjectStaff
![Page 6: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/6.jpg)
Purpose of ER modelling?
• ER model is simpler and easier to understand– Helps discussions with customers and fellow-workers
• Separates – Modelling the real-world problem situation– Designing the DB tables for a DBMS (e.g. MSA)
• Most large organisations will require ER Modelling
![Page 7: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/7.jpg)
Contents• Components of an ER model • Entity
– Entity sets and descriptions• Relationship
– Functional and membership class descriptions• Constraints and Assumptions• Putting it all together with example ER Models• Advance concepts
– Inheritance – Recursion– Ternary relationships
![Page 8: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/8.jpg)
Components of an ER Model• The ER Model consists of four items:
1. An ER Diagram (using Bachman Notation)- graphical representation of the entities and the
relationships between them2. A formal description of each entity in terms of its
attributes and identifiers3. Descriptions of the meaning of relationships4. Descriptions of any constraints on the system
and of any assumptions made
Note: the completion of an ER Model is iterative and unlikely to be successful first time round
![Page 9: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/9.jpg)
MakesDriver Trip Vehicle
Incurs
Includes
Uses
Food Processor
Expense
Maintenance
Engineer QualificationHolds
CustomerStop
DeliversReceives
Food Processor Type
HasUndergoes
VisitsDate-obtained
Performs
Staff
Example of a realistic ER diagram
![Page 10: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/10.jpg)
How do you obtain an ER Model ?
• Given a specification, you need to identify the following concepts:– Entities: ‘things’ with physical or conceptual
existence - usually nouns– Relationships: between entities - usually verbs– Attributes: of each entities and sometimes
relationships– any constraints or assumptions
![Page 11: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/11.jpg)
Identify ER Concepts in a Specification
Departments control many projects and each department has many employees. Each employee works on only one project at a time. A project’s start datestart date must be before the project’s target completion datecompletion date.
Entities: departments, projects, employees
Relationships: control - between departments and projectshas - between departments and employeesworks_on - between employees and projects
Attributes: Start date, completion date for project. NI number, name, address for employee.
Constraints: A project’s start date must be before its target completion date.
Entity Attribute Attribute RelationshipRelationship
![Page 12: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/12.jpg)
Contents• Components of an ER model • Entity
– Entity sets and descriptions• Relationship
– Functional and membership class descriptions• Constraints and Assumptions• Putting it all together with example ER Models• Advance concepts
– Inheritance – Recursion– Ternary relationships
![Page 13: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/13.jpg)
Entity• Definition
– real-world object distinguishable from other objects and described using a common set of attributes.
– Diagrammatically shown as
• Entity Set: a collection of similar entities– E.g. all employers, all students– the set tends to have the same number of attributes– must have a primary key
entity-name
![Page 14: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/14.jpg)
Entity Examples
• Delivery databaseDrivers Customers Deliveries Invoice
• College registration databaseStudent Instructor Classroom Course
• Restaurant databaseMenu Recipe Ingredient Order
• Personal Music database ?
![Page 15: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/15.jpg)
Descriptions of entities
• Properties of entities are called attributes– Simple or composite e.g. name (firstname, surname)– Identifiers: One or more unique attributes are chosen as an
identifier for the entity (a.k.a. primary key)– Single or multi-valued e.g. hobbies {cycling,reading,music}– Derived e.g. TotalCost (UnitCost * Quantity)
• Entity description = Entity name + identifier + other attributes
• Entity Set: the set of entity descriptions
![Page 16: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/16.jpg)
Entity Sets
Supervisor Student
Supervisor(staffID, name, jobTitle, address)
Student(studentID, name, address, staffId*)
Entity Setsdo NOT include foreign keys
at the conceptual modelling
stageOther Examples of Entity Sets:
Driver(driver#, first-name, surname, address, #points)Exam(module#, student#, grade)
![Page 17: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/17.jpg)
Contents• Components of an ER model • Entity
– Entity sets and descriptions• Relationship
– Functional and membership class descriptions• Constraints and Assumptions• Putting it all together with example ER Models• Advance concepts
– Inheritance – Recursion– Ternary relationships
![Page 18: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/18.jpg)
Relationships• An association among entities.
– Supervisor supervises student
• Relationships are described in terms of their– Functionality – Membership Class– Attributes
Supervisor Studentsupervises
![Page 19: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/19.jpg)
Relationship: FunctionalityEach supervisor can supervise many students, but each student has only one supervisor
Supervisor Studentsupervises
Functionality answers two questions:• Can the supervisor have more than one student? Yes• Can the student have more than one supervisor? No
So we’re interested in the MAX number of each entity involved: is it 1 or many?
![Page 20: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/20.jpg)
Functionality Types
1- to -1 1- to many many to -1 many to many
Functionality types are based on different mappings between entities
![Page 21: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/21.jpg)
Relationship: Membership ClassA supervisor does not have to supervise any students.A student has to have a supervisor.
The membership class answers two questions:• Must the supervisor have at least one student? No• Must the student have at least one supervisor? Yes
So we’re interested in the MIN number of each entity involved: is it 0 or 1?
Where, 0 means optional and 1 means mandatory
Supervisor Studentsupervises
Supervisor’s participation in
supervision is optionalStudent’s participation in supervision
is mandatory
![Page 22: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/22.jpg)
A supervisor may supervise no students, or may supervise many
students
A student must have at least one supervisor, and not more than one supervisor
Combining Functionality and Membership
Supervisor Studentsupervises
![Page 23: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/23.jpg)
Relationship: Example 1
Castle School
High School
Hill School
Hamilton
Stevens
Walters
TeacherSchoolemploy
Brian
Paul
Every school must employ atleast one or more TeachersEvery teacher must work in at most one school
![Page 24: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/24.jpg)
Castle School
High School
Hill School
Hamilton
Stevens
Walters
TeacherSchoolemploy
Brian
Paul
Every school must employ one or more TeachersEvery teacher may work in zero or one school
- Paul does not work in any school
Relationship: Example 2
![Page 25: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/25.jpg)
Relationship: Example 3
Castle School
High School
Hill School
Hamilton
Stevens
Walters
TeacherSchoolemploy
Brian
Paul
Schools can employ many teachers, but some schools have yet to recruit.Some teachers do not work whilst others work in a school.
![Page 26: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/26.jpg)
Relationship: Example 4
Castle School
High School
Hill School
Hamilton
Stevens
Walters
TeacherSchoolemploy
Brian
Paul
Schools can employ many teachers, but some schools have yet to recruit All teachers must work in a school.
![Page 27: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/27.jpg)
Description of Relationships
Supervisor Studentsupervises
Functionality: one to many, written [1:M]many to many, written [M:N]one to one, written [1:1]
Membership class: optional to mandatory, written [o:m]
Description of relationship is therefore:Supervises: Supervisor supervises student [1:M] [o:m]
![Page 28: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/28.jpg)
Description of Relationships with Attributes
works_inAssistant Lab
hours
Assistant works in Lab for many hours per week.
works_in(hours): assistant works in lab [M:N] [o:m]
Description of relationship
![Page 29: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/29.jpg)
Contents• Components of an ER model • Entity
– Entity sets and descriptions• Relationship
– Functional and membership class descriptions• Constraints and Assumptions• Putting it all together with example ER Models• Advance concepts
– Inheritance – Recursion– Ternary relationships
![Page 30: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/30.jpg)
Descriptions of Constraints / Assumptions
• Examples of constraints:– #points on driver’s license must be less than 11– pickup_date must be before delivery_date– Driver title must be Mr, Mrs or Ms
• Examples of Assumptions– Typically involve assumptions made about a
relationship’s membership and/or functionality
![Page 31: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/31.jpg)
Contents• Components of an ER model • Entity
– Entity sets and descriptions• Relationship
– Functional and membership class descriptions• Constraints and Assumptions• Putting it all together with example ER Models• Advance concepts
– Inheritance – Recursion– Ternary relationships
![Page 32: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/32.jpg)
Example ER model: a staff project management system
Supervisor Studentsupervises
Supervisor(StaffID, Name, JobTitle, Address)
Student(StudentID, Name, Address, date_enrolled, completion_date, DOB)
Entity Sets
Supervises: Supervisor supervises student [1:M] [o:m]Relationships
Constraints/AssumptionsStudent’s date_enrolled must be before completion_date
ER diagram
![Page 33: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/33.jpg)
Example ER model: A library System
Entities– Book(ISBN, title, author,…)– Member(MemberID, name, address, phone#, …)
Relationships– Borrows(return-date) – members borrow books [1:M][o:o]
Constraints and assumptions:– A member can borrow up to 6 books at once– Each book can be borrowed by at most one member
ER diagramreturn-date
borrowsMember Book
![Page 34: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/34.jpg)
Contents• Components of an ER model • Entity
– Entity sets and descriptions• Relationship
– Functional and membership class descriptions• Constraints and Assumptions• Putting it all together with example ER Models• Advance concepts
– Inheritance – Recursion– Ternary relationships
![Page 35: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/35.jpg)
Advanced Concepts of ER diagrams
ER Diagram, which is part of the ER Model, can become more complex i.e., we may have the following:– Entity Subsets and Supersets– Complex Relationships
• Involuted or Recursive Relationships• Ternary Relationships
![Page 36: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/36.jpg)
Entity subsets and supersets
makesDriver Trip
MaintenanceEngineerperforms
Staff
Branch has many Staff.
Engineers perform many maintenance tasks.
Drivers make many trips.
Branch has
![Page 37: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/37.jpg)
Unusual Relationships
• Recursive
Employee
manages
• Ternary
Programmer Project
Site
works on
Convert works on into an entity
![Page 38: CM2020: Introduction to Database Systems Conceptual Modelling with The Entity Relationship Model Database Systems 4 th edition Connolly and Begg Chapter](https://reader035.vdocuments.us/reader035/viewer/2022062619/5515e59f55034638038b4e18/html5/thumbnails/38.jpg)
Summary ER Modelling
– A conceptual design technique
– Is independent of the type of logical model / database you’re going to transform it into
– Contains entities, attributes, relationships and constraints/assumptions
– ER Diagram is a graphical representation using the Bachman notation