RELATIONAL DATABASES

AND ENTERPRISE
SYSTEMS

By: Meschell P. Vergara

 DATABASE
 A database is a
collection of related data
for various uses.
 Used to maintain
information about
various types of objects
(inventory), events (sales),
people(customer) and
places (retail store).
 DATABASE

3 types of Data Models

a) Hierarchical data model- organized data into tree-
like structure that allows repeating information using
defined parent/child relationship. The mapping from parent
to child is (1:N), also known as one-to-many relationships.
b) Network Data Model- allows each record to have
multiple parent and child (M:N) also known as many-to-
many relationships.
c) Relational Data Model
 RELATIONAL DATA MODEL
 Is a data model that stores information in the form of
related two-dimentional tables. It allows designers and users
to identify relationships at the time the database is created.
 Are the dominant data model form in use today.
 RELATIONAL DATA MODEL
Advantages:
a) Flexibility and scalability- able to handle changes in
business and informational requirement quickly and easily.
SAP, Oracle, Microsoft use this model as their foundation.
b) Simplicity- easy to communicate to both database
users and database developers.
c) Reduced Information redundancy- requires each
data to be recorded in one place which helps keep the
information updated because it only has to be updated
once in a database, which can help avoid data
inconsistency.
DATABASE MANAGEMENT SYSTEM
Is defined as a computer program that creates, modifies
and queries the database. Specifically, it is designed to
manage a database’s storage and retrieval of information.
Data Dictionary
Describes the data fields in each database record such as
field description, field length, field type (e.g., alphanumeric,
numeric), etc.
Data Administrator

The person responsible for the design, implementation,

repair and security of a firm’s database. Manages the
complete database of an organization.
Progress Checks
Name at least 3 uses of Sales Database in an
organization:
1. Which products are selling the best.
2. Which products needs to advertise or which needs to
lower the price of.
3. How many of which product to order for tomorrow,
next week, and next month from its suppliers.
 Fundamentals of Relational
Databases
Entities and Attributes:
Class in the relational database model could be a person, place,
thing, transaction, or event about which information is stored.
(Person- e.g., customers and employees)
( Event- e.g., cash receipt, sales, and purchases)
(Thing- e.g., product)
Attributes are characteristics, properties, or adjectives that
describe each class.
•(Customer- e.g. Customer ID, Customer last name, first name and
address)
•(Sales- e.g., Invoice No. Customer ID, Date and Product No.)
•(Product- e.g., Product No., Product Name and Product price)
 Fundamentals of Relational
Databases
Three Main Constructs:
Table/Relation The primary construct, used for data storage
with rows and columns much like a spreadsheet. Each table in
a database represents either a class or a relationship between
classes. Tables need to be properly linked to make a relational
database.
Column The columns in a table are called fields that
represent the attributes or characteristics of the class relationship.
Rows The rows in a table are called records or tuples. The
records represent all the specific data values that are associated
with one instance.
Three Main Constructs:
 CUSTOMER TABLE
Customer ID Customer Last Customer First Customer
Name Name Address
602-966-1238 Wagstaff Mark 168 Apple Rd.,
Rockville, MD 20952

602-252-7513 Waite Seth 2500 Campanile Dr.,

NY 10001

SALES TABLE
Invoice No. Customer ID Date Product No.

13131 602-966-1238 10/17/13 1233

13945 602-252-7513 12/28/13 1334

11995 602-966-1238 2/21/14 1233

 Fundamentals of Relational
Databases
Keys and Relationships:
Primary key is an attribute or combination of attributes that
uniquely identify a specific row in a table.
Foreign key in the relational database model serves as an
attribute in one table that is a primary key in another table. A
foreign key provides a logical relationship, or a link between
two tables.
 CUSTOMER TABLE
Customer ID Customer Last Customer First Customer
Name Name Address
602-966-1238 Wagstaff Mark 168 Apple Rd.,
Rockville, MD 20952

602-252-7513 Waite Seth 2500 Campanile Dr.,

NY 10001

Primary Key

Foreign Key
SALES TABLE
Invoice No. Customer ID Date Product No.

13131 602-966-1238 10/17/13 1233

13945 602-252-7513 12/28/13 1334

