CHAPTER 4

Relational Database

2022 Bereket N.
 FILE Vs DATABASES
• Database systems were developed to
address the problems associated with the
proliferation of master files.
–For years, each time when a new
information need arose, companies
create new files and programs.
–The result: a significant increase in the
number of master files.
 FILE Vs DATABASES
• This proliferation of master files
creates problems:
– Often the same information
Master File 1 was stored in multiple master
Enrollment
Fact A
Fact B
Program files.
Fact C – Made it more difficult to
effectively integrate data and
obtain an organization-wide
Master File 2
Fact A Fin. Aid view of the data.
Fact D Program
– Also, the same information may
Fact F not have been consistent
between files.
Master File 3 e.g. If a student changed his
Grades
Fact A Program phone number, it may have
Fact B been updated in one master file
Fact F
but not another.
 FILE Vs DATABASES

Database
Fact A Fact B
• Database systems
Fact C Fact D
Fact E Fact F
were developed to
address the above
Database stated problems
Management
System

Enrollment Fin. Aid Grades

Program Program Program
 FILE Vs DATABASES
• The database approach
treats data as an
Database organizational resource
Fact A Fact B that should be used by
Fact C Fact D entire organization and
Fact E Fact F managed for the entire
organization,
Database • Data is not just a
Management particular department.
System
• A database management
system (DBMS) serves as
Enrollment Fin. Aid Grades the interface between the
Program Program Program database and the various
application programs.
 FILE Vs DATABASES

Database • The combination of

Fact A Fact B
Fact C Fact D the database, the
Fact E Fact F DBMS, and the
application
Database
Management
programs that
System access the database
is referred to as the
Enrollment
Program
Fin. Aid
Program
Grades
Program
database system.
 FILE Vs DATABASES

Database • The person responsible

Fact A Fact B for the database is the
Fact C Fact D
Fact E Fact F database
administrator.
Database • As technology
Management
System improves, many large
companies are
Enrollment Fin. Aid Grades
developing very large
Program Program Program databases called data
warehouses.
 IMPORTANCE AND ADVANTAGES OF
DATABASE SYSTEMS
• Database technology provides the following
benefits to organizations:
– Data integration - Achieved by combining master
files into larger pools of data accessible by many
programs.
– Data sharing - It’s easier to share data that are
integrated.
– Reporting flexibility - Reports can be revised easily
and generated as needed.
– Minimal data redundancy and inconsistencies -
Because data items are usually stored only once.
 Cont.…
- Central management of data - Data management is
more efficient because the database administrator
is responsible for coordinating, controlling, and
managing data.
- Cross-functional analysis - Relationships can be
explicitly defined and used in the preparation of
management reports.
Example: Relationship between selling costs and
promotional campaigns.
 RELATIONAL DATABASES
• Most new DBMSs are called relational
databases.
• A data model is an abstract representation
of the contents of a database.
• The relational data model represents
everything in the database as being stored
in the forms of tables (also known as,
relations).
10
 Cont…
• In a Relational Table
– Each row contains multiple attributes
describing an instance of the entity.
• This is equivalent to a record
– Each column contains data about one
attribute of an entity.
• This is equivalent to a field

11
 Each row is called a tuple.

STUDENTS
Last First Phone
Student ID Name Name No.
333-33-3333 Simpson Alice 333-3333
111-11-1111 Sanders Ned 444-4444
123-45-6789 Moore Artie 555-5555

12
Each row contains data about a specific
occurrence of the type of entity in the table.

STUDENTS
Last First Phone
Student ID Name Name No.
333-33-3333 Simpson Alice 333-3333
111-11-1111 Sanders Ned 444-4444
123-45-6789 Moore Artie 555-5555

13
Each column in a table contains information
about a specific attribute of the entity.

STUDENTS
Last First Phone
Student ID Name Name No.
333-33-3333 Simpson Alice 333-3333
111-11-1111 Sanders Ned 444-4444
123-45-6789 Moore Artie 555-5555

14
 Attributes
• Primary Key
– An attribute or combination of attributes that
can be used to uniquely identify a specific row
(a record) in a table.
– For example, Student ID in the students’ table
& Item Number in the inventory table.
• Foreign Key
– An attribute in one table that is a primary key
in another table.
• Used to link the two tables
15
 STUDENTS
