csci 465 lse
Post on 10-Oct-2015
38 Views
Preview:
DESCRIPTION
TRANSCRIPT
-
5/20/2018 CSCI 465 lse
1/49
Copyright 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley
Chapter 7
Data ModelingUsing the
Entity-Relationship
(ER) Model
-
5/20/2018 CSCI 465 lse
2/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Lecture Outline
Using High-Level Conceptual Data Modelsfor Database Design
Entity Types, Attributes, and Keys
Relationship Types, Roles, and StructuralConstraints
Weak Entity Types
-
5/20/2018 CSCI 465 lse
3/49
Copyright 2011 Ramez Elmasri and Shamkant NavatheSlide 3- 3
Overview of Database Design Process
Two main activities: Database design
Applications design
Focus in this chapter on database design To design the conceptual schema for a database
application
Applications design focuses on the programsand interfaces that access the database
Generally considered part of softwareengineering
-
5/20/2018 CSCI 465 lse
4/49
Copyright 2011 Ramez Elmasri and Shamkant NavatheSlide 3- 4
Overview of Database Design Process
-
5/20/2018 CSCI 465 lse
5/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Steps in Database Design
Requirements Analysis user needs; what must database do?
Conceptual Design
high level descr (often done with ER model) Logical Design
translate ER into DBMS data model
Schema Refinement consistency, normalization
Physical Design -indexes, disk layout
Security Design -who accesses what,and how
-
5/20/2018 CSCI 465 lse
6/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Data Modeling Using theEntity-Relationship (ER) Model
Entity-Relationship (ER) model
Popular high-level conceptual data model
ER diagrams Diagrammatic notation associated with the ER
model
-
5/20/2018 CSCI 465 lse
7/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Using High-Level ConceptualData Models for Database
Design Requirements collection and analysis
Database designers interview prospective
database users to understand and documentdata requirements
Result: data requirements
Functional requirements of the application
-
5/20/2018 CSCI 465 lse
8/49Copyright 2011 Ramez Elmasri and Shamkant Navathe
Using High-Level ConceptualData Models (contd.)
Conceptual schema
Description of data requirements
Includes detailed descriptions of the entity
types, relationships, and constraints
Transformed from high-level data model intoimplementation data model
-
5/20/2018 CSCI 465 lse
9/49Copyright 2011 Ramez Elmasri and Shamkant Navathe
Using High-Level ConceptualData Models (contd.)
Logical design or data model mapping
Result is a database schema in implementationdata model of DBMS
Physical design phase
Internal storage structures, file organizations,indexes, access paths, and physical design
parameters for the database files specified
-
5/20/2018 CSCI 465 lse
10/49Copyright 2011 Ramez Elmasri and Shamkant NavatheSlide 3- 10
Example COMPANY Database
We need to create a database schemadesign based on the following (simplified)requirementsof the COMPANY Database: The company is organized into DEPARTMENTs.
Each department has a name, number and anemployee who managesthe department. Wekeep track of the start date of the departmentmanager. A department may have several
locations. Each department controlsa number of
PROJECTs. Each project has a unique name,unique number and is located at a single
location.
-
5/20/2018 CSCI 465 lse
11/49Copyright 2011 Ramez Elmasri and Shamkant NavatheSlide 3- 11
Example COMPANY Database(Contd.)
We store each EMPLOYEEs social securitynumber, address, salary, sex, and birthdate.
Each employee works forone department but maywork onseveral projects.
We keep track of the number of hours per week thatan employee currently works on each project.
We also keep track of the direct supervisorof eachemployee.
Each employee may havea number ofDEPENDENTs.
For each dependent, we keep track of their name, sex,birthdate, and relationship to the employee.
-
5/20/2018 CSCI 465 lse
12/49Copyright 2011 Ramez Elmasri and Shamkant Navathe
Using High-Level ConceptualData Models (contd.)
What are the entitiesand relationshipsinthe enterprise?
What information about these entities andrelationships should we store in thedatabase?
What are the integrity constraints or
business rules that hold?
A database `schema in the ER Model can
be represented pictorially (ER diagrams).
-
5/20/2018 CSCI 465 lse
13/49Copyright 2011 Ramez Elmasri and Shamkant Navathe
Entity Types, Entity Sets,Attributes, and Keys
ER model describes data as:
Entities
Relationships
Attributes
-
5/20/2018 CSCI 465 lse
14/49Copyright 2011 Ramez Elmasri and Shamkant NavatheSlide 3- 14
ER Model Concepts
Entities and Attributes Entities are specific objects or things in the mini-world that
are represented in the database. For example the EMPLOYEE John Smith, the Research
DEPARTMENT, the ProductX PROJECT
Attributes are properties used to describe an entity. For example an EMPLOYEE entity may have the attributes
Name, SSN, Address, Sex, BirthDate
A specific entity will have a value for each of its attributes. For example a specific employee entity may have Name='John
Smith', SSN='123456789', Address ='731, Fondren, Houston,TX', Sex='M', BirthDate='09-JAN-55
Each attribute has a value set(or data type) associated withite.g. integer, string, subrange, enumerated type,
-
5/20/2018 CSCI 465 lse
15/49Copyright 2011 Ramez Elmasri and Shamkant Navathe
ER Diagrams, NamingConventions, and Design Issues
-
5/20/2018 CSCI 465 lse
16/49Copyright 2011 Ramez Elmasri and Shamkant Navathe
Entities and Attributes
Entity
Entity: Real-world object, distinguishable fromother objects. An entity is described using a set
of attributes. Entity Set: A collection of similar entities. E.g.,
all employees.
All entities in an entity set have the same set of
attributes. (Until we consider hierarchies,anyway!)
Each entity set has a key (underlined).
-
5/20/2018 CSCI 465 lse
17/49Copyright 2011 Ramez Elmasri and Shamkant Navathe
Entities and Attributes (contd.)
Employees
ssnname
salary
ssn name salary
219654231 Tom 50,000 $
318689741 John 60,000 $
354856421 Mary 55,000 $
Employees
-
5/20/2018 CSCI 465 lse
18/49Copyright 2011 Ramez Elmasri and Shamkant Navathe
Entities and Attributes (contd.)
Attributes
Particular properties that describe entity
Each attribute has a domain (the set of
permitted values for each attribute ).
Types of attributes:
Compositeversus simple(atomic) attributes
Single-valuedversus multivaluedattributes Complexattributes
Storedversus derivedattributes
Descriptive attributes
-
5/20/2018 CSCI 465 lse
19/49Copyright 2011 Ramez Elmasri and Shamkant Navathe
Entities and Attributes (contd.)
-
5/20/2018 CSCI 465 lse
20/49Copyright 2011 Ramez Elmasri and Shamkant Navathe
STUDENT
Name
Last
Birth-date
Month DayYear
First
Entities and Attributes (contd.)
COMPOSITE ATTRIBUTE
-
5/20/2018 CSCI 465 lse
21/49Copyright 2011 Ramez Elmasri and Shamkant Navathe
Entities and Attributes (contd.)
MULTIVALED ATTRIBUTE
Department
Location NameNumber
-
5/20/2018 CSCI 465 lse
22/49Copyright 2011 Ramez Elmasri and Shamkant Navathe
Entities and Attributes (contd.)
Employee
Address NameNumber
St_NumSt_name City Zip
COMPLEX ATTRIBUTE
-
5/20/2018 CSCI 465 lse
23/49Copyright 2011 Ramez Elmasri and Shamkant Navathe
Entities and Attributes (contd.)
DERIVED ATTRIBUTE
STUDENT
Name Birth-dateAge
-
5/20/2018 CSCI 465 lse
24/49Copyright 2011 Ramez Elmasri and Shamkant Navathe
Entities and Attributes (contd.)
address
name
STUDENT
ssn
register
datecname
# of creditscno
Course
DESCRIPTIVE ATTRIBUTE
-
5/20/2018 CSCI 465 lse
25/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Entities and Attributes (contd.)
-
5/20/2018 CSCI 465 lse
26/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Entity Types, Entity Sets, Keys,and Value Sets
Entity type
Collection (or set) of entities that have thesame attributes
-
5/20/2018 CSCI 465 lse
27/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Entity Types, Entity Sets, Keys,and Value Sets (contd.)
Key oruniqueness constraint
Attributes whose values are distinct for eachindividual entity in entity set
Key attribute Uniqueness property must hold for every entity set
of the entity type
Value sets (or domain of values) Specifies set of values that may be assigned to
that attribute for each individual entity
-
5/20/2018 CSCI 465 lse
28/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Entity Types, Entity Sets, Keys,and Value Sets (contd.)
A super keyof an entity set is a set of oneor more attributes whose values uniquelydetermine each entity.
A candidate keyof an entity set is aminimal super key
IDis candidate key of student
course_idis candidate key of course Although several candidate keys may exist,
one of the candidate keys is selected to be
the primary key.
-
5/20/2018 CSCI 465 lse
29/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Entity Types, Entity Sets, Keys,and Value Sets (contd.)
Composite key is a key that consists of 2attributes that uniquely identify an entityoccurrence
Foreign key is a column or a combinationof columns that is used to establish andenforce a link between two tables.
-
5/20/2018 CSCI 465 lse
30/49
Copyright 2011 Ramez Elmasri and Shamkant NavatheSlide 3- 30
Initial Design of Entity Types forthe COMPANYDatabase Schema Based on the requirements, we can identify four
initial entity types in the COMPANY database:
DEPARTMENT
PROJECT EMPLOYEE
DEPENDENT
Their initial design is shown on the following slide The initial attributes shown are derived from the
requirements description
-
5/20/2018 CSCI 465 lse
31/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Initial Conceptual Design of theCOMPANY Database
-
5/20/2018 CSCI 465 lse
32/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Relationship Types, Relationship
Sets, and Roles Relationship
When an attribute of one entity type refers to
another entity type Represent references as relationships not
attributes
-
5/20/2018 CSCI 465 lse
33/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Relationship Types, Sets, andInstances(contd.)
Relationship Association among two ormore entities. E.g., Tom works inPharmacy department.
relationships can have their own attributes.
Relationship Set Collection of similarrelationships.
An n-ary relationship set Rrelates nentity setsE1... En ; each relationship in Rinvolvesentities e1E1, ..., enEn
-
5/20/2018 CSCI 465 lse
34/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Relationship Types, Sets, andInstances(contd.)
salary
name
Employees
ssn
Works_In
sincedname
budgetd_id
Departments
-
5/20/2018 CSCI 465 lse
35/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Relationship Types, Sets, andInstances(contd.)
Same entity set can participate in differentrelationship sets, or in different roles in
the same set.
since
Manages
dname
budgetdid
Departments
since
Works_In
salary
name
ssn
Employees
-
5/20/2018 CSCI 465 lse
36/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Relationship Degree
Degreeof a relationship type
Number of participating entity types
Binary, ternary
Binary relationship
salary
name
Employees
ssn
Works_In
dname
budgetd_id
Departments
-
5/20/2018 CSCI 465 lse
37/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Relationship Degree
a ternary relation Contracts relates entitysets Parts, Departments andSuppliers, andhas descriptive attribute qty. No
combination of binary relationships is anadequate substitute.
Suppliers
qty
DepartmentsContractParts
-
5/20/2018 CSCI 465 lse
38/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Relationship Degree
S can-supply P, D needs P, and D deals-with S does not imply that D has agreed to buy Pfrom S.
How do we record qty?
Suppliers
qty
DepartmentsContractParts
Suppliers
Departments
deals-with
Parts
can-supply
VS.
needs
38
-
5/20/2018 CSCI 465 lse
39/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
-
5/20/2018 CSCI 465 lse
40/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Role Names and RecursiveRelationships
Role names and recursive relationships
Role name signifies role that a participatingentity plays in each relationship instance
Recursiverelationships Same entity type participates more than once
in a relationship type in different roles
Must specify role name
-
5/20/2018 CSCI 465 lse
41/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Recursive Relationships Example
COURSE
Is_Pre
is prerequisite-to
Has-prerequisites
Is-Pre COURSE
COURSE
C t i t Bi
-
5/20/2018 CSCI 465 lse
42/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Constraints on BinaryRelationship Types
Cardinality ratio for a binary relationship
Specifies maximum number of relationshipinstances that entity can participate in
Participation constraint Specifies whether existence of entity depends
on its being related to another entity
Types: totaland partial
-
5/20/2018 CSCI 465 lse
43/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Cardinality Examples
1:1 relationshipA person has only one IDbook and an ID book belongs to only 1person
N:1 relationshipA school can have manystudents, but a student attends only 1
school
M:N relationshipA person can own manyhouses and a house can have multipleowners
C t i t Bi
-
5/20/2018 CSCI 465 lse
44/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Constraints on BinaryRelationship Types
Entity set E has total participation inrelationship set R: every entity in Eparticipates in at least one relationship in R
E.g. an ID book must have an owner
E.g. a house must have an owner
C t i t Bi
-
5/20/2018 CSCI 465 lse
45/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Constraints on BinaryRelationship Types
Partial participation: only some entities in Eparticipate in relationships in R
E.g. not every person owns a house
We will use a double-line to show total
participation and a single-line to showpartial participation (NB there are manystyles of ER notation, but the ideas are thesame).
-
5/20/2018 CSCI 465 lse
46/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Weak Entity Types
Can be identified uniquely only byconsidering the primary key of another(owner) entity.
Do not have key attributes of their own Identified by being related to specific entities
from another entity type
Identifying relationship Relates a weak entity type to its owner
Always has a total participation constraint
-
5/20/2018 CSCI 465 lse
47/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Weak Entity Types(contd.)
Owner entity set and weak entity set mustparticipate in a one-to-many relationship set(one owner, many weak entities).
Weak entity set must have total participation in
this identifying relationship set.
salary
name
agedname
DependentsEmployees
ssn
Policy
cost
Weak entities have only a partial key (dashed underline)
1 N
-
5/20/2018 CSCI 465 lse
48/49
Copyright 2011 Ramez Elmasri and Shamkant Navathe
Weak Entity Types(contd.)
# of credit
cname
namesnumber
SectionCourse
cnumber
has
Weak entities have only a partial key (dashed underline)
1 N
-
5/20/2018 CSCI 465 lse
49/49
Summary
Basic ER model concepts of entities andtheir attributes
Different types of attributes
Structural constraints on relationships
ER diagrams represent E-R schemas
top related