11995 602-966-1238 2/21/14 1233

 SALES TABLE
Invoice No. Customer ID Date Product No.

13131 602-966-1238 10/17/13 1233

13945 602-252-7513 12/28/13 1334

11995 602-251-7513 2/21/14 1233

Primary Key Foreign Key

Primary Key

PRODUCT TABLE
Product No. Product Name Product Price

1233 Apple iPhone 5g 399

1334 Motorola Droid 299

1233 Apple iPhone 5g 399

Gizmos and Gadgets June 17, 2014
Sales Invoice
Inv No. 13131

To: Mark Wagstaff

168 Apple Rd., Rockville, MD 20952
602-966-1238
Product No. Description Price Amount

1233 Apple iPhone 5g 399.00 399.00

Total 399.00
 Basic Requirements of Tables

The Entity Integrity Rule: the primary key of a table must have
data values (cannot be null).
The Referential Integrity Rule: the data value for a foreign key
must either be null or match one of the data values that already
exist in the corresponding table.
Each attribute in a table must have a unique name.
Values of a specific attribute must be of the same type.
Each attribute (column) of a record (row) must be single-valued.
This requirement forces us to create a relationship table for each
many-to-many relationship.
All other non-key attributes in a table must describe a
characteristic of the class (table) identified by the primary key.
Microsoft Access to Implement a
Relational Database
Microsoft access is a program
in the Microsoft Office Suite.
Access is a simple database
management system that can
be used to run databases for
individuals and small firms.
The basic building block of a
database is the table. It is linked
by the foreign keys, forming an
interconnected network of
records that taken together are
relational database.
Microsoft Access to
Implement a Relational
Database

The Access system is composed of 7 objects that are used

to implement relational databases.
1.Tables are used to store data which consists of a series
of rows (records) and columns (attributes) connected by
relationships (links between tables).
2.Queries are a tool used to retrieve and display data
derived from records stored within the database.
3.Forms are utilized by users to enter data into tables
and view existing records.
4. Reports are used to integrate data from one or more
queries and tables to provide useful information to
decision makers.
5. Pages, Web-based forms, allow data to be entered
into the database in real time from outside of the
database system.
6. Macros are defined by users to automate processes
like opening a specific form.
7. Access’s code can be altered by the use of modules.
DATA MODEL FOR SALES PROCESS

(1..*) (0..1) (0..*) (1..1)

(1..*)

(1..1)

(1..1)

(1..1)

(1..*)

(1..1) (0..*) (1..*)

(0..*)
(1..1)
SALES EVENT

(1..*) (0..1) (0..*) (1..1)

(1..*)

(1..1)
CASH RECEIPT EVENT

(1..1)

(1..*)

(1..1) (0..*)

(0..*)

(1..1)
SALES AND CASH RECEIPT EVENT

(1..1) (1..*)
 Assume the following to implement a simple database
using Access.
Customer Sales Person
a)Customer Number a)Salesperson Number

b)Customer Zip b)Salesperson First Name

c)Customer First Name c)Salesperson Last Name

d)Customer Last Name d)Salesperson SSN

e)Customer Address
Sales
f)Customer City
a)Sale Number
g)Customer email
b)Sale Date
Cashier c)Sale Amount
a)Cashier Number d)Payment type
b)Cashier First Name e)Customer Number
c)Cashier Last Name f)Salesperson Number
d)Cashier SSN
 Assume the following to implement a simple database
using Access.

Cash Receipt Inventory

a)Receipt Number a)Inventory Number

b)Receipt Date b)Description

c)Receipt Amount c)Completion Date

d)Customer Number d)Cost

e)Salesperson Number e)Price

f)Sale Number
Cash
a)Account Number
b)Bank Name
c)Bank Address
d)Bank Contact person
e)Balance
Structured Query Language (SQL)
SQL (usually pronounced "Sequel") stands for
Structured Query Language and is a computer
language designed to query data in a relational database.
Structured Query Language (SQL)
 SQL is based on relational algebra and allows a user to
query and update the database.
 In a database, while queries allow the user to access,
read and report on desired data, the responsibility of
actually making physical changes to the relational
database belongs to the Database Management
System (DBMS).
Structured Query Language (SQL)
 The SELECT statement is used to begin a query.
 The SELECT statement tells the query which columns
