Database Design

IS222

Ms.Abeer Alkulaib
[email protected]
 Chapter 7

Data Modeling Using the

Entity-Relationship (ER)
Model
 Chapter 7 Outline

 Using High-Level Conceptual Data Models for

Database Design
 A Sample Database Application
 Entity Types, Entity Sets, Attributes, and Keys
 Relationship Types, Relationship Sets, Roles,
and Structural Constraints
 Weak Entity Types
 Chapter 7 Outline (cont’d.)

 Refining the ER Design for the COMPANY

Database
 ER Diagrams, Naming Conventions, and
Design Issues
 Example of Other Notation: UML Class
Diagrams
 Relationship Types of Degree Higher than
Two
 Data Modeling
 Before designing database, based on model of
real world objects, you need to choose a
suitable design methodology to:
– Accurate representation of objects in the real
world
– Flexible enough to accommodate future
processing requirements
– Help to design database sequentially,
eliminate trial and error, use the design plan
and drawings to document the database, and
modify database more easily
Overview of DB Design Process
Database Design Life Cycle
 Using High-Level Conceptual Data
Models for Database Design

 Requirements collection and analysis

– Database designers interview prospective database
users to understand and document data
requirements
– Result: data requirements
– Functional requirements of the application
 Using High-Level Conceptual Data
Models (cont’d.)
 Conceptual schema
– Conceptual design
– Description of data requirements
– Includes detailed descriptions of the entity
types, relationships, and constraints
– Transformed from high-level data model into
implementation data model
 Implementation-Free Models
 A good conceptual data
model stays the same
regardless of the type of
database system that is
eventually built—or
implemented—on.
 This is what
“implementation-free”
model means.
 Using High-Level Conceptual Data
Models (cont’d.)
 Logical design or data model mapping
– Result is a database schema in implementation
data model of DBMS
 Physical design phase
– Internal storage structures, file organizations,
indexes, access paths, and physical design
parameters for the database files specified
 Entity-Relationship (ER) Model

 Popular high-level conceptual data model

 list all entities and attributes as well as all
relationships between the entities that are of
importance.
 Provides background information such as entity
descriptions, data types, and constraints.
 ER diagrams
– Diagrammatic notation associated with the ER
model
 Data Modeling Tools
 A number of popular tools that cover
conceptual modeling and mapping into
relational schema design:
– Examples: ErWin, S-Designer (Enterprise Application Suit), ER-
Studio etc.
– SQL developer data modeler for data modeling task:
http://www.oracle.com/technetwork/developer-tools/datamodeler/
overview/index.html
– Online tool: http://www.yworks.com/en/products/yfiles/yed/
 Advantages: Serve as a documentation of application
requirements, easy user interface, mostly graphics editor
support.
Steps to Create ER Model

Slide 1-14
 A Sample Database Application

 COMPANY
– Employees, departments, and projects
– Company is organized into departments
– Department controls a number of projects
– Employee: store each employee’s name, Social
Security number, address, salary, sex (gender),
and birth date
– Keep track of the dependents of each employee
 Entities and Attributes
 Entity