Last First Phone
Student ID Name Name No.
333-33-3333 Simpson Alice 333-3333
111-11-1111 Sanders Ned 444-4444
123-45-6789 Moore Artie 555-5555

A primary key is the attribute or combination

of attributes that uniquely identifies a specific
row in a table.

16
 STUDENTS
First Advisor
Student ID Last Name Name Phone No. No.
333-33-3333 Simpson Alice 333-3333 1418
111-11-1111 Sanders Ned 444-4444 1418
123-45-6789 Moore Artie 555-5555 1503

ADVISORS
Advisor No. Last Name First Name Office No.
1418 Howard Glen 420
1419 Melton Amy 316
1503 Zhang Xi 202
1506 Radowski J.D. 203

A foreign key is an attribute in one table that

is a primary key in another table.
17
 STUDENTS
First Advisor
Student ID Last Name Name Phone No. No.
333-33-3333 Simpson Alice 333-3333 1418
111-11-1111 Sanders Ned 444-4444 1418
123-45-6789 Moore Artie 555-5555 1503

ADVISORS
Advisor No. Last Name First Name Office No.
1418 Howard Glen 420
1419 Melton Amy 316
1503 Zhang Xi 202
1506 Radowski J.D. 203

Foreign keys are used to link tables

together.
18
 STUDENTS
First Advisor
Student ID Last Name Name Phone No. No.
333-33-3333 Simpson Alice 333-3333 1418
111-11-1111 Sanders Ned 444-4444 1418
123-45-6789 Moore Artie 555-5555 1503

ADVISORS
Advisor No. Last Name First Name Office No.
1418 Howard Glen 420
1419 Melton Amy 316
1503 Zhang Xi 202
1506 Radowski J.D. 203

Other non-key attributes in each table store

important information about the entity.
19
 Basic requirements of a relational
database
• Every column in a row must be single valued.
– In other words, every cell can have one and only
one value.
– In the student table, you couldn’t have an attribute
named “Phone Number” if a student could have
multiple phone numbers.
– There might be an attribute named “local phone
number” and an attribute named “permanent
phone number.”
– You could not have an attribute named “Class” in
the student table, because a student could take
multiple classes.
20
 Cont.….
• The primary key cannot be null.
– The primary key uniquely identifies a specific row
in the table, so it cannot be null, and it must be
unique for every record.
– This rule is referred to as the entity integrity rule.
• A foreign key must correspond to the value of a
primary key in another table.
– This rule is referred to as the referential integrity
rule.
– The rule is necessary because foreign keys are used
to link rows in one table to rows in another table.
• All non-key attributes in a table should describe a
characteristic of the object identified by the primary
key.
21
Data Base Design Process

22
 Cont…
• Accountants may provide the greatest value by
taking responsibility for data modeling — the
process of defining a database to faithfully
represent all aspects of the organization, including
interactions with the external environment.
– Occurs during both system analysis and
conceptual design stage.
– Two important tools to facilitate data modeling:
• Entity-relationship diagramming
• REA data model (Resource, Event, and
Agent)
23
ENTITY-RELATIONSHIP DIAGRAMS

• An entity-relationship (E-R)
diagram is a graphical technique
for portraying (depicting) a
database schema (DB Design in the
picture).
–Shows the various entities being
modeled and the important
relationships among them.
24
 Cont…
• An entity is anything about which the
organization wants to collect and
store information.
– Example: Your university collects and stores
information about students, courses,
enrollment activity, etc.
• In a relational database, separate
tables would be created to store
information about each distinct
entity. 25
 Cont…

• In an E-R diagram, entities are depicted

as rectangles.
• But there are no industry standards for
other aspects of these diagrams.

Enrollment Students

26
 Cont…
• Some data modelers, tools, and authors use
diamonds to depict relationships.

Line
Enrollment Items Students

27
 Cont…
• Others do not use diamonds.

Enrollment Students

28
 Cont…
• Sometimes the attributes associated with each
entity are depicted as named ovals connected to
each rectangle.

Student
Enrollment Student Name
Number Enrollment ID No.
Date
Student
Address
Enrollment
Time

Enrollment Students

29
 Cont…
• Sometimes these attributes are listed in a
separate table.

Enrollment Students

Entity Name Attributes