(or attributes) of a table should be included in the
query
 The FROM clause to the SELECT statement indicates
the name of table(s) from which to retrieve data.
 The WHERE clause states the criteria that must be
met to be shown in the query result.
 The GROUP BY operator is used with aggregate
functions on the query results based on one or more
columns.
Example: FROM

Customer # Name A/R Amount SP#

How is a query used to find
C-1 Bill 345 E-12
the salesperson for each
C-2 Mick 225 E-10
customer?
C-3 Keith 718 E-10

C-4 Charlie 828 E-99

C-5 Ron 3,200 E-10

The result is: Used the following SQL command:

Customer # Name SP# SELECT Customer#
C-1 Bill E-12
FROM Customer;

C-2 Mick E-10

C-3 Keith E-10

C-4 Charlie E-99

C-5 Ron E-10

 Example: WHERE

Remittance Amount Bank Date Custom Cashier

Advice # Account er Number
# Numbe
r
RA-1 1,666 BA-6 25-JUL-2014 C-2 E-39
RA-2 10,000 BA-7 26-JUL-2014 C-2 E-39

RA-3 72,000 BA-7 15-AUG-2014 C-1 E-39

RA-4 32,600 BA-7 15-AUG-2014 C-5 E-39

RA-5 1,699 BA-6 25-AUG-2014 C-2 E-39

How is a query used to Used the following SQL command:

retrieve information for SELECT*
Customer C-2 from the cash FROM Cash Receipt
receipt table? WHERE Customer Number =C-2

Asterisk (*) indicates all

columns should be selected
The result is:

Remittance Amount Bank Date Customer Cashier

Advice # Account Number Number
#
RA-1 1,666 BA-6 25-JUL-2014 C-2 E-39
RA-2 10,000 BA-7 26-JUL-2014 C-2 E-39

RA-4 1,699 BA-6 25-AUG-2014 C-2 E-39

Example: WHERE
Customer # Name A/R Amount SP# SP# SP_Name
C-1 Bill 345 E-12 E-10 Howard

C-2 Mick 225 E-10 E-12 Pattie

C-3 Keith 718 E-10 E-34 Stephanie

C-4 Charlie 828 E-99 E-99 David

C-5 Ron 3,200 E-10

Used the following SQL command:

SELECT Customer#,Name,SP#,SP_Name
FROM Customer, Salesperson
The result is: WHERE Customer.SP#=Salesperson.SP#

Customer # Name SP# SP_Name

C-1 Bill E-12 Pattie

C-2 Mick E-10 Howard

C-3 Keith E-10 Howard

C-4 Charlie E-99 David

C-5 Ron E-10 Howard

 Example: GROUP BY

Remittance Amount Bank Date Customer Cashier

Advice # Account Number Number
#
RA-1 1,666 BA-6 25-JUL-2014 C-2 E-39
RA-2 10,000 BA-7 26-JUL-2014 C-2 E-39

RA-3 72,000 BA-7 15-AUG-2014 C-1 E-39

RA-4 32,600 BA-7 15-AUG-2014 C-5 E-39

RA-5 1,699 BA-6 25-AUG-2014 C-2 E-39

How to query for the total cash receipt from each customer?
Example: GROUP BY

Used the following SQL command:

SELECT Customer Number , SUM(Amount)
FROM Cash Receipt
GROUP BY Customer Number :

The result is:

Customer Number Amount
C-2 13,335
C-1 72,000

C-5 32,600
Structured Query Language (SQL)
 The ORDER BY clause identifies which columns are
used to sort the resulting data. If there is no ORDER
BY clause, the order of rows returned by an SQL query
will not be defined.
 The INSERT INTO operator inserts data into a SQL
table.
 The UPDATE operator is for updating data in a SQL
table.
 The DELETE FROM operator deletes data from SQL
table.
 SELECT DISTINCT clause selects a column without
showing repetitive values.
Example: ORDER BY

Used the following SQL command:

SELECT *
FROM Cash Receipt
WHERE Customer Number =‘C-2’
ORDER BY Amount ASC:

How to query the amount of cash receipt in ascending amount