– Thing in real world with independent existence
– (for example, a particular person, car, house, or
employee)
– or it may be an object with a conceptual
existence (for instance, a company, a job, or a
university course
 Entity and Attributes

 Attributes
– Particular properties that describe entity
• Examples: an EMPLOYEE entity may have the attributes
Name, SSN, Address, Sex, BirthDate
 Types of Attributes
• Simple attribute (sometimes called atomic):
– Each entity has a single value for the attribute. For example, the
SSN or Sex of an employee.
• Composite attribute (also called compound):
– The attribute may be composed of several components. For
example:
• Address(Apt#, House#, Street, City, State, ZipCode, Country), or
• Name(FirstName, MiddleName, LastName).
• Composition may form a hierarchy where some components are
themselves composite).
• Multi-valued attribute (also called repeating group or
collection):
– An single entity may have multiple values for that attribute. For
example, Color of a CAR or PreviousDegrees of a STUDENT.
• Denoted as {Color} or {PreviousDegrees}.
 Entity Type
• Entities with the same basic attributes are
grouped (or typed) into an entity type.
– Examples: EMPLOYEE or PROJECT.
– A name /label assigned to objects/items that exists
in real world and that have similar properties

– It could be person, place, event or even concept

– Distinguish from other entity types based on

properties
 Entity Instance, Entity Set
 Entity or Entity Instance: A particular object
belonging to particular entity type
 Entity Set: set of all entities

 Example:
– Entity type: Student
– Entity instance: Fatima Ali
– Entity Set: All students
Entity Types, Entity and Entity Sets

Entity
 Naming convention for entity type
 Singular name recommended

 Organization specific name

– e.g. customer/client is same term choose which
organization is used
 Use capital letter

 Abbreviation can be used, be consistent

 Domain of Attributes
– Data values: A specific entity has a value for each of its
attributes.
• Example: An employee entity may have Name='John Smith',
SSN='123456789', Address ='731, Fondren, Houston, TX',
Sex='M', BirthDate='09-JAN-55

Every attribute has domain

– Set of possible value of an attribute
– The attributes in the entity set get the value
from same domain
 Identify Unique Identifier

 Each entity type may contain few hundreds to

millions of its instances

 How to identify each entity instance uniquely?

 Solution: Key Attribute

Initial Conceptual Design of the
COMPANY Database
Relationship Types, Relationship Sets,
Roles, and Structural Constraints

 Relationship
– When an attribute of one entity type refers to
another entity type
– Represent references as relationships not attributes
 Relationship Types, Sets, and
Instances
 Relationship type R among n entity types E1,
E2, ..., En
– Defines a set of associations among entities from
these entity types
 Relationship instances ri
– Each ri associates n individual entities (e1, e2, ...,
e n)
– Each entity ej in ri is a member of entity set Ej
 Relationship Degree

 Degree of a relationship type

– Number of participating entity types
– Unary, Binary, ternary
 Relationships as attributes
– Think of a binary relationship type in terms of
attributes
 Role Names and Recursive
Relationships
 Role names and recursive relationships
– Role name signifies role that a participating
entity plays in each relationship instance
 Recursive relationships
– Same entity type participates more than once in
a relationship type in different roles
– Must specify role name
 Constraints on Binary Relationship
Types
 Cardinality ratio for a binary relationship
– Specifies maximum number of relationship
instances that entity can participate in
 Participation constraint
– Specifies whether existence of entity depends
on its being related to another entity
– Types: total and partial
 Attributes of Relationship Types

 Attributes of 1:1 or 1:N relationship types can be

migrated to one entity type
 For a 1:N relationship type
– Relationship attribute can be migrated only to entity
type on N-side of relationship
 For M:N relationship types
– Some attributes may be determined by combination
of participating entities
– Must be specified as relationship attributes
 Weak Entity Types

 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
 Refining the ER Design for the
COMPANY Database
 Change attributes that represent relationships
into relationship types
 Determine cardinality ratio and participation
constraint of each relationship type
ER Diagrams, Naming Conventions,
and Design Issues
 Proper Naming of Schema
Constructs
 Choose names that convey meanings attached
to different constructs in schema
 Nouns give rise to entity type names
 Verbs indicate names of relationship types
 Choose binary relationship names to make ER
diagram readable from left to right and from
top to bottom
 Design Choices for ER Conceptual
Design
 Model concept first as an attribute
– Refined into a relationship if attribute is a
reference to another entity type
 Attribute that exists in several entity types may
be elevated to an independent entity type
– Can also be applied in the inverse
 Alternative Notations for ER
Diagrams
 Specify structural constraints on relationships
– Replaces cardinality ratio (1:1, 1:N, M:N) and
single/double line notation for participation
constraints
– Associate a pair of integer numbers (min, max)
with each participation of an entity type E in a
relationship type R, where 0 ≤ min ≤ max and
max ≥ 1
 Relationship Types of Degree
Higher than Two
 Degree of a relationship type
– Number of participating entity types
 Binary
– Relationship type of degree two
 Ternary
– Relationship type of degree three
Choosing between Binary and Ternary
(or Higher-Degree) Relationships
 Some database design tools permit only binary
relationships
– Ternary relationship must be represented as a weak
entity type
– No partial key and three identifying relationships
 Represent ternary relationship as a regular entity
type
– By introducing an artificial or surrogate key
 Ternary Relationships
 The ternary relationship in previous slide Supplies connects
three entities:
– Supplier, Project and Part. Suppliers supply different parts for
different projects. Each part is supplied by a unique supplier, and
is used for a given project within a company. Project uses different
parts from different supplier. Quantity represents the quantity of a
parts supplied by a particular supplier for a particular project.
 Represent the Ternary Relationship with multiple Binary
Relationships:
– Three binary relationship types CAN_SUPPLY, USES, and
SUPPLIES.
– The existence of three relationship instances (s,p),(j,p),and (s,j) in
CAN_SUPPLY,USES,and SUPPLIES,respectively,does not
necessarily imply that an instance (s, j, p) exists in the ternary
relationship SUPPLY.
– Hence, not sufficient to accurately describe the semantic
 Ternary Relationships

 Represent the Ternary Relationship with weak

entity:
– In this case, a ternary relationship such as SUPPLY must be represented as a
weak entity type, with no partial key and with three identifying relationships.
The three participating entity types SUPPLIER, PART, and PROJECT are
together the owner entity types (see Figure 7.17(c)).Hence,an entity in the weak
entity type SUPPLY in Figure 7.17(c) is identified by the combination of its
three owner entities from SUPPLIER, PART, and PROJECT.
 Textbook Reading Reference:
– Relationship Types of Degree Higher than Two (7.9 – e-copy)
 Constraints on Ternary (or Higher-
Degree) Relationships
 Notations for specifying structural constraints
on n-ary relationships
 Should both be used if it is important to fully specify
structural constraints
 How to read?
– The way to read the relationship is to always isolate 2
out of the 3 participating entities and see how they
relate towards the third one. And you need to do this
for all possible pairs.
– More precisely: the 2 entities that you pair each time,
need to be considered as "one of" for each one of them
and the question to answer is "how many" of the third
one can correspond to this pair.
 Ternary Relationship: Exercise
 Convert given ternary relationship into binary, identify
what semantic is missing and use weak entity to fix the
problems:

Employees can use many skills on any one of many

projects, and each project has many employees with
various skills.
 Ternary Relationship: Exercise

 Each engineer working on a particular project has exactly

one manager, but each manager of a project may manage
many engineers, and each manager of an engineer may
manage that engineer on many projects.
 General n-ary Relationships
 Generalizing the ternary form to higher-degree
relationships, an n-ary relationship that describes
some association among n entities is represented
by a single relationship diamond with n
connections, one to each entity
 ER Practice
 Consider a MAIL_ORDER database in which employees take orders
for parts from customers. The data requirements are summarized as
follows:
 The mail order company has employees, each identified by a unique
employee number, first and last name and zip code. Each customer of
the company is identified by a unique customer number, first and last
name, and zip code. Each part sold by the company is identified by a
unique part number, a part name, price, and quantity in stock. Each
order placed by a customer is taken by an employee and is given a
unique order number. Each order contains specified quantities of one
or more parts. Each order has a date of receipt as well as an expected
ship date. The actual ship date is also recorded. Design an ER diagram
for this application.
Slide 1-53
ER- Diagram for Bank database- Home Work
 Questions
 Consider the ER diagram for part of a BANK database.
Each bank can have multiple branches, and each branch
can have multiple accounts and loans.
– a. List the strong (nonweak) entity types in the ER
diagram.
– b. Is there a weak entity type? If so, give its name,
partial key, and identifying relationship.
– c. What constraints do the partial key and the
identifying relationship of the weak entity type specify
in this diagram?
 Question (cont…)

– d. List the names of all relationship types, and specify

the (min, max) constraint on each participation of an
entity type in a relationship type. Justify your choices.
– e. List concisely the user requirements that led to this
ER schema design.
– f. Suppose that every customer must have at least one
account but is restricted to at most two loans at a time,
and that a bank branch cannot have more than 1,000
loans. How does this show up on the (min, max)
constraints?
 MOVIE Database: Home Work
 Consider a MOVIE database in which data is recorded about the movie industry.
The data requirements are summarized as follows:
– Each movie is identified by title and year of release. Each movie has a lenghth
in minutes. Each has a production company and each is classified under one
or more genres (such as horror, action, drama etc). Each movie has one or
more directors and one or more actors appearing in it. Each movie also has a
plot outline. Finally each movie has zero or more quotable qoutes, each of
which is spoken by a particular actor appearing in the movie. Actors are
identified by name and date of birth and appear in one or more movies. Each
actor has a role in the movie. Directors are also identified by name and dateof
birth and direct one or more movies. It is possible for a director to act in a
movie (including one that he or she may also direct). Production companies
are identified by name and each has an address. A production company
produces one or more movies.
 Design an ER diagram for this application.
 Home Library Database: Functionality Based
Models
 Assume we are building an information system for a home library. The
system shall has a single user. The system is supposed to help the library
owner to keep track and search for titles.
 The user should be able to perform the following set of functions:
– Add new book details to the database.
– Delete a selected book from the database.
– Change a selected book details in the database.
 The user should be able to perform the following queries:
– List all books titles in his/her library.
– List all subjects in the library.
– List all books titles for a certain author.
– Show the authors and the subject for a certain title.
– Show the total amount of money spent to buy those books.
– Search for a certain book using its ISBN, Title, Author, Subject, and/or
price.
Home Library Database: Solution

Slide 1-59
 Virtual Library system: Home Work
 In a “Virtual Library” system a database is to be created to enable
users to browse books, search stored books, and to read books
online.
 A user can browse books using categories; each book is classified
into exactly one category. Moreover, a user can browse books using
authors; any book has at least one author. A book is composed of
any number of pages; in which, each one holds some text.
 Search function should allow a user to filter the list of books using
the book title, year of publish, author, and/or category. Moreover, a
user can search the library looking for some text, the system should
return in this case all books with each book page number(s) that
contain the given text.
 Banner System: Home Work
 This system uses a database to maintain the registration of students in courses
within a certain collage.
 The system should allow the following set of operations:-
– Add/Delete/Update student’s profiles.
– Add/Delete/Update Courses.
– Register a given student into a certain section with the regulation of the
collage, student load should not exceed a certain limit of credits, the room
for that section still can accommodate more students, no time conflict will
result from this registration, and that the student already passed the
needed prerequisite for this course.
– Drop a certain section.
– Add/Update/Delete instructors, and assign them to sections.
 The system should allow for the following list of reports:-
– List all student/Instructors/sections/ or courses.
– Print the details for a certain student/instructor/ section/ or course.
– Print the schedule for a certain student or instructor.
– Print the list of students in a certain section.
– Calculate and print the GPA for a certain student.
 Assignments

 In class Practice Exercises:

– 7.29, 7.34, 7.35

 Homework Exercises
– 7.52, 7.53, 7.54, 7.56

 Reading Material
– 7.1-7.8
 Summary

 Basic ER model concepts of entities and their

attributes
– Different types of attributes
– Structural constraints on relationships
 ER diagrams represent E-R schemas