Project Stage 2
Kim’s Flowers Expenditure Cycle
The second stage of the project requires students to perform step 8 in the event-oriented
modeling (as described in chapters 8 and 9).
1. Examine the standard solution for Kim Flowers.
2. In step 8 you need to convert your REA diagram into tables. Make sure that you
follow the procedure described in chapter 9.
3. Implement the rules in chapter 9 for determining whether a table conforms to first,
second, or third normal form.
4. Normalize tables that are not is 3rd normal form.
5. Make sure that all your tables are normalized (each table should be in 3rd normal
form).
6. You should submit a list of all your tables, the attributes in each table and primary
and foreign keys for each table.
7. You must show all the steps in the normalization process.
Make sure that you send me your design in Word format via Email. The subject of your
email must follow the required convention. The document must be named using the same
convention. Your project may not be received and processed by my email account if you
do not follow the naming convention. The email must be send to my MSU email from
your MSU ones.
1
2
3
Logical Design for Database
Systems
Chapter 8
Learning Objectives
The two aspects of logical design are logical data
modeling and logical process modeling.
We will look at logical data modeling using the entityrelationship (ER) approach and on Logical process
modeling using data flow diagrams (DFD)
We will use business events as the foundation for our
modeling
From Analysis to Design
Systems analysis usually results in a narrative
description of a business process scenario
Business scenarios: managers supervise
employees, customers place sales orders
which consist of one or more items ordered,
purchase orders are placed with vendors
Logical modeling involves capturing the
“semantics,” or meaning, of the business
process scenario in a pictorial
representation
Fundamentals of ER modeling
Entities are represented using rectangles
Relationships between entities are represented using diamonds
A nything tha t the org a niz a tion would like to m a inta in
inform a tion a bout is considered a n entity
Entities include customers, employees, orders, payments, cash, …
Relationships are nothing but associations between entities
ER modeling continued…
◼ An expanded version of the initial ER diagram, showing
managers supervising professionals who have skills
ER modeling continued…
◼ The initial model is expanded even further, to show the jobs that
professionals are assigned to, clients for whom the jobs are performed,
and the equipment needed on jobs
Extended Entity-Relationship (EER)
model
The basic ER model shows only entities and the
relationships between entities
It does not show the type of relationship in terms of
the cardinality of each relationship, whether
relationships are mandatory or optional, and the
attributes of each entity.
Extended entity-relationship (EER) models show all of
the above characteristics (cardinality, optionality, and
attributes).
Cardinality
The purpose of the "crow's foot" is to
show that many professionals are
supervised by a manager.
Optionality
An entity's participation in a relationship can either be mandatory
or optional
The "optional" participation of an entity is indicated by an "O" on
the relationship line leading to the entity, and the "mandatory"
participation of an entity is indicated by a "|" (vertical line) on the
relationship line leading to the entity
Attributes
EER Conventions & Interpretation
Figure
Interpretation
Minimum...maximum
cardinality for entity "B"
Every A is associated
with exactly one B, every
B is associated with
exactly one A
1...1
Every A may be
associated with one B,
every B is associated
with exactly one A
0...1
Every A is associated
with at least one B and
may be associated with
many Bs, every B is
associated with exactly
one A
1...N
Every A may be
associated with either
none or many Bs, every
B is associated with
exactly one A
0...N
REA -Definitions
Resources: economic resources are the assets of the
organization. They are defined as object that are both
scarce and under the control of the organization
Events are phenomena that affect changes in resources
Agents are individuals that participate in an economic
event
Event-oriented Modeling of Business
Processes, using the REA framework
McCarthy’s REA framework provides a mechanism for
focusing on significant business events, the resources
increased or decreased by events, and the agents who are
involved with the events
Significant business events are those activities and
endeavors that management wants to plan, execute, and
evaluate
Events are either economic or non-economic
Agents are either internal (employees) or external
(customers, suppliers, stockholders, etc.)
REA modeling continued..
Economic events always either increase or decrease a
resource – it is an “exchange event” between the
organization and an external agent
Every increase (decrease) in a resource is paired with a
corresponding decrease (increase) in another resource
-- this is referred to as a dua lity or “give-take”
relationship
Non-economic events are those that do not directly
involve an increase or decrease in resources, but have
important implications for future economic events
Economic
Resource
Economic
Event
Economic
Agent
duality
Source:
W. E. McCarthy “The REA Accounting Model: A Generalized Framework for Accounting
Systems in a Shared Data Environment,” The Accounting Review, July 1982, pp 554-78.
W.E. McCarthy “The REA Modeling Approach to Teaching Accounting Information Systems,”
Issues in Accounting Education, November 2003, pp. 427-41. (source of following slides)
Cookie-Monster (the customer) and Elmo (the
entrepreneur) meet in the (real or virtual)
marketplace, thus setting the stage for an
Economic Exchange
Cookie-Monster (the customer) and Elmo
(the entrepreneur) engage in a SALE
(transfer of Cookie Inventory)
Economic Resource
inside
participation
Economic Agent
Economic Event
stock-flow
Economic Agent
outside
participation
Give
Take
duality
outside
participation
Economic Agent
Economic Event
stock-flow
Economic Resource
inside
participation
Economic Agent
REA model of cookie sale from
entrepreneur’s (ELMO) perspective
Cookie-Monster (the customer) and
Elmo (the entrepreneur) engage in a
PAYMENT (transfer of Cash)
Economic Resource
inside
participation
Economic Agent
Economic Event
stock-flow
Economic Agent
outside
participation
Give
Take
duality
outside
participation
Economic Agent
Economic Event
stock-flow
Economic Resource
inside
participation
Economic Agent
REA model of cookie sale from
entrepreneur’s (ELMO) perspective
Business processes vs. information
processes
The economic (exchange) event itself is a business process,
but recording information pertaining to the event is an
information process
EXAMPLE: A sales is a business process, but recording
information about the sales order is an information
process also generating an invoice is information process
Business process modeling is done using EER diagrams
Information process modeling is done using DFDs
Event-oriented modeling: a ninestep process
1.
Identify significant events (what is occurring? and draw them sequentially.
2.
For each event identified in step 1, identify any resources that are used by,
created by, or otherwise involved with the event. There could be multiple
resources associated with a single event. Resources are drawn to the left of
events using the entity symbol, with a diamond connecting a resource to all
event entities to which it has some relationship
3.
For each event identified in step 1, identify any agents that perform the
event or are otherwise affected by the event. Agents could be within the
organization or outside the organization. Economic events usually have both
internal and external agents associated with the event. Agents are drawn to
the right of events again using the entity symbol, with a diamond connecting
an agent to all event entities
Event-oriented modeling: a ninestep process
4.
5.
Identify relationships: Resources and
agents are typically related to one
another through a significant event.
Additionally, it is possible that two
resources or two agents have a direct
relationship not stemming from any
event.
Specify the optionality and cardinality of
relationships.
Event-oriented modeling: a ninestep process
6.
7.
8.
9.
Identify the attributes of events, resources,
and agents
Identify the information processes
Design the structure of the data repository
Implement the design
Results of applying the above nine-step process is a
tentative EER diagram and corresponding DFD
– to be refined further
student registration system - Example
Steps 1-3 Step 4
Resource
Courses
Step 5
Events
Offered
On
Course
Offerings
Step 6
Agents
By
A ssig ned
to
Conta in
Departments
E m ploy
Instructors
M a jor in
Registration
By
Students
Data Flow Diagrams
REA diagrams shows the data structure
aspect, rather than what and how
modifies the entities
DFDs (Data Flow Diagrams) are used for
logical process modeling
They are aimed to show the way data
flows and processed
Logical Process Modeling using DataFlow Diagrams
◼ Four symbols are used, as shown below:
DFDs
Data flow diagrams can be used to describe processes at different
levels.
At the highest level, the DFD is called a context diagram
A context diagram treats the entire information system as one
process and shows the inputs to the system from external and
internal entities, and the outputs from the system to external and
internal entities
Data stores
shown in a context diagram because the
stores are internal to the system
are not
Recording client jobs and the
assignment of professionals to jobs
◼ The entire system is shown as one process
◼ Entities providing inputs are shown on the left and those receiving
outputs are shown on the right
◼ Flow arrows indicate the type of input or output
Information process modeling for student registration
system
Step 7: Identify information processes
Context diagram --
Level 0 DFD
◼ One level of detail greater than the Context Diagram is shown
in a Level 0 Data-Flow Diagram
◼ The breakdown of the major processes is shown
◼ Data stores being accessed and updated are shown
◼ An arrow going from a process to a data store indicates that the
data store is being updated or records are being added to the
data store by the process
◼ An arrow going from a data store to a process indicates that the
process is reading data from the data store (but the data store is
not being updated or added to)
Information process modeling :: DFD
Recording process DFD
◦ Used to record all aspects of each business event identified in
the REA diagram
Maintenance process DFD
◦ Needed for each resource and ag ent on the REA diagram
◦ The processes involved in adding, updating, or deleting
resources and agents
Reporting process DFD
◦ Need one or more for each resource, event, and agent on the
REA diagram
◦ Involves the extraction of information from one or more data
stores to generate either routine or non-routine reports.
Recording process DFDs :: rules
1.
2.
3.
4.
Each event entity on the EER diagram will have a Level 0 DFD
process (i.e., 1.0, 2.0, etc.),
The agents associated with each event should be shown as
external entity/agent symbols providing inputs to or getting
output from the event process,
All resource and agent entities associated with the event entity
should be shown as data stores being accessed and/or updated by
the event process,
A data store should be shown for the event itself, with an arrow
going to the data store indicating that it is being added to
Level 0 DFD -- example
Student Registration REA-diagram
Information process modeling for student
registration system
Level 0 DFD --
Level 1 DFD
◼ Showing greater detail than a Level 0 DFD, a Level 1 DFD shows
details of the processes within each of the major Level 0 processes
◼ The Level 1 DFD shows the processes involved in the “assign
professionals” process from the Level 0 DFD
◼ Data flows between external or internal entities must always
be labeled because they indicate inputs and outputs
Maintenance information processes for student registration
system
Maintenance process DFD
◦ Needed for each resource and agent on the REA diagram
◦ The processes involved in adding, updating, or deleting resources and agents
Reporting information processes for student registration
system
Reporting process DFD
◦
◦
Need one or more for each resource, event, and agent on the EER diagram
Involves the extraction of information from one or more data stores to generate either routine or
non-routine reports.
S econd com prehensive exa m ple – City P ublic
L ibra ry
◼ Read narrative in the chapter
◼ EER diagram --
S econd com prehensive exa m ple – City
P ublic L ibra ry
◼ Context diagram --
S econd com prehensive exa m ple – City
P ublic L ibra ry
◼ Level 0 DFD --
Summary
Logical data modeling using the entity relationship approach
Logical process modeling using data flow diagrams
Entity-relationship diagramming,
◦ EER
Relationship cardinality,
attributes
optional or mandatory participation in relationships
REA - focuses on the significant business events, the resources
involved in these events, and the agents who perform or are
affected by the events
Summary Cont..
Data flow diagrams focus on processes and
the logical flow of data in the system, and
can focus on different levels of modeling -from context diagrams to "level 0" and "level
1" diagrams
The nine-step event-oriented modeling
approach
Recording process DFDs rules
Comprehensive examples the Student
Registration System example and the City
Public Library example
Physical Design for Database
Systems
Chapter 9
5/13/2019
1
Summary of Chapter 8
Logical data modeling using the entity relationship approach
Logical process modeling using data flow diagrams
Entity-relationship diagramming was discussed, followed by extended entity
relationship, which shows relationship cardinality, entity and relationship
attributes, and the optional or mandatory participation in relationships
Event-oriented logical modeling following the REA framework was
described, which focuses on the significant business events, the resources
involved in these events, and the agents who perform or are affected by the
events
Data flow diagrams focus on processes and the logical flow of data in the
system, and can focus on different levels of modeling -- from context
diagrams to "level 0" and "level 1" diagrams
The nine-step event-oriented modeling approach was described
Recording process DFDs rules
Comprehensive examples were then presented – the Student Registration
System example and the City Public Library example
5/13/2019
2
Converting an REA diagram to relational
tables – Physical Design
Step 8 in the event-oriented modeling
approach involves the application of
relatively straightforward rules for
converting an REA diagram to tables.
The rules take into account (1) the
optionality of relationships, and (2) the
cardinality of relationships.
5/13/2019
3
Conversion rules for mandatory
relationships
1.
2.
3.
4.
For one-to-one relationships: collapse the entities
participating in the relationship into a single table
For relationships other than one-to-one relationships,
create a separate table for each entity
Create fields for each table based on the attributes
identified in the REA diagram.
Designate the primary key in each table based on the
attributes identified in the REA diagram.
5/13/2019
4
Conversion rules for mandatory
relationships:
5.
For one to many relationships: post the primary key of the table on the
"one" side of the cardinality to the table on the "many" side of the
cardinality; the posted key becomes a foreign key in the table on the
"many" side of the cardinality,
6.
For many-to-many relationships: create a new table for the relationship
and assign the primary keys of each participating entity to the new table,
resulting in a composite key for the new table. Add any attributes
unique to the many-to-many relationship as non-key attributes in the
new table. If there are no attributes unique to the many-to-many
relationship, note that the composite key table will be an "all key
relation" (i.e., a table with no non-key attributes).
5/13/2019
5
SALESPERSON-PHONE
SPNO
Name
Addre
ss
Dateemplo
yed
Depart
ment
Phone
NO
Carr
ier
Month
lycharg
e
Paymentdate
We can designate either SPNO or PhoneNO to be the primary key since they both uniquely
identify the rows in the table
5/13/2019
6
SALESPERSONS
SPNO
Name
Address
Date-employed
Department
CELLULAR-PHONES
PhoneNO
PhoneNO *
Carrier
Monthly-charge
Payment-date
1.
A separate table was created for each entity in the REA diagram (SALESPERSONS and CELLULARPHONES).
2.
Fields were created for each table based on the attributes of each entity.
3.
The primary key in each table was designated (SPNO in SALESPERSONS and PhoneNO in CELLULARPHONES).
4.
The primary key of the table on the "one" side of the relationship (i.e., CELLULAR-PHONES) was posted
to the table on the "many" side of the relationship
5/13/2019
7
Collapse the tables together?
SALESPERSON-PHONE
SPNO
Name
Addre
ss
Dateemplo
yed
Depart
ment
Phone
NO
5/13/2019
Carr
ier
Month
lycharg
e
Paymentdate
8
SALESPERSONS
SPNO
Name
Address
Date-employed
Department
CELLULAR-PHONES
PhoneNO
Carrier
Monthly-charge
Payment-date
5/13/2019
SPNO
9
SALESPERSONS
SPNO
Name
Address
Date-employed
Department
CELLULAR-PHONES
PhoneNO
Carrier
Monthly-charge
Payment-date
SP-PHONE
SPNO*
5/13/2019
PhoneNO*
10
Conversion rules for optional
relationships
Two rules for optional relationships
1. Consider all optional relationships as having a "many" cardinality
whether or not the relationship is actually a "many" relationship and
2. Ignore the fact that relationships are optional and apply the above six
rules for mandatory relationships.
the theory behind these rules is to minimize the number of "null" values in
foreign key fields in the database. Null values in foreign key fields are
undesirable mainly because they can make the results of queries performed
on those tables unpredictable
5/13/2019
11
SALESPERSONS
SPNO
SPNO*
Name
Address
Date-employed
Department
CELLULAR-PHONES
PhoneNO
Carrier
Monthly-charge
Payment-date
5/13/2019
12
How did we prevent null values?
Consider the alternative of collapsing both tables into
one
SALESPERSON-PHONE
SPNO
Name
Addre
ss
Dateemplo
yed
Depart
ment
Phone
NO
5/13/2019
Carr
ier
Month
lycharg
e
Paymentdate
13
How did we prevent null values?
Another option would be to create two separate tables and post
the key of the CELLULAR-PHONES table to the SALESPERSONS
table
Both alternatives would result in many null values and are therefore
not desirable.
SALESPERSONS
SPNO
Name
Address
Dateemployed
Department
PhoneNO*
CELLULAR-PHONES
PhoneNO
Carrier
Monthly-charge
Payment-date
5/13/2019
14
The rule for dealing with the optional side of relationships is simply to treat
the optional side as a "many" in a mandatory relationship
Create a new composite key table with the primary key of both CELLULARPHONES and SALESPERSONS
5/13/2019
15
The conversion rule for dealing with optional
relationships simply requires that the above relationship
be treated the same way as a mandatory M:M
relationship.
5/13/2019
16
SUMMARY OF CONVERSION RULES
A. Mandatory relationships
1. Collapse 1:1 relationships into a single table.
2. For 1:M relationships, post the primary key of the table on the "one"
side to the table on the "many" side.
3. For M:M relationships, create a new composite key table posting the
primary key of each participating table to the new table.
B. Optional relationships
1. Treat the "optional" side of a relationship as a mandatory "many"
relationship for the purpose of conversion.
2. Apply the same conversion rules for mandatory relationships stated
above.
5/13/2019
17
Application of physical modeling--the
student registration system example
5/13/2019
18
Applying the conversion rules above
to the above REA diagram
There are no mandatory 1:1 relationships, so we do not have to
collapse any entities together
A 1:M between DEPARTMENTS and COURSE-OFFERING
DEPARTMENTS,DEPT_NO*)
(DEPT_NO,…..,)
COURSE-OFFERING (OFFERING-NO…., )
A 1:M between COURSES and COURSE-OFFERING
COURSE(COURSE_NO)
,COURSE_NO*)
5/13/2019
19
We therefore have the following
sets of relationships
1:M between DEPARTMENTS and INSTRUCTORS
◦ DEPARTMENTS (DEPT_NO)
◦ INSTRUCTORS (INSTRUCTOR-NO, DEPT_NO*)
1:M between INSTRUCTORS and COURSE-OFFERING
◦ INSTRUCTORS (INSTRUCTOR-NO, DEPT_NO*)
◦ COURSE-OFFERING (OFFERING-NO, COURSE_NO*,INSTRUCTOR-NO*)
1:M between STUDENTS and REGISTRATION
◦ STUDENTS (STUDENT-NO)
◦ REGISTRATION (REGISTRATION-NO,STUDENT-NO* )
1:M between DEPARTMENTS and STUDENTS
◦ DEPARTMENTS (DEPT_NO)
◦ STUDENTS (STUDENT-NO, DEPT_NO*)
M:M between COURSE-OFFERING and REGISTRATION
◦ COURSE-OFFERING (OFFERING-NO, INSTRUCTOR-NO*)
◦ REGISTRATION (REGISTRATION-NO,STUDENT-NO* )
◦ COURSE-SECTION-REGISTRATION (OFFERING-NO*, REGISTRATION-NO*,
STATUS)
5/13/2019
20
5/13/2019
21
Database normalization
For implementation of the tables in a relational DBMS it is
necessary to ensure that the database design is not flawed
The process of database normalization seeks to ensure that there
are no significant data anomalies in a database design
A few design flaws may still remain in the tables that result from
mechanical application of the conversion rules
A "normal form" simply means that a table conforms to certain
rules aimed at testing the integrity of the table structure.
5/13/2019
22
Data anomalies
There are three kinds of data anomalies we are concerned about:
◦ An insert anomaly is the inability to insert or add data to a table
because of a design problem
◦ A delete anomaly means that deleting certain data from a table
inadvertently results in the loss of other related data which the
user would like to retain
◦ An update anomaly simply means that a change in a data item
must be performed in multiple locations rather than just one
Increasing levels of normal forms indicate that the table conforms
to stricter rules with consequently fewer data anomalies.
5/13/2019
23
Functional dependency (FD)
In a table with fields X and Y, if there is only
one possible value of Y for every value of X,
then Y is said to be functionally dependent on
X:
field X determines the value in field Y
Ina table with fields Student_Name
Student_ID andand
Student_Name,
Student_ID,
if there is only one possible value of Student_ID
Student_Name
for every value of Student_Name,
Student_ID, thenthen
Student_Name
Student_ID
is said to be functionally dependent on Student_Name:
Student_ID:
field
fieldStudent_Name
Student_ID determines
determines
thethe
value
value
in field
in field
Student_name
Student_ID
Why may FD work only in one direction?
5/13/2019
24
Motivating example
A department in a business school would
like to store information in a database
about professors, their offices, and
courses taught by professors. The
department wants to keep track of
professors' teaching preferences in terms
of the days and times they would like to
teach a course.
5/13/2019
25
PROF-COURSES
OFFICEPROF-NO
1001
NAME
Dr. Benton
NO
401A
PHONE-NO
5-5515
1002
Dr. Carter
401B
5-8761
1003
Dr. Ross
402G
2-6785
1004
Dr. Green
401Z
5-9111
CREDIT-
DAY-
TIME-
HOURS
PREF
PREF
3
MW
8 am
Advanced AIS
1
MW
2 pm
COMP101
Software tools
3
MWF
11 am
ACCT209
Principles
3
TR
3 pm
ACCT327
Intermediate I
3
MWF
10 am
ACCT 328
Intermediate II
3
TR
10 am
ACCT427
AIS
3
MW
10 am
ACCT 209
Principles
3
TR
9 am
COURSE-NO
DESCRIPTION
ACCT427
AIS
ACCT 648
5/13/2019
26
First Normal Form (1NF)
The PROF-COURSES relation above is an example of a nonnormalized relation since it contains a "repeating group."
The existence of multiple cell values in one or more non-key
attributes for a single primary key value is called a repeating
group.
A relation that is said to be in first normal form (1NF) must
have exactly one value in each cell. That is, a 1NF rela tion ha s
no repea ting g roups –(no multiple cell values in non-key
attributes for single values in the primary key).
In a 1NF table, a ll non-key attributes should depend on
the prim a ry key (but not necessa rily on the entire
prim a ry key).
5/13/2019
27
PROFS (2NF)
PROF-NO
NAME
OFFICE-NO
PHONE-NO
1001
Dr.
Benton
401A
5-5515
1002
Dr. Carter
401B
5-8761
1003
Dr. Ross
402G
2-6785
1004
Dr. Green
401Z
5-9111
COURSE-PREF(1NF)
PROF-NO
COURSE-NO
DESCRIPTION
CREDIT-HOURS
DAY-PREF
TIME-PREF
1001
ACCT427
AIS
3
MW
8 am
1001
ACCT648
Advanced AIS
3
MW
2 pm
1002
ACCT209
Principles
3
TR
3 pm
1003
ACCT327
Intermediate I
3
MWF
10 am
1003
ACCT328
Intermediate II
3
TR
10 am
1004
ACCT427
AIS
3
MW
10 am
1004
ACCT209
Principles
3
TR
9 am
1001
COMP101
Software tools
1
MW
11 am
Open Access and create the above tables
5/13/2019
28
Insert Anomalies in 1NF
Add The course
◦ COURSE_NO - ACCT567
◦ Description – ERP
◦ CREDIT-HOURS - 4
It is not possible to add course information without
professor information because the primary key in the
above table is a composite key of PROF-NO and
COURSE-NO
This inability to add data to the database is called an
insert anomaly
5/13/2019
29
PROFS (2NF)
PROF-NO
NAME
OFFICE-NO
PHONE-NO
1001
Dr.
Benton
401A
5-5515
1002
Dr. Carter
401B
5-8761
1003
Dr. Ross
402G
2-6785
1004
Dr. Green
401Z
5-9111
COURSE-PREF(1NF)
PROF-NO
COURSE-NO
DESCRIPTION
CREDIT-HOURS
DAY-PREF
TIME-PREF
1001
ACCT427
AIS
3
MW
8 am
1001
ACCT648
Advanced AIS
3
MW
2 pm
1002
ACCT209
Principles
3
TR
3 pm
1003
ACCT327
Intermediate I
3
MWF
10 am
1003
ACCT328
Intermediate II
3
TR
10 am
1004
ACCT427
AIS
3
MW
10 am
1004
ACCT209
Principles
3
TR
9 am
1001
COMP101
Software tools
1
MW
11 am
5/13/2019
30
Update and Delete Anomalies in
1NF
◦ Please Change AIS credit hours from 3 to 4
◦ If the description of a particular course is to be modified, the
change will have to be performed multiple times rather than just
once
◦ This duplication of the update effort is called an update anomaly
and is also avoidable by redesigning the table
◦ Dr Ross has left the university please delete him from our records
◦ Because a particular course is taught by only one professor. If that
professor leaves the university and his record is deleted from the
database, we also inadvertently lose information about the course
that was being taught by the professor (since that professor was
the only one teaching the course). This inadvertent loss of data is
called a delete anomaly
5/13/2019
31
Why did we have these anomalies?
The DAY-PREF and TIME-PREF fields are FD on the
entire primary key, while the DESCRIPTION and
CREDIT-HOURS are FD only on part of the key
COURSE-PREF(1NF)
PROF-NO
COURSE-NO
DESCRIPTION
CREDITHOURS
DAY-PREF
TIME-PREF
1001
ACCT427
AIS
3
MW
8 am
1001
ACCT648
Advanced AIS
3
MW
2 pm
1002
ACCT209
Principles
3
TR
3 pm
1003
ACCT327
Intermediate I
3
MWF
10 am
1003
ACCT328
Intermediate II
3
TR
10 am
1004
ACCT427
AIS
3
MW
10 am
1004
ACCT209
Principles
3
TR
9 am
1001
COMP101
Software tools
1
MW
11 am
5/13/2019
32
Second Normal Form (2NF)
A relation is said to be in second normal form if
◦ (1) it is in 1NF, and
◦ (2) the non-key attributes in the relation are functiona lly dependent
on the entire primary key.
The PROFS table is already in 2NF since every non-key field depends on the
entire primary key (if there is only one field m a king up the prim a ry
key, the ta ble will a utom a tically be in 2NF )
PROFS (2NF)
PROF-NO
NAME
OFFICE-NO
PHONE-NO
1001
Dr.
Benton
401A
5-5515
1002
Dr.
Carter
401B
5-8761
1003
Dr. Ross
402G
2-6785
1004
Dr.
Green
401Z
5-9111
5/13/2019
33
Second Normal Form (2NF)
Then How do we decompose the COU RS E -P RE F Ta ble ?
The two relations into which the COURSE-PREF relation is decomposed
are :COURSES which contains only course information, and PREFERENCES
which contains only information about professor preferences for courses
COURSE-PREF(1NF)
PROF-NO
COURSE-NO
DESCRIPTION
CREDITHOURS
DAY-PREF
TIME-PREF
1001
ACCT427
AIS
3
MW
8 am
1001
ACCT648
Advanced AIS
3
MW
2 pm
1002
ACCT209
Principles
3
TR
3 pm
1003
ACCT327
Intermediate I
3
MWF
10 am
1003
ACCT328
Intermediate II
3
TR
10 am
1004
ACCT427
AIS
3
MW
10 am
1004
ACCT209
Principles
3
TR
9 am
1001
COMP101
Software tools
1
MW
11 am
5/13/2019
34
COURSES (3NF)
COURSE-NO
DESCRIPTION
CREDIT-HOURS
ACCT427
AIS
3
ACCT648
Advanced AIS
3
ACCT209
Principles
3
ACCT327
Intermediate I
3
ACCT328
Intermediate II
3
COMP101
Software tools
1
COURSE-PREF (3NF)
PROF-NO *
COURSE-NO *
DAY-PREF
TIME-PREF
1001
ACCT427
MW
8 am
1001
ACCT648
MW
2 pm
1001
COMP101
MWF
11 am
1002
ACCT209
TR
3 pm
1003
ACCT327
MWF
10 am
1003
ACCT328
TR
10 am
1004
ACCT427
MW
10 am
1004
ACCT209
TR
9 am
5/13/2019
35
PROFS (2NF)
PROF-NO
NAME
OFFICE-NO
PHONE-NO
1001
Dr.
Benton
401A
5-5515
1002
Dr.
Carter
401B
5-8761
1003
Dr.
Ross
402G
2-6785
1004
Dr.
Green
401Z
5-9111
5/13/2019
36
Insert, update, and delete
anomalies in 2NF
Let us assume that there is exactly one phone in every office (PROFS table ).
PHONE-NO non-key attribute is functionally dependent on the OFFICE-NO
non-key attribute.
Dependency between non-key attributes in a relation is called a transitive
dependency.
PROFS (2NF)
PROF-NO
NAME
OFFICE-NO
PHONE-NO
1001
Dr.
Benton
401A
5-5515
1002
Dr. Carter
401B
5-8761
1003
Dr. Ross
402G
2-6785
1004
Dr. Green
401Z
5-9111
5/13/2019
37
Insert, update, and delete anomalies in
2NF
◦ Add Office # 403B and phone number 55519
◦ It is not possible to add information about a
new office and phone number until a
professor is assigned to that office
◦ Dr Green has left the school please delete
him from our records
◦ When a professor record is deleted (upon
the professor leaving the university), we also
inadvertently lose office and phone number
information for the office that was occupied
by the professor
5/13/2019
38
Third Normal Form (3NF)
A relation is said to be in
third normal form if
◦ (1) it is in 2NF, and
◦ (2) all functional
dependencies in the
relation originate from
the primary key
◦ (3) the relation contains
no derived fields
The PROFS relation is
decomposed into two
relations:
PROF-INFO (3NF)
PROF-NO
NAME
OFFICE-NO*
1001
Dr. Benton
401A
1002
Dr. Carter
401B
1003
Dr. Ross
402G
1004
Dr. Green
401Z
OFFICES (3NF)
OFFICE-NO
PHONE-NO
401A
5-5515
401B
5-8761
402G
2-6785
401Z
5-9111
5/13/2019
39
Normalization summary
Rela tion a re:
1. in 1NF if there are no repeating groups in the relation (every cell
contains a single value; exactly one non-key attribute value for
every primary key value),
2. in 2NF if every non-key field is functionally dependent on the entire
primary key (no field depends on only part of a composite primary
key)
3. in 3NF if all functional dependencies in the relation originate from
the primary key (no transitive dependencies) and the relation
contains no derived fields
5/13/2019
40
Sales Order
Fiction Company
202 N. Main
Mahattan, KS 66502
CustomerNumber:
Customer Name:
Customer Address:
Item Ordered
800
801
805
1001
ABC Company
100 Points
Manhattan, KS 66502
Sales Order Number:
405
Sales Order Date:
2/1/2000
Clerk Number:
210
Clerk Name:
Martin Lawrence
Description
widgit small
tingimajigger
thingibob
Quantity
40
20
10
Unit Price
60.00
20.00
100.00
Total
2,400.00
400.00
1,000.00
3,800.00
Order Total
(SalesOrderNo, Date, CustomerNo, CustomerName, CustomerAdd, ClerkNo,
ClerkName, ItemNo, Description, Qty, UnitPrice,Total)
5/13/2019
41
Normalization: First Normal Form
Separate Repeating Groups into New Tables.
Repea ting Groups Fields that may be repeated several times for
one document/entity
Create a new table containing the repeating data
The primary key of the new table (repeating group) is always a
composite key; Usually document number and a field uniquely
describing the repeating line, like an item number.
The new table is as follows:
◦ (S a lesOrderNo*, Item No, Description, Qty, U nitP rice)
◦ The repeating fields will be removed from the original data table, leaving the
following.
◦ (S a lesOrderNo, Da te, Custom erNo, Custom erNam e,
Custom erA dd, ClerkNo, ClerkNa m e)
◦ These two tables are a database in first normal form
5/13/2019
42
Normalization: Second Normal
Form
Remove Partial Dependencies.
Functiona l Dependency The value of one attribute in
a table is determined entirely by the value of another.
Pa rtia l Dependency A type of functional dependency
where an attribute is functionally dependent on only
part of the primary key (primary key must be a
composite key)
Create separate table with the functionally dependent
data and the part of the key on which it depends
5/13/2019
43
Normalization: Second Normal
Form
The new table will contain the following fields:
◦ (Item No, Description)
All of these fields except the primary key will be removed from the
original table. The primary key will be left in the original table to
allow linking of data:
◦ (S a lesOrderNo*, Item No*, Qty, U nitP rice)
Never trea t price a s dependent on item. Price ma y be
different for different sa les orders (discounts, specia l
customers, etc.)
Along with the unchanged table below, these tables make up a
database in second normal form:
◦ (S a lesOrderNo, Da te, Custom erNo, Custom erNam e,
Custom erA dd, ClerkNo, ClerkNa m e)
5/13/2019
44
Normalization: Third Normal Form
Remove transitive dependencies.
Tra nsitive Dependency A type of functional dependency
where an attribute is functionally dependent on an
attribute other than the primary key Thus its value is only
indirectly determined by the primary key
Create a separate table containing the attribute and the
fields that are functionally dependent on it. Tables created
at this step will usually contain descriptions of either
resources or agents. Keep a copy of the key attribute in
the original file
5/13/2019
45
Normalization: Third Normal Form
The new tables would be:
◦ (Custom erNo, Custom erNam e, Custom erA dd)
◦ (ClerkNo, ClerkNa m e)
All of these fields except the primary key will be removed from the
original table. The primary key will be left in the original table to
allow linking of data as follows:
◦ (S a lesOrderNo, Da te, Custom erNo*, ClerkNo*)
Together with the unchanged tables below, these tables make up
the database in third normal form.
◦ (Item No, Description)
◦ (S a lesOrderNo*, Item No*, Qty, U nitP rice)
5/13/2019
46
City Public Library
5/13/2019
47
We therefore have the following
sets of relationships
1) 1:M between MEMBERS and CHECK-OUT, (2) a 1:M between CLERKS and
CHECK-OUT, (3) a 1:M between CLERKS and CHECK-IN, (4) a 1:M between
MEMBERS and CHECK-IN, (5) a 1:M between CASH and CHECK-OUT, (6) a
M:M between BOOKS and CHECK-OUT, (7) a M:M between BOOKS and
CHECK-IN, and (8) a M:M between CHECK-IN and CHECK-OUT.
For all 1:M relationships, the conversion rules require that the primary key of
the table on the "one" side of the relationship be posted as a foreign key to the
table on the "many" side of the relationship.
For the M:M relationship between COURSE-OFFERING and REGISTRATION,
we must create a new composite key table.
5/13/2019
48
Tables
BOOKS (BOOK-NO, TITLE, AUTHOR, PUBLISHER, ISBN, TIMES-CHECKEDOUT)
CASH (ACCOUNT-NO, BANK, ACCT-NO-AT-BANK, TYPE, DATE-OPENED,
CURRENT-BALANCE)
MEMBERS (MEMBER-NO, NAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, DATEOPENED, DATE-CLOSED, FINES-DUE)
CLERKS (CLERK-NO, NAME, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, SALARY,
DATE-HIRED, DATE-LEFT)
CHECK-OUT (CHECK-OUT-NO, DATE, DUE-DATE, MEMBER-NO*, CLERK-NO*,
ACCOUNT-NO*)
BOOKS-CHECKED-OUT (CHECK-OUT-NO*, BOOK-NO*)
CHECK-IN (CHECK-IN-NO, DATE, CLERK-NO*, MEMBER-NO*)
BOOKS-CHECKED-IN (CHECK-IN-NO*, BOOK-NO*, FINE)
CHECKIN-CHECKOUT (CHECK-IN-NO*, CHECK-OUT-NO*)
5/13/2019
49
5/13/2019
50
Summary
Physical design involves translating a logical model of a
database system to constructs that can be implemented
in a database system, i.e., tables
The rules for converting REA diagrams to tables were
covered. Basic rules for mandatory relationships were
first presented, followed by the special case rules for
dealing with optional relationships.
The process of database normalization was then
discussed. The anomalies that can result from nonconformance to normal forms were described. First,
second, and third normal form were explained.
5/13/2019
51
Kim’s Flowers Purchasing Cycle Assignment
Required:
• Based on the narrative below, your team should follow steps 1-6 of the event oriented
modeling (covered in chapter 8) for constructing a comprehensive EER diagram. You
should submit a typed document describing in detail each one of the steps.
• Your team would submit a complete EER diagram and Level 0 DFD for the chosen
enterprise. The diagrams should be prepared in Microsoft Word or PowerPoint.
• Corrections and comments from the first stage should be implemented in subsequent
stages.
• If you identify more resources/events/agents then are needed NO points will be
deducted!
• All students are required to equally participate in the project and may be called upon
to present any part of the project.
Kim Burke is the owner and operator of a small flower shop, Kim's Flowers. She has
decided to develop a database system to track her purchases, floral inventory, accounts
payable and cash payments. However, she does not have time to do the development
herself. Therefore, she has hired you to design and implement the system for her. She
describes the requirements of her system as follows:
Whenever flowers are needed, a purchasing agent enters a purchase order for the items
needed directly into the computer system. To accommodate all of her floral needs, the
purchasing agent may need to prepare several orders, as Kim orders flowers from many
different vendors. The purchasing agent may order several types of flowers from a single
vendor on one order. Kim approves and prints these purchase orders and sends them to
the vendors. A floral designer records the receipt of flowers from a vendor on a special
screen in her accounting system. At the end of each month, an accounts payable clerk
creates checks to pay for all amounts charged through the end of the month. Once the
checks are created, Kim authorizes the checks, prints them and mails them to vendors.
Kim also uses special screens in the computer system to perform maintenance for
inventory items (add, modify and delete items from the file), and to record disposal of old
or damaged flowers.
•
•
Prepare an REA diagram describing Kim's database. You may repeat a box for an
individual entity if necessary to make your drawing neat. Kim would like for you to
consider that, eventually, as new stores are opened, she will have store managers in
each store that will perform the tasks she now performs. As a result, the system
should be planned to always store information about the employee associated with
transactions, whether that employee enters information, performs a task (such as
delivery) or formally authorizes the transaction. Hints: (1) Each item on a floral
receipt may be from a different purchase order. Different items on a single purchase
order may be received at different times using different receipts. (2) Each check
may pay for several receipts. However, a single receipt will always be paid for using
the same check. (3) Multiple floral items may be disposed of using a single form.
Prepare the context diagram and the level 0 DFD.
Purchase answer to see full
attachment