Conceptual Modeling and Entity-Relationship Diagrams Chapter 3 & 4: Elmasri/Navathe 3753 X1
Outline • Phases of Database Design • Conceptual Modeling • Abstractions in Conceptual Design • Example Database Requirements • Deconstructing the E-R Diagram – Entities, Attributes and Relationships – Participation, Cardinality and Keys 3753 X1 2
Phases of Database Design Application Domain Requirements Analysis Database Requirements DBMS Independent Conceptual Design Conceptual Schema Data Model Mapping Implementation Schema Physical Design DBMS Dependent Physical Schema 3753 X1 3
Conceptual Design • Similar to the analysis phase in software development – produce a description of the data – capture the semantics of the data • Description in a high-level model – close to the user’s view of the world – abstract concepts – means of communication between the user and the developer 3753 X1 4
Reasons for Conceptual Modeling • Independent of DBMS. • Allows for easy communication between end-users and developers. • Has a clear method to convert from high- level model to relational model. • Conceptual schema is a permanent description of the database requirements. 3753 X1 5
Abstractions in Conceptual Design • An abstraction is a mental process where we select some set of properties of an object and exclude others. • 3 types of abstractions – classification – aggregation – generalization 3753 X1 6
Classification • Define a class of real-world objects with common properties Month … January February December 3753 X1 7
Aggregation • Define a new class from a set of other classes that represent component parts Car Tires Steering Wheel Engine Gas pedal 3753 X1 8
Generalization • Defines a subset relationship between elements of 2 or more classes Person Employee Student Faculty Staff 3753 X1 9
Entity-Relationship Model • Most popular conceptual model for database design • Basis for many other models • Describes the data in a system and how that data is related • Describes data as entities, attributes and relationships 3753 X1 10
Database requirements • We must convert the written database requirements into an E-R diagram • Need to determine the entities, attributes and relationships. – nouns = entities – adjectives = attributes – verbs = relationships 3753 X1 11
Acadia Teaching Database Design an E-R schema for a database to store info about professors, courses and course sections indicating the following: • The name and employee ID number of each professor • The salary and email address(es) for each professor • How long each professor has been at the university • The course sections each professor teaches • The name, number and topic for each course offered • The section and room number for each course section • Each course section must have only one professor • Each course can have multiple sections 3753 X1 12
Visual View of the Database Employee ID Years Teaching Section ID Room Start Date N 1 Section Professor teaches Email N Part First Salary of Name 1 Last Number Course Topic Name 3753 X1 13
The Pieces • Objects – Entity (including weak entities) – Attribute – Relationship • “Structural” Constraints – Cardinality – Participation 3753 X1 14
Entities • Entity – basic object of the E-R model – Represents a “thing” with an independent existence – Can exist physically or conceptually • a professor, a student, a course • Entity type – used to define a set of entities with the same properties. 3753 X1 15
Entity and Entity Types Name Number Entity Type Topic Course Number : 3753 Entity Name : Database Management Systems Topic : Introduction to DBMSs 3753 X1 16
Attributes • Each entity has a set of associated properties that describes the entity. These properties are known as attributes . • Attributes can be: – Simple or Composite – Single or Multi-valued – Stored or Derived – NULL 3753 X1 17
Attributes (cont’d) Simple Professor Start Date First Professor Composite Name Last 3753 X1 18
Attributes (cont’d) Single Professor Employee ID# Multi-Valued Professor Email 3753 X1 19
Attributes (cont’d) Stored Professor Start Date Derived Professor Years Teaching 3753 X1 20
Attributes (cont’d) • NULL attributes have no value – not 0 (zero) – not a blank string • Attributes can be “nullable” where a null value is allowed, or “not nullable” where they must have a value. 3753 X1 21
Primary Keys Professor Employee ID • Employee ID is the primary key • Primary keys must be unique for the entity in question 3753 X1 22
Relationships • defines a set of associations between various entities • can have attributes to define them • are limited by: – Participation – Cardinality Ratio 3753 X1 23
Relationships (cont’d) Section part of Course 3753 X1 24
Participation • Defines if the existence of an entity depends on it being related to another entity with a relationship type. – Partial – Total Section part of Course 3753 X1 25
Cardinality • The number of relationships that an entity may participate in. – 1:1, 1:N, N:M, M:1 N 1 Section part of Course 3753 X1 26
Weak entity • Weak entities do not have key attributes of their own. • Weak entities cannot exist without another a relationship to another entity. • A partial key is the portion of the key that comes from the weak entity. The rest of the key comes from the other entity in the relationship. • Weak entities always have total participation as they cannot exist without the identifying relationship. 3753 X1 27
Weak Entity (cont’d) Section Section ID Identifying Relationship part of Number Course 3753 X1 28
Review of the ER Diagram Employee ID Years Teaching Section ID Room Start Date N 1 Section Professor teaches Email N First Salary Part Name of Last 1 Number Course 3753 X1 Topic Name 29
University DB Case Study • Maintain the following information about undergrad students: – Name, address , student number, date of birth, year of study, degree program (BA, BSc, BCS), concentration (Major, Honours, etc) and department of concentration. • Note: An address is composed of a street, city, province and postal code; the student number is unique for each student 3753 X1 30
University Case Study (cont’d) • Maintain information about departments – Name, code (CS, Phy), office phone, and faculty members • Maintain information about courses: – Course number (3753), title, description, prerequisites. • Maintain information about course sections: – Section (A, B, C), term (X1), slot #, instructor 3753 X1 31
University Case Study (cont’d) • Maintain information about faculty: – Name, rank, employee number, salary, office number, phone number and email address. – Note: employee number is unique • Maintain a program of study for the current year for each student: – i.e. courses that each student is enrolled in 3753 X1 32
Extended E-R Model • E-R model is sufficient for traditional database applications • Nontraditional applications (CAD, multimedia) have more complex requirements • Can extend traditional E-R diagrams with semantic data modeling concepts 3753 X1 33
IS-A Relationship (cont’d) Name S.I.N. Employee d Staff Teaching Assistant Faculty Rank Position Student # 3753 X1 34
Specialization & Generalization • Specialization – process of taking an entity and creating several specialized subclasses • Generalization – process of taking several related entities and creating a general superclass • We will talk mainly of specialization , but most information will also apply to generalization 3753 X1 35
Specialization constraints • Specializations can be predicate-defined or attribute-defined (otherwise called user- defined) • Disjointness constraint – specialization is disjoint or overlapping • Completeness constraint – specialization is total or partial 3753 X1 36
Predicate-defined subclass • An attribute value is used to determine the members of a subclass • Not all members of every subclass can be determined by the attribute value • In the following example, the Pension Plan type can be used to determine faculty from staff, but has no effect on students or those who opted out of the pension plan. 3753 X1 37
Predicate-defined subclass Pension Employee S.I.N. Plan Type Note: not all d employees included Staff Faculty Rank Position 3753 X1 38
Attribute-defined subclass • There is one defining attribute for all subclasses • Each member of the superclass can be assigned to the appropriate subclass based on this one attribute 3753 X1 39
Attribute-defined subclass Jobtype S.I.N. Employee Jobtype d “Faculty” “Staff” “Student” Students Faculty Staff Rank Year Rank 3753 X1 40
User-defined subclass • When there is no condition to automatically determine membership in a subclass, it must be done at the discretion of the user. 3753 X1 41
Recommend
More recommend
