Outline
[Link]
Conceptual Mdan
Modeling andia Phases of Database Design
Entity-Relationship AGrams Conceptual Modeling
Abstractions in Conceptual Design
Example Database Requirements
Deconstructing the E-R Diagram
Entities, Attributes and Relationships
Participation, Cardinality and Keys
Phases of Database Design Conceptual Design
Application Domain
Similar to the analysis phase in software
Requirements Analysis development
Database Requlrements produce a description of the data
DBMS
Independent capture the semantics of the data
Conceptual Deslgn
Conceptual Schema Description in a high-level model
close to the user's view ofthe world
Data Model Mapplng
-
Implementatlon Schema abstract concepts
means of communication between the user and
DBMS Physical Deslgn the developer
Dependen Physical Schema
Abstractions in Conceptual
Reasons for Conceptual Modeling Design
Independent of DBMS. As abstraction is a mental process
Allows for easy communication between where we select some set of properties
end-users and developers. of an object and exclude others.
Has a clear method to convert from 3 types of abstractions
high-level model to relational model. Classification
Conceptual schema is a permanent aggregation
description of the database generalization
requirements.
Classification Aggregation
Define a class of real-world objects with Define a new class from a set of other
common properties classes that represent component parts
Month
Car
January February December
Tires Steering Wheel Engine Gas pedal
Generalization Entity-Relationship Model
Defines a subset relationship between Most popular conceptual model for
elements of 2 or more classes database design
Person Basis for many other models
Describes the data in a system and how
Employee that data is related
Describes data as entities, attributes
Staff Student and relationships
Faculty
10
Database requirements Acadia Teaching Database
Design an E-R schema for a database to store info about
We must convert the written database professors, courses and course sections indicating the following:
requirements into an E-R diagram The name and employee ID number of each professor
The salary and emaill address(es) for each
Need to determine the entities, professor
How long each professor has been at the unlversity
attributes and relationships. The course sectlons each professor teaches
nouns = entities The name, number and topic for each course offered
.The section and room number for each course section
adjectives =attributes Each course section must have only one professor
verbs relationships Each course can have multiple sections
11
12
Visual View of the Database
The Pieces
Emplovee TD (Start Date Years Teaching Secton 10 Room
Objects
Secton
Email
Professor teaches Entity (including weak entities)
Attribute
Salary
First
Part of
Relationship
Name
Structural" Constraints
Last
Cardinality
(Number Course Participation
Toplc Name
Entities Entity and Entity Types
Entity basic object of the E-R model
Name
(Number Toplc
Represents a "thing" with an independent Entity Type
existence
Course
Can exlst physically or conceptually
a professor, a student, a course
Entity type used to define a set of Number: 1123
entities with the same properties Entity Name: Computer Programming 2
Toplc: Computer Programming
5
Attributes Attributes (cont'd)
Each entity has a set of associated properties
that describes the entity. These properties Professor (Start Date)
are known as attributes.
Simple
Attributes can be:
Simple or Composite First
Single or Multi-valued
Stored or Derived Professor Name
NULL
Composite Last
Attributes (cont'd) Attributes (cont'd)
Professor (Employee ID:# Stored Professor Start Date)
Single
Multi-Valued Professor Email Derived Professor Years Teaching
Attributes (cont'd) Primary Keys
NULL attributes have no value Professor Employee ID
not 0 (zero)
not a blank string Employee ID is the primary key
"nullable" where null
Primary keys must be unique for the
a
Attributes can be
value is allowed, or "not nullable" where entity in question
they must have a value.
Relationships(cont'd)
Relationships
between
defines a set of associations
various entities
them
can have attributes to define
are limited by: Section part of - Course
Participation
Cardinality Ratio
23
Cardinality Types of relationships
The number of relationships that an The number of relationships that an
entity may participate in. entity may participate in.
1:1, 1:N, N:M, M:1 1:1
Student has passport
Section N part of Course
Types of relationships Weak entity
1:M relationships 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. h e rest of the
key comes from the other èntity in the
relatlonship.
M study ollege Weak entities always have total participation
as they cannot exist without the identiying
Student relationship.
Participation
Weak Entity (cont'd)
Section
existence of an entity depends Section ID
Defines if the
on it being related to another entity with a
relationship type.
Partial
part of
Identifying Relationship
Total
Number
part oof Course
Section Course
29
University DB Case Study
Review of the E-R Diagram
(Assignment-1)
Sectlon. ID) Room
Employee 1 Start Date) Years Teaching Maintain the following information
----*
Section about undergrad students:
teaches
Professor
Emal Name, address, student number, date of
birth, year of study, degree program (BA,
Salary
First
part of BSc, BCS), concentration (Major, Honours,
Name
etc) and department of concentration.
Last
Note: An address is composed of a street, city,
province and postal code; the student number
Course
(Number is unique for each student
2
Toplc Name
LUniversity Case Study (cont'd) University Case Study (contd)
Maintain information about departments Maintain information about faculty:
Name, code (CS, Phy), office phone, and faculty Name, rank, employee number, salary,
members office number, phone number and email
Maintain information about courses: address.
Course number (3753), title, description, Note: employee number is unique
prerequisites. Maintain a program of study for the
Maintain information about course sections:
Current year for each student
Section (A, B, C), term (X1), slot #, instructor
i.e. courses that each student is enrolled in
COMPANY Database(Assignment-2) We keep track of the number of hours per
intoDEPARTMENTs, week that an employee currently works on
The company is organized
name, number, and
an
department has each project.
manages the department.
emptoyeewho
We keep track of the start date of the
A department may have
We also keep track of the direct supervisor
department manager. of each employee
several locations.
a number of
Each department controls
PROJECTs. Each project has a name, number,
and is located at a single location.
Each employee may have a number of
DEPENDENTS.
We store each EMPLOYEE's soclal security
and birth date.
number, address, salary, sex, department but For each dependent, we keep their name,
Each employee works for one
may work on several projects sex, birth date, and relationship to the
employee. 36
for the
Preliminary design of entity types
COMPANY databasee
DEPARTMENT
ManagerSlartDale
Name, Number, [Locatons), Manager,
PROJECT
Name, Number, Locaion, ControlingDepartmet
EMPLOYEE
Salary
Name (FName, Mirit, LName), SSN,Sex, Address,Hours))
(Projec/
BirthDate, Depatment, Supervisor, {WorksOn
DEPDOn
DEPENDENT
BirthDate, Relationship
Employee, DependentName, Sex,
Extended E-R Model IS-A Relationship
EER model includes concepts of
E-R model is sufficient for traditional
subclass and superclass
database applicationsS
Subclass is a subgroup of an entity type
Nontraditional applications (CAD,
multimedia) have more complex E1 IS-A E2 and e E E1 » e ¬ E2
requirements provides typeinheritance
- Can extend traditional E-R diagrams
with semantic data modeling concepts
Supports specialization and
generalization abstractions
40
IS-A Relationship (cont'd) Specialization & Generalization
Specialization
Name
Employee S.I.N. process of taking an entity and creating
several specialized subclasses
d) Generalization
process of taking several related entities
and creating a general superclass
Staff Teaching Assistant We will talk mainly of specialization, but
Faculty most information will also apply to
generalization
Position) Rank (Student #)
Specializationconstraints Predicate-defined subclass
Specializations can be predicate-defined An attribute value is used to determine the
or attribute-defined (otherwise called members of a subclass
user-defined) Not all members ofevery subclass can be
determined by the attribute value
Disjointness constraint- specializaion is In the following example, the Pension Plan
disjoint or overlapping type can be used to determine faculty from
Completeness constraint- specialization staff, but has no effect on students or those
is total or partial who opted out of the pension plan.
Predicate-defined subclass Attribute-defined subclass
Penslon There is one defining attribute for all
Plan Type Employee S.L.N
subclasses
Note: not all Each member of the superclass can be
employees Included| assigned to the appropriate subclass
based on this one attribute
Staff Faculty
Rank
Posltlon
Attribute-defined subclass User-defined subclass
(Joblypee EmployeeF S.I.N. When there is no condition to
automatically determine membership in
Jobtype a subclass, it must be done at the
discretion of the user.
"Staff" Faculty"
| "Student"
Staff Students Faculy
Rank Year Rank
Disjointness constraint
Specifies that an entity can be a Disjoint constraint
member of at most one
There can be
subclass Nane
Employee SLN
no overlap between the
subclasses d
We use the notation
of a d in
symbolize that the subclasses a circle to
are
disjoint Staff
Facuty Teaching Assistant
Position Rank
Student
Overlap
Entities are able to belong
Overlap
to more than
one subcass Jobtype Employeae SLN
Notation is an o inside of a circle Astaff nembr may|
also be a student
Staff Students Cuty
Rank Yenr Rant
Completeness Constraint Partial
May be total or partial
for tota, every entity in the
Jobtype Employee S.L.N.
must belong to a subclass superclass
for partial, entities in the superclass do o)
not need to be part of any subclass
notation for total and partial are the
same as in a regular E-R diagram-
single and double lines Staff Students Faculty
Rank Year Rank
Total Hierarchies and Lattices
(Jobtype Employee SLN. Hierarchies
a tree-like structure where each subclass
belongs to only one superclass
everything we have seen so far is a
hierarchy
Lattices
Staff Students Faculty a
graph-llke structure where a subclass can
belong to more than one
superclass
Rank Year Rank
Lattice
name Person
student
Employee Student
salary
Teaching Assistant
COUrScourse 57