(ASC)?

The result is:

Remittance Amount Bank Date Customer Cashier

Advice # Account Number Number
#
RA-1 1,666 BA-6 25-JUL-2014 C-2 E-39
RA-5 1,669 BA-6 25-AUG-2014 C-2 E-39

RA-2 10,000 BA-7 26-JUL-2014 C-2 E-39

Example: INSERT INTO

Used the following SQL command:

INSERT INTO Cash Receipt
VALUES ( RA-6’ , 5000, ‘BA-7’ , ’28-AUG-2014’, ‘C-2’, ‘E-39’);

The result is:

Remittance Amount Bank Date Customer Cashier

Advice # Account # Number Number
RA-1 1,666 BA-6 25-JUL-2014 C-2 E-39
RA-2 10,000 BA-7 26-JUL-2014 C-2 E-39

RA-3 72,000 BA-7 15-AUG-2014 C-1 E-39

RA-4 32,600 BA-7 15-AUG-2014 C-5 E-39

RA-5 1,699 BA-6 25-AUG-2014 C-2 E-39

RA-6 5,000 BA-7 28-AUG-2014 C-2 E-39

Example: UPDATE

Used the following SQL command:

UPDATE Cash Receipt
SET AMOUNT= 6000
WHERE Remittance Advice# =‘RA-6’;

The result is:

Remittance Amount Bank Date Customer Cashier

Advice # Account # Number Number
RA-1 1,666 BA-6 25-JUL-2014 C-2 E-39
RA-2 10,000 BA-7 26-JUL-2014 C-2 E-39

RA-3 72,000 BA-7 15-AUG-2014 C-1 E-39

RA-4 32,600 BA-7 15-AUG-2014 C-5 E-39

RA-5 1,699 BA-6 25-AUG-2014 C-2 E-39

RA-6 6,000 BA-7 28-AUG-2014 C-2 E-39

Example: DELETE FROM

Used the following SQL command:

DELETE FROM Cash Receipt
WHERE Remittance Advice# =‘RA-6’;

Example: SELECT DISTINCT

Used the following SQL command:

SELECT DISTINCT Customer Number
FROM Cash Receipt
Customer
The result is: Number
C-2

C-1

C-5
Structured Query Language (SQL)
 The BETWEEN operator can be used to specify the
end points of a range.
 Membership Operator (IN) allows you to test whether
a data value matches the specified target values.
Example: BETWEEN
Used the following SQL command:
SELECT *
FROM Cash Receipt
WHERE Date BETWEEN ’01-JULY-2014’ AND ‘31-JULY-2014’;

The result is:

Remittance Amount Bank Date Customer Cashier

Advice # Account # Number Number
RA-1 1,666 BA-6 25-JUL-2014 C-2 E-39
RA-2 10,000 BA-7 26-JUL-2014 C-2 E-39
Example: MEMBERSHIP OPERATOR (IN)
Used the following SQL command:
SELECT Customer Number , SUM(Amount)
FROM Cash Receipt
WHERE Customer Number IN (‘C-1’, ‘C-2’)
GROUP BY Customer Number

The result is:

