Transcript Database Design - Department of Accounting and Information

Database Design
Chapters 17 and 18
Copyright © 2015 Pearson Education, Inc.
17-1
Learning Objectives
• Learn the steps to design and implement a
database system.
• Learn how is an entity-relationship (E-R)
diagram is drawn.
• Learn how E-R diagrams are read and what they
reveal about the business activities and policies
of the organization being modeled
Copyright © 2015 Pearson Education, Inc.
17-2
Database Design Process
Copyright © 2015 Pearson Education, Inc.
17-3
Data Modeling
• Process of defining a database so that it
faithfully represents all aspects of the
organization, including its interactions with the
external environment.
▫ Entity-relationship (E-R) diagrams
▫ REA data model
Copyright © 2015 Pearson Education, Inc.
17-4
Review of Database Terms
• entity, relation, table
• attribute, field, column
• tuple, record, row
▫ instance of an entity
•
•
•
•
primary key
foreign key
relationship
cardinality, modality
Copyright © 2015 Pearson Education, Inc.
ENTITY-RELATIONSHIP DIAGRAMS
• In an E-R diagram, entities are depicted as rectangles.
• But there are no industry standards for other aspects of
these diagrams.
Enrollment
Copyright © 2015 Pearson Education, Inc.
Student
ENTITY-RELATIONSHIP DIAGRAMS
• Sometimes attributes are listed in the same symbol as
the entity itself.
Enrollment
Enrollment No. (pk)
Enrollment Date
Enrollment Time
Student
Student ID No. (pk)
Student Name
Student Address
• This is the approach we will be using in this class.
However many of the examples in these slides do not
show attributes since the focus is on relationship issues.
A final ERD should show all attributes of each entity.
Copyright © 2015 Pearson Education, Inc.
RELATIONSHIPS
A relationship is a natural business association
that exists between one or more entities. Entities
are connected by a line
Should use active verbs to describe the
relationship
One verb can describe the relationship in both
directions
Places
Customer
Copyright © 2015 Pearson Education, Inc.
Order
CARDINALITY
• Cardinality describes the maximum number
of instances of one entity that may be related to a
specific instance of another entity.
▫ For example, the cardinalities between Order and
Customer answer the questions:
 How many customers can be associated with a single
order that a company receives?
 How many orders can be associated with a single
customer of the company?
Copyright © 2015 Pearson Education, Inc.
MODALITY
• Modality describes the minimum number of
instances of one entity that can be related to a
specific instance of another entity.
▫ For example, the cardinality between Order and
Customer answers the questions:
 Does each order have to be associated with a
customer?
 Does each customer have to be associated with an
order?
Copyright © 2015 Pearson Education, Inc.
CARDINALITY and MODALITY
• Using the crow’s feet notation:
▫ The symbol for zero is a circle: O
▫ The symbol for one is a single stroke: |
▫ The symbol for many is the crow’s foot:
Copyright © 2015 Pearson Education, Inc.
CARDINALITY and MODALITY
is placed by
Order
Customer
• Because all relationships are
bidirectional, modality and
cardinality must be defined in both
directions for every relationship.
Copyright © 2015 Pearson Education, Inc.
CARDINALITY and MODALITY
is placed by
Order
Customer
• The modality symbol next to
customer is the symbol for one.
• This symbol means that for every
occurrence of an order, there must
be a minimum of one customer
involved.
Copyright © 2015 Pearson Education, Inc.
CARDINALITY and MODALITY
is placed by
Order
Customer
• The cardinality symbol next to
customer is the symbol for one.
• This symbol means that for every
occurrence of an order, there can be
no more than one customer
involved.
Copyright © 2015 Pearson Education, Inc.
CARDINALITY and MODALITY
Order
is placed by
Customer
• The modality symbol next to order is the symbol for
zero.
• This symbol means that for every customer in the
database, there must be a minimum of zero orders. This
minimum of zero allows the company to add a customer
to its database before any orders have been placed by
that customer, i.e., a prospective customer can be
Copyright © 2015 Pearson Education, Inc.
included.
CARDINALITY and MODALITY
Order
is placed by
Customer
• The cardinality symbol next to order is the symbol for
many.
• This symbol means that for every customer in the
database, there can be many orders involved.
Obviously, a company can allow multiple orders from an
individual customer.
Copyright © 2015 Pearson Education, Inc.
CARDINALITY and MODALITY
Customer
Places
Order
Claims
Dependent
Employee
Teaches
Class
Instructor
Claims
Employee
Copyright © 2015 Pearson Education, Inc.
Dependent
CARDINALITY and MODALITY
• It is not a “one size fits all” world for
relationships and cardinalities. The cardinalities
between two entities can vary based on how the
particular company does business.
• It reflects facts about the organization that are
obtained during the requirements definition
stage of the database design process.
Copyright © 2015 Pearson Education, Inc.
CARDINALITY and MODALITY
Sale
Cash
Receipt
Copyright © 2015 Pearson Education, Inc.
• Customers pay
for each sale with
a maximum of
one payment
(typical for retail
stores).
• Each cash receipt
from a customer
relates to one
(and only one)
sale.
CARDINALITY and MODALITY
Sale
Cash
Receipt
Copyright © 2015 Pearson Education, Inc.
• Customers pay
for each sale with
a maximum of
many payments
(installments).
• Each cash receipt
from a customer
relates to one
(and only one)
sale.
CARDINALITY and MODALITY
Sale
Cash
Receipt
Copyright © 2015 Pearson Education, Inc.
• Customers make
only one payment
for a sale.
• Each cash receipt
from a customer
can relate to
multiple sales
(e.g., they pay for
all sales that
month in one
payment).
CARDINALITY and MODALITY
Sale
Cash
Receipt
Copyright © 2015 Pearson Education, Inc.
• Customers may
make multiple
payments for a
particular sale.
• A cash receipt
from a customer
may relate to
more than one
sale.
CARDINALITY and MODALITY
• Three Types of Relationships
▫ Three types of relationships are possible between
entities.
▫ Relationships depend on the cardinality on each side
of a relationship.
 A one-to-one relationship (1:1) exists when the maximum
