Transcript Relational Database Model - School of Business Administration

Introduction to FIS 318/618,
Financial Systems & Databases:
The Relational Database Model
Oakland University
School of Business Administration
Accounting and Finance
Joe Callaghan
The Relational Database Model
• Based on the theory of relational math (set theory)
• It is an “automatic transmission” database (with
embedded relationships between tables) which
replaces the “standard transmission” database (which
employs flat-file techniques with explicit pointers
between files and records)
• Flat-files (collections of similar records) are being
replaced by collections of interrelated files
• Allows data to be broken down into logical, smaller,
more manageable units - simplifies the organization
of complex sets of data
Why A Relational Model?
• Duplicate data reduced - less input, maintenance, storage,
and improved data integrity
• Data independence: Data can be thought of as being stored
in tables regardless of how physically stored.
• Application independence: Databases defined
independently from the systems and programs that will use
them - allows users to create ad hoc queries, rather than
only receive pre-specified reports
• A change in the database does not require rewriting all the
application program codes. Ability to share same data
across multiple applications and systems.
• It has the ability to maintain several tables of related
information that can be accessed by several different users
in many different ways - a single query can retrieve data
from more than one table.
Some Definitions...
Data: Raw facts about the organization and its business
transactions that are of interest to the end user
Database: A computer structure that houses a collection of data
Relational database: Stores information about instances of
entities (a specific sales event, salesperson), attributes of
those entities (invoice no., salesperson ID) , and the
relationships among these entities (each sale can only have
one salesperson) - perceived by user to be a collection of twodimensional tables
RDBMS: Software that manages a relational database, controls
access, and allows users to retrieve requested data through a
standard data-access language, SQL.
Entity-type: Something of significance about which you want
to store data in a database, e.g., customers, employees,
suppliers, inventory items (note: this is a data modeling term
– an entity becomes a table in a RDBMS)
Table: An entity-type (e.g., customer) and its attributes
Attribute: A property or characteristic of an entity. A column in
a relational database table, e.g., customer name, reference
#, address, zip ((note: this is a data modeling term – an
attribute becomes a column in a RDBMS
Row (tuple, record): A record of data in a database table - a
single occurrence or entity instance
Value: Data in a “cell” – the intersection between row and
column in a database table
Types of Attributes
Key (identifier in data modeling): Attribute, or combination of
attributes, that determines the values of other attributes in
each row
Composite Key: Multiple-attribute keys; may be further
subdivided, e.g., phone may be area code and number - can
be a primary key
Candidate Key (CK): Attribute (or a minimum combination of
attributes) that uniquely identifies each row in a given table there can be more than one CK (employee entity type: SSN;
assigned ID#)
Primary Key (PK) ( a unique identifier in data modeling): A
CK selected to uniquely identify all other attributes in a given
row; cannot be Null
Foreign Key (FK): ( a relationship in data modeling): Attribute
(combination of attributes) whose value(s) must match the
Primary Key in another table in the same database, or whose
value(s) must be Null
Attributes With A Null Value
• Null Value: An unknown attribute value (e.g.,
salesperson not yet allocated to a customer) - it
is not a zero. It is an optional attribute.
• Inclusion of nulls in a table is important - they
provide a consistent way to distinguish
between valid data such as a 0 and missing
data, e.g., an account payable with 0 is good to
see; one with an unknown balance can indicate
a significant problem
• In most cases, nulls appear as blanks on a
query’s result table on a screen
Relationships
• Data modeling term that indicates an
association between tables: How the things of
significance are related (A FK must match to
an existing PK, or else be NULL)
• This controlled redundancy allows linking of
tables (hence “relational”)
• Entity-Relationship Diagram (ERD): A data
model (at the conceptual level) that shows
the relationships enforcing business rules
between entities (tables) in a database
environment (Fig. 5.4)
Business Rules
• Narrative descriptions of policies,
procedures, or principles in an
organization
• Examples:
– A pilot cannot be on duty for more than 10
hours in a 24-hour period
– A professor must teach at least three classes
in a semester
– A class may not have fewer than 10
enrollments
Concept to Definition
Concept
Collection of Objects
Object
Aspect
File
System
File
record
field
Uniqueness
Relationship
index?
programmed
Value
value
Modeling
Entity-Type
entity
attribute
identifier
relationship
with cardinality
and optionality
realization
RDBMS
Table
row (tuple)
column of a
data type
primary key
primary key to
foreign key
matching
cell
Example
Customers
John Doe, a particualr customer
Hair Color
customer #
sometimes has orders
Blonde
Example from Ch. 3 (P&S)
• Partial MSC:
– Sale to Customer
– Ship to Customer
• Multi-product merchandiser
• Salesperson, Shipping Clerk
Customer
Invoice
Invoice
Figure 3.25 Entity-relationship diagram.
1
is sent an
1
contains
M
draws upon
M
M
M
Invoice
Invoice Line
Inventory
1
Customer
is sent an
1
Invoice
contains
1
M
Invoice
Figure 3.26 Revised entity-relationship diagram.
M
M
M
InvoiceInven
Invoice
Invoice Line
1
Inventory
Figure 3.1 Sales transactions stored in a database accounting system.
Figure 3.7 The Customer relation, tblCustomer.
foreign key
primary key
Figure 3.8 Primary key and foreign key relationship.
tblCountryName
(CountryID, CountryName, ExportCoffeeBags, ExportTeaPounds)
tblCustomer
(CustomerID, CompanyName, Contact, Address, City, State,
ZipCode, PhoneNumber, FaxNumber, CreditLimit)
tblEmployee
(EmployeeID, EmployeeFirstName, EmployeeLastName,
EmployeeWorkPhone, EmployeeTitleID, EmployeeCommRate,
EmployeeHireDate, EmployeeDOB, EmployeeGender,
EmployeeNotes)
tblEmployeeTitle
(TitleID, Title)
tblInventory
(InventoryID, ItemID, Caffeinated, Price, OnHand)
tblInventoryDescription
(ItemID, Name, BeverageType, Flavored, CountryID, Comments)
tblInvoice
(InvoiceID, InvoiceDate, OrderDate, CustomerID, EmployeeID,
CustomerPO)
tblInvoiceLine
(InvoiceID, InventoryID, Quantity, UnitPrice, Discount)
tblSalesTaxRate
(StateAbbreviation, StateName, TaxRate, Population, LandArea)
Figure 3.9 Schemas of tables in the invoicing system.
primary key
foreign key
500 records in this table
Figure 3.10 Example rows in the Invoice table, tblInvoice.
primary key
foreign key
Figure 3.11 Example rows in the Invoice Line table, tblInvoiceLine.
primary key
foreign key
Figure 3.12 Example rows in the primary Inventory table, tblInventory.
primary key
foreign key
Figure 3.13 Example rows in the secondary Inventory table, tblInventoryDescription.
Normalization
• Process of taking a “raw” database and breaking it
into logical units called tables, by following theoretical
rules called normal forms
• The intent is to create a degree of controlled
redundancy that allows two or more tables to be
joined, by matching a FK in one table to a PK in
another table
• Referential integrity (constraint created upon table
creation) is enforced when every non-null FK value
must match an existing PK value (if there is a FK,
there has to be a PK for that FK in another table)
• Normalization has six nested normal forms
• Generally a well-formed business database will be
normalized through 3rd normal form (3NF)
Benefits of Normalization
•
•
•
•
•
•
Greater overall database organization
Minimize data redundancies
Data consistency within the database
A more flexible database design
Data can be used more productively
A better handle on database security
Disadvantage of Normalization
• Reduced database performance because database
must locate requested tables and join data - requires
additional processing logic
Normal Forms
—Normalization through a series of
stages called NORMAL FORMS
–Each NF depends on normalization
steps taken in the previous NF
»First Normal Form - 1NF
»Second Normal Form - 2NF
»Third Normal Form - 3NF
1NF
First normal form rules:
•All key attributes must be defined
•There must be no repeating groups (values), i.e., each
row/column intersection can have only one value
•All attributes must be functionally dependent on the PK,
or part of the PK - e.g., SSN determines DOB, but DOB
cannot determine SSN
Hint: Put all attributes in a two-dimensional flat table,
with no repeating values
General Journal Entry:
Traditional View - Unnormalized
Table 1
Traditional General Journal Entries
Tran_#
Date
Title
Acct_#
Debit
1
1/3/96
Cash
11001
100.00
Receivable
11003
200.00
Sales
41001
Credit
Explain
300.00
credit sale with down payment
2
1/3/96
COGS
Inventory
51001
11004
150.00
150.00
sale of inventory
Assume that the transaction # will reset to 1 at the beginning of the next fiscal year
GJ: First Normal Form
Table 2
General Journal Entries: 1NF
Tran_#
Date
Title
Acct_#
Debit
Credit
Explain
1
1/3/96
Cash
11001
100.00
0.00
credit sale
1
1/3/96
Receivable
11003
200.00
0.00
credit sale
1
1/3/96
Sales
41001
0.00
300.00
credit sale
2
1/3/96
COGS
51001
150.00
0.00
sale of
inventory
2
1/3/96
Inventory
11004
0.00
150.00
sale of
inventory
2NF
–Second Normal Form Rules:
»Table is in 1NF; and
»Table includes no partial
dependencies; that is, no attribute is
dependent on only portion of the
primary key – must be dependent on
entire PK
Hint: Examine non-key attributes to determine
whether any are dependent on only portion of a
composite PK - this would violate 2NF
If a table only has one attribute as a PK, then it
is in 2NF.
Chart of Accounts Table
Table C
General Journal Entries: 2NF, 3NF
Acct_#
Title
11001
Cash
11003
Receivable
41001
Sales
51001
COGS
11004
Inventory
Transaction Listing Table
Table A
General Journal Entries: 2NF, 3NF
Tran_#
Date
Explain
1
1/3/96
credit sale with down payment
2
1/3/96
sale of inventory
Transaction Detail Table
(Base Table)
Table B
General Journal Entries: 2NF
Tran_#
Date
Acct_#
Debit
1
1/3/96
11001
100.00
0.00
1
1/3/96
11003
200.00
0.00
1
1/3/96
41001
0.00
300.00
2
1/3/96
51001
150.00
0.00
2
1/3/96
11004
0.00
150.00
Credit
3NF
–Third Normal Form Rules:
»Table is in 2NF and
»There are no transitive
dependencies
Hint: You will violate 3NF if you can deduce the
value of a non-key attribute by knowing the
value of another non-key attribute
Normalized
Transaction Detail (Base) Table
Table D
General Journal Entries: 3NF
Tran_#
Date
Acct_#
Amount
1
1/3/96
11001
$100.00
1
1/3/96
11003
$200.00
1
1/3/96
41001
($300.00)
2
1/3/96
51001
$150.00
2
1/3/96
11004
($150.00)
Example from Ch. 3 (P&S)
Continued
repeating
group
repeating
group
repeating
group
repeating
group
Figure 3.14 Example table containing repeating groups.
Figure 3.15 Example rows of the Customer table in first normal form (1NF).
CustomerID CompanyName
PhoneNumber
Figure 3.16 Functional dependencies in the Customer table.
Contact
InvoiceID
Total
Figure 3.17 Invoice table in second normal form (2NF).
InvoiceID
InvoiceDate
OrderDate
CustomerID EmployeeID
Figure 3.18 Transitive dependencies in the Invoice table shown in Figure 3.17.
Contact
Operations
•
•
•
•
Restrict aka Select
Project
Join
SQL the standard language
– DDL: data definition language
– DML: data manipulation language
• Destructive: CUD
• Non-Destructive: R
• CRUD again
Employee table:
ID
1301
1364
1528
1695
2240
2318
2754
3370
3432
3436
3458
3609
…
Name
Stonesifer
Pruski
Pacioli
Nagasaki
Stonely
Hunter
Kahn
Kole
English
Gates
Morrison
Chang
…
4112 Goldman
Comm HireDate BirthDate Gender
5% 07/06/96 03/10/66
F
4% 12/01/00 01/26/79
M
6% 08/26/95 05/06/50
M
4% 01/28/00 04/10/77
M
15% 11/05/88 05/03/61
F
8% 11/16/93 01/26/54
F
5% 05/14/97 05/29/61
M
9% 02/08/92 03/23/63
M
8% 10/01/93 02/14/56
F
6% 04/11/95 03/09/54
M
15% 12/13/89 07/04/56
F
5% 09/16/97 03/30/77
F
…
11%
…
…
…
12/24/90
03/05/62
M
Result of selection operation: HireDate > 1/1/97
ID
1364
1695
2754
3609
Figure 3.19 Select operation.
Name
Pruski
Nagasaki
Kahn
Chang
Comm HireDate BirthDate Gender
4% 12/01/00 01/26/79
M
4% 01/28/00 04/10/77
M
5% 05/14/97 05/29/61
M
5% 09/16/97 03/30/77
F
Employee table:
ID
1301
1364
1528
…
4057
4058
4082
4112
Figure 3.20 Project operation.
Name
Stonesifer
Pruski
Pacioli
…
Bateman
Halstead
Flintsteel
Goldman
Projection:
Comm
5%
4%
6%
HireDate BirthDate Gender
07/06/96 03/10/66
F
12/01/00 01/26/79
M
08/26/95 05/06/50
M
Name
Gender
Stonesifer
F
Pruski
M
Pacioli
M
…
…
…
…
…
…
9%
5%
11%
11%
02/16/92
06/16/96
03/21/90
12/24/90
05/01/58
12/22/73
08/22/58
03/05/62
M
F
F
M
Bateman
Halstead
Flintsteel
Goldman
M
F
F
M
tblEmployee
ID
1301
1364
1528
1695
2240
2318
2754
3370
3432
3436
…
Name
Stonesifer
Pruski
Pacioli
Nagasaki
Stonely
Hunter
Kahn
Kole
English
Gates
…
4082 Flintsteel
4112 Goldman
tblEmployeeTitle
TitleID
2
1
2
1
3
2
2
2
2
2
HireDate Gender
07/06/96
F
12/01/00
M
08/26/95
M
01/28/00
M
11/05/88
F
11/16/93
F
05/14/97
M
02/08/92
M
10/01/93
F
04/11/95
M
…
…
…
3
3
03/21/90
12/24/90
F
M
TitleID
1
2
3
4
5
6
7
8
9
Title
Sales Trainee
Sales Associate
Senior Sales Associate
Sales Manager
Senior Sales Manager
Division Sales Manager
Regional Manager
Division Manager
National Sales Manager
Result of join operation:
ID
1301
1364
1528
1695
2240
2318
2754
3370
3432
3436
…
Name
Stonesifer
Pruski
Pacioli
Nagasaki
Stonely
Hunter
Kahn
Kole
English
Gates
…
4082 Flintsteel
4112 Goldman
Figure 3.21 Join operation.
TitleID
2
1
2
1
3
2
2
2
2
2
HireDate Gender
Title
07/06/96
F
Sales Associate
12/01/00
M
Sales Trainee
08/26/95
M
Sales Associate
01/28/00
M
Sales Trainee
11/05/88
F
Senior Sales Associate
11/16/93
F
Sales Associate
05/14/97
M
Sales Associate
02/08/92
M
Sales Associate
10/01/93
F
Sales Associate
04/11/95
M
Sales Associate
…
…
…
3
3
03/21/90
12/24/90
F
M
…
Senior Sales Associate
Senior Sales Associate
tblCustomer
Custom erID CompanyName
Contact
Address
PhoneNumber
(city, state,zip)
tblInvoice
InvoiceID
InvoiceDate
OrderDate
CustomerID
EmployeeID
tblInvoiceLine
InvoiceID
InventoryID
Quantity
UnitPrice
Discount
InventoryID
ItemID
Caffeinated
Price
OnHand
ItemID
Name
BeverageType
Flavored
CountryID
tblInventory
tblInventoryDescription
Figure 3.22 Joining tables with primary key/foreign key relationships.
FaxNumber
CustomerPO
Comments
CreditLimit