Customer
Number
Amount How to query the total cash receipt
C-1 72,000
amount from customer C-1 and C-2?
C-2 13,335
Structured Query Language (SQL
 SQL language provides several convenient aggregate
functions to be used in SQL commands. These aggregate
functions include AVG, SUM, MAX, MIN, and COUNT.
Their definitions are as follows:
 AVG(X): gives the average of column X.
 SUM(X): gives the summation of all rows the selection
criteria for column X.
 MAX(X): gives the maximum value of column X.
 MIN(X): gives the minimum value of column X.
 COUNT(X): gives the number of rows that satisfy the
given condition.
Example: MEMBERSHIP OPERATOR (IN)
Used the following SQL command:
SELECT Customer Number , SUM(Amount)
FROM Cash Receipt
WHERE Customer Number IN (‘C-1’, ‘C-2’)
GROUP BY Customer Number

The result is:

Customer Amount
Number
C-2 72,000
C-2 13,335
Six relational operators in SQL
RELATIONAL OPERATORS MEANING
1. = Equal
2. != or < > Not equal
3. < Less than
4. <= Less than or equal to
5. > Greater than
6. >= Greater than or equal to
 Enterprise Systems
 Enterprise systems
(ES), also known as
Enterprise Resource
Planning (ERP) systems,
are commercialized
information systems that
integrate and automate
business processes
across a firm’s value
chain located within and
across organizations.
Enterprise Systems
 An enterprise system uses relational data model as a
basis for the information system. The use of primary and
foreign keys links the hundreds of tables that form the basis
for the enterprise system.
 ES accommodates the integration and support of the various
business processes and information needs of a company.
 ES has a higher level of internal transparency compared to
the typically isolated legacy systems.
 Enterprise Systems
 The purported informational benefits of an enterprise
system include enhanced completeness, transparency,
and timeliness of information needed to manage
effectively an organization’s business activities.
 The enterprise system serves as the backbone of the
company’s internal business processes and serves as a
connection with the external business processes for
supply chain and customer relationship management
systems.
Challenges of
Enterprise System
Implementation

1. integrating various modules within the enterprise

system.
2. integrating with external systems such as the
information system of a supplier and/or customer.
3. integrating with the firm’s own existing legacy systems.
4. converting data from existing legacy systems to the
enterprise system, and
5. getting any big project implemented at a firm.
Enterprise Systems Computing in a
Cloud
 Cloud is a set of different types of hardware and
software that work collectively to deliver many aspects
of computing to the end-users as an online service.
 Cloud computing is the use of hardware and
software to deliver a service over a network
(internet).e.g., online banking services, social services,
Google’s Gmail
Exercises:
1. The hierarchical data model, the mapping from parent to child is
a) 1:1 (one-to-one)
b) 1:N (one-to-many)
c) N:N (many-to-many)
d) N:1 (many-to-one)
2. Advantages of relational data model generally include:
a) Flexibility and Scalability
b) Low Cost
c) Ease of Implementation
d) Most reliable
3. A class in a relational database model is defined as
a) The sum of a whole
b) Characteristics or properties of a table
c) Person, place, thing, transaction or event about which info is stored
d) Being or existence especially when considered as distinct,
independent, or self-contained
4. Which statement about enterprise system is correct?
a) Most of the enterprise systems are designed mainly for accounting and
finance functions.
b) SAP, Oracle, and Microsoft all offer products for enterprise system
c) Most enterprise systems are designed for the service industry
d) Small companies do not use enterprise systems at all
5. Refer to figure 4.2 if Steve’s Stylin Sunglasses accepts installments without
requiring a down payment, the multiplicities between Sales and Cash
Receipts should be charged to
a) Sales (0..1)- (1..*) Cash Receipts
b) Sales (1..1)- (0..*) Cash Receipts
c) Sales (1..1)- (1..1) Cash Receipts
d) Sales (0..*)- (1..1) Cash Receipts
6. The FROM clause to the select statement used in SQL indicates:
a) The name of the tables from which to retrieve data
b) The name of the columns from which to retrieve data
c) The name of the database from which to retrieve data
d) The name of the query from which to retrieve data
7. The WHERE clause to the SELECT table used in SQL states the criteria
that must be met
a) To run a query
b) To be included as an attribute in the table
c) To be included in the database
d) To be shown in the query result
8. The ORDER BY amount ASC clause to the SELECT statement used in
SQL suggests that the:
a) Amount of the query result will be listed in ascending order
b) Amount of the query result will be listed in descending order
c) The data attribute ASC be shown in order
d) None of the above
9. SAP modules available for implementation include:
a) Payroll, Personnel Time Management and Enterprise Management
b) Payroll, Financial Accounting and Enterprise Management
c) Financial Accounting, Payroll, Sales and Distribution
d) Sales and Distribution, Financial Accounting and Procurement
10. Cloud computing
a) Takes energy from the sun and clouds
b) Is internet based computing where shared resources, software and
information are provided to firms on demand
c) Requires firm to make an extensive investment in hardware and
software to meet firm needs
d) Can meet computing needs today but is not expected to meet
tomorrow’s computing needs

Answers: Answers:
1. B 6. A
2. A 7. D
3. C 8. A
4. B 9. C
5. D 10. B