Enrollment Enrollment No., Enrollment Date, Enrollment
Time
Student Student ID No., Student Name, Student Address

30
 Cont…
• E-R diagrams can be used to represent
the contents of any kind of database.
• Our focus is on databases designed to
support an organization’s business
activities.
• The diagrams we develop depict the
contents of a database and graphically
model those business processes.
31
 Cont..
• In addition to their use in designing
databases, E-R diagrams can be used
to:
– Document and understand existing
databases.
– Reengineer business processes.
• In this chapter, we’ll use E-R
diagrams for designing new
databases and understanding
existing ones.
32
 Cont…
• An important step in designing a
database is deciding which entities
need to be modeled.

• The REA data model is useful for this

decision.

33
 THE REA DATA MODEL
• The REA data model was developed
specifically for use in designing
accounting information systems.
–Focuses on business semantics
underlying an organization’s value
chain activities.
–Provides guidance for:
• Identifying the entities to be
included in a database.
• Structuring the relationships
among the entities.
34
 Cont.…
• REA data models are usually
depicted in the form of E-R
diagrams.

• Therefore, we refer to E-R

diagrams developed with the REA
model as REA diagrams.

35
 Cont…
• Three basic types of entities
– The REA data model is so named because it
classifies entities into three distinct categories:
• Resources that the organization acquires and uses.
Resources are things that have economic value to the
organization.
• Events in which the organization engages. These are the
various business activities about which management wants
to collect information for planning or control purposes.
• Agents participating in these events. Includes people and
organizations who participate in events and about whom
information is desired for planning, control, and evaluation
purposes.
36
 Cont…
• Can you identify the resources in this diagram?
• Can you identify the events in this diagram?
• Can you identify the agents in this diagram?

Inventory Sales Employee

Customer

Cash Receive
Employee
Accounts Cash
37
 Cont…
• Structuring relationships: The basic
REA template

– The REA data model prescribes a basic

pattern for how the three types of entities
(resources, events, and agents) should
relate to one another.

38
 Cont…
• Rule 1: Each event is linked to at least one resource
that it affects.

Resource A Event A

Resource B Event B

39
 Cont…
• Rule 2: Each event is linked to at least one other
event.

Resource A Event A

Resource B Event B

40
 Cont…
• Rule 3: Each event is linked to at least two
agents.

Resource A Event A Agent A

Agent B

Resource B Event B Agent C

41
 Developing an REA Diagram for a
Specific Transaction Cycle
• It consists of three steps:
– STEP ONE: Identify the events about which
management wants to collect information.
– STEP TWO: Identify the resources affected
by the events and the agents who
participated.
– STEP THREE: Determine the cardinalities
between the relationships.
• Let’s walk through an example.
42
 STEP ONE: IDENTIFY RELEVANT
EVENTS
• At a minimum, every REA model must
include the two events that represent the basic
“give-to-get” economic exchange performed in
that transaction cycle.
• The give event reduces one of the
organization’s resources.
• The get event increases a resource.
• There are usually other events that management
is interested in planning, controlling, and
monitoring. These should be included in the
model.
43
 Cont…
• Example: Typical activities in the revenue cycle
include:
– Take customer order: Taking the customer order
does not involve giving or taking a resource. It is a
commitment event.
– Fill customer order: Filling the order involves a
reduction in the company’s inventory. It is a give
event.
– Bill customer: Billing customers involves the
exchange of information with an external party but
does not affect resources.
– Collect collection: Collecting collection results in an
increase in cash. It is a get event.
44
 Cont…
• The give-to-get, then, is:
– Fill customer orders (often referred to as
“sale”);
– Collect cash (often referred to as “cash
receipt”).
• Should “take customer order” and “bill
customer” be included in the model?

45
 Cont…
Take customer order Bill customer
• Taking an order • Printing and mailing invoices
requires that we set does not directly affect an
economic resource.
resources aside.
• It does not represent a
• That information commitment on the part of
should be included in the company to a future
our model. exchange.
• It is an information retrieval
event and should not alter the
contents of the database.
• Does not need to be included
in the model.
46
 Cont…

• In completing the first step of an REA

diagram, the event entities are typically
drawn from top to bottom in the
sequence in which they normally occur.

47
Cont…

Take Order

Sale