cardinality for each entity in the relationship is 1.
 A one-to-many (1:N) relationship exists when the maximum
cardinality on one side is 1 and the maximum on the other side
is many.
 A many-to-many (M:N) relationship exists when the maximum
on both sides is many.
Copyright © 2015 Pearson Education, Inc.
CARDINALITY and MODALITY
College
• Both cardinalities
are one, so this is
a one-to-one
relationship.
Dean
Copyright © 2015 Pearson Education, Inc.
CARDINALITY and MODALITY
Order
Customer
• The maximum number of customers that can be
involved in each order is one.
• The maximum number of orders that can be associated
with any individual customer is many.
• This is a one-to-many (1:N) relationship.
Copyright © 2015 Pearson Education, Inc.
CARDINALITY and MODALITY
Inventory
Sale
• The maximum number of inventory items that can be
sold in one sale is many.
• The maximum number of sales that can occur for a
particular inventory item is many.
• This is a many-to-many (M:N) relationship.
Copyright © 2015 Pearson Education, Inc.
Implement One-to-One Relationships
• One-to-One Relationships:
• In a relational database, one-to-one
relationships between entities can be
implemented by merging the two tables/entities
together.
Copyright © 2015 Pearson Education, Inc.
Implement One-to-Many Relationships
• One-to-many relationships between entities can
be implemented by placing the primary key of
the entity that can occur only once as a foreign
key in the entity that can occur many times.
▫ EXAMPLE: The primary key for salesperson (which
can occur only once per sale) is a foreign key in the
sale table/entity (which can occur many times for a
particular salesperson).
▫ If you tried to do the opposite, you would not have
single-valued entries in the tables
Copyright © 2015 Pearson Education, Inc.
Implement One-to-Many Relationships
Customer
Order
Primary Key
Primary Key
"Customer number" [PK1]
Non-Key Attributes
"Order Number" [PK1]
Places
"Customer first name"
"Customer last name"
"Order Date"
"Order Status"
Customer
Order
Primary Key
Primary Key
"Customer number" [PK1]
Non-Key Attributes
"Customer first name"
"Customer last name"
Copyright © 2015 Pearson Education, Inc.
Non-Key Attributes
"Order Number" [PK1]
Places
Non-Key Attributes
"Order Date"
"Order Status"
"Customer number" [FK]
Implement Many-to-Many Relationships
• Many-to-Many Relationships:
• In a relational database, many-to-many
relationships between entities can be
implemented by creating a new entity that links
the other two entities together. In most cases,
the primary key of the new entity consists of the
concatenated primary key attributes of the
entities that it is relating together.
• The table/entity names for M:N relationships
should be hyphenated concatenations of the
entities involved in the relationship.
Copyright © 2015 Pearson Education, Inc.
Implement Many-to-Many Relationships
Student
Course
registers
for
Student ID (pk)
Student first name
Student last name
…
Student
participates
in
Student ID (pk)
Student first name
Student last name
…
Copyright © 2015 Pearson Education, Inc.
Student-Course
Student ID (pk)(fk)
Course CRN (pk)(fk)
Course CRN (pk)
Course name
Course department
…
Is for a
Course
Course CRN (pk)
Course name
Course department
…
SUMMARY:
DESIGNING AN E-R Diagram
• Create an entity for each distinct thing (people, places,
events, etc.) that the organization wants to track.
• Identify a primary key for each entity
• Assign non-key attributes to appropriate entities
• Using knowledge of business processes identify
relationships between entities and assign modality and
cardinality to each relationship.
• Merge one-to-one relationships together and use foreign
keys to implement one-to-many relationships.
• Draw new entities to handle with many-to-many
relationships and add appropriate attributes,
cardinalities, modalities, etc.
• Create tables (one for each entity on the ERD) for sample
data and check for rule violations or anomalies
Copyright © 2015 Pearson Education, Inc.
IMPLEMENTING A RELATIONAL DATABASE
• When all attributes have been assigned, the
basic requirements for a well-structured
relational database can be used as a final
accuracy check:
▫ Every table has a primary key.
▫ Other attributes in the table are either a fact that
describes the entity or a foreign key used to link
tables.
▫ Every attribute in every table is single-valued.
Copyright © 2015 Pearson Education, Inc.