Receive
Cash
48
STEP TWO: IDENTIFY RESOURCES
AND AGENTS
• When the relevant events have been
diagrammed in the center of the REA
diagram, the resources that are affected by
those events need to be identified.
• Involves determining:
– The resource(s) reduced by the give event.
– The resource(s) increased by the get
event.
– The resources that are affected by a
commitment event.
49
 Cont…
• In order to identify the resources that are affected by the
relevant events identified above the following questions
may be raised:
– What is the give event?
 Sale
– What resource is reduced by the give event?
 Inventory
– What is the get event?
 Receive Cash
– What resource is increased by the get event?
 Cash
– Is there a commitment event?
 Take Order
– What resource is affected by the commitment event?
 Inventory
50
 Cont…

Take Order

Inventory

Sale

Receive
Cash
Cash

51
 Cont…
• The agents who participate in each
event should also be identified.
–There will always be at least one
internal agent (employee).
–In most cases, there will also be an
external agent (e.g., customer or
supplier) who participates.

52
 Cont…
• In order to identify the agents who participate
in each event identified above the following
questions may be raised:
– What agents are involved in the sale?
 Employee and Customer
– What agents are involved in the receipt of
cash?
 Employee and Customer
– What agents are involved in taking the order?
 Employee and Customer
53
 Cont…

Take Order Employee

Inventory
Customer

Sale

Employee

Receive
Cash Customer
Cash

54
 STEP THREE: DETERMINE
CARDINALITIES OF RELATIONSHIPS
• The final step in an REA diagram for a transaction
cycle is to add information about the relationship
cardinalities.
• A cardinality describes the nature of the relationship
between two entities.
– It indicates how many instances of one entity can be
linked to a specific instance of another entity.
– For example, the cardinality between the event Sales
and the agent Customer answers the question:
• For each sale a company makes, how many
customers are associated with that sale?
55
 Cont…
• Unfortunately, there is no universal
standard for diagramming
cardinalities.
• In this chapter, we adopt the graphical
“crow’s feet” notation style because:
– It is becoming increasingly popular.
– It is used by many software design tools.

56
 Cont…
• 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:

57
 Cont…
• There is typically a minimum and
maximum cardinality for each entity
participating in a relationship.
– The minimum cardinality can be either
zero or one.
– The maximum cardinality can be either
one or N (many).

58
 Cont…

• Three types of relationships

–Three types of relationships are
possible between entities.
–Relationships depend on the
maximum cardinality on each side of
a relationship.

59
 Cont…
• A one-to-one relationship (1:1) exists when the
maximum cardinality for each entity in the
relationship is one.

Take Order
• Both maximums are
one, so this is a one-to-
one relationship.

Sale

60
 Cont…
• A one-to-many (1:N) relationship exists when the
maximum cardinality on one side is one and the
maximum on the other side is many.

Sale Customer

• The maximum number of customers that can be

involved in each sale is one.
• The maximum number of sales that can be associated
with any individual customer is many.
• This is a one-to-many (1:N) relationship.
61
 Cont…
• A many-to-many (M:N) relationship exists when
the maximum on both sides is many.

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.
62
 Cont…
• 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.
• Let’s look at some examples.

63
 Cont.…
• Customers pay for each
Sale sale with a maximum of
one payment (typical
for retail stores).
• Each cash receipt from
Cash Receipt
a customer relates to
one (and only one) sale.
• The relationship
between sales and cash
receipts is 1:1.
64
 Cont.…

• Customers pay for each

Sale sale with a maximum of
many payments
(installments).
• Each cash receipt from a
customer relates to one
Cash Receipt
(and only one) sale.
• The relationship
between sales and cash
receipts is 1:N.
65
 Cont.…
• Customers make only one
Sale
payment for a sale.
• Each cash receipt from a
customer can relate to
multiple sales (e.g., they
Cash Receipt pay for all sales that
month in one payment).
• The relationship between
sales and cash receipts is
1:N.
66
 Cont.…
• Customers may make
Sale
multiple payments for a
particular sale.
• A cash receipt from a
customer may relate to
Cash Receipt more than one sale.
• The relationship
between sales and cash
receipts is M:N.

67
 Cont.…

• In other words, the choice of

cardinalities is not arbitrary.
• It reflects facts about the organization
that are obtained during the
requirements definition stage of the
database design process.

68
The End!

69