Transcript Relational Databases and Enterprise Systems

Chapter 4
Relational
Databases
and
Enterprise
Systems
Copyright © 2014 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
Learning Objectives
• LO#1 Describe the advantages of relational databases.
• LO#2 Explain basic relational database principles.
• LO#3 Describe how to query using Structured Query Language
(SQL).
• LO#4 Understand the purpose and basic framework for an
enterprise system.
• LO#5 Assess how cloud computing facilitates enterprise
systems
4-2
LO# 1
Introduction of Database
• A database is a collection of related data for various uses.
Three types of data models:
• A Hierarchical data model organizes data into a tree-like
structure that allows repeating information using defined
parent/child relationships.
• A Network data model is a flexible model representing
objects and their relationships.
• A Relational data model is a data model that stores
information in the form of related two-dimensional tables.
4-3
LO# 1
Introduction of Database
• Relational data models are the dominant data model
form in use today likely because they offer many
advantages over other data models, including:
 Flexibility and Scalability
 Simplicity
 Reduced Information Redundancy
4-4
LO# 2
Definitions for Databases
• Database Management System (DBMS) – The DBMS is
defined as a computer program that creates, modifies and
queries the database. Specifically, the DBMS is designed to
manage a database’s storage and retrieval of information.
• Data Dictionary – The data dictionary describes the data
fields in each database record such as field description, field
length, field type (e.g., alphanumeric, numeric), etc.
• Database Administrator – The person responsible for the
design, implementation, repair and security of a firm’s
database.
4-5
LO# 2
Fundamentals of Relational Databases
• Entities and Attributes:
• A class in the relational database model could be a person, place,
thing, transaction, or event about which information is stored.
Classes could be grouped into resources (R), events (E), and agents
(A) in data modeling.
• Attributes are characteristics, properties, or adjectives that
describe each class.
• Keys and Relationships:
• A primary key is an attribute or combination of attributes that
uniquely identify a specific row in a table.
• A 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 acts to provide a logical relationship, or a link, between
two tables.
4-6
LO# 2
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 or
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.
4-7
LO# 2
Fundamentals of Relational Databases
• 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.
4-8
LO# 2
Fundamentals of Relational Databases
• Basic Requirements of Tables
• 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 singlevalued. 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.
4-9
LO# 2
Microsoft Access to Implement a Relational
Database
• Introduction to Microsoft Access
Access is a simple database management system that can be
used to run databases for individuals and small firms.
The Access system is composed of seven objects that are used
to implement relational databases.
----Tables are used to store data which consists of a series of
rows (records) and columns (attributes) connected by
relationships (links between tables).
----Queries are a tool used to retrieve and display data derived
from records stored within the database.
----Forms are utilized by users to enter data into tables and view
existing records.
4-10
LO# 2
Microsoft Access to Implement a Relational
Database
• Reports are used to integrate data from one or more queries
and tables to provide useful information to decision makers.
• Pages, Web-based forms, allow data to be entered into the
database in real time from outside of the database system.
• Macros are defined by users to automate processes like
opening a specific form.
• Access’s code can be altered by the use of modules.
4-11
LO# 3
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.
• 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).
4-12
LO# 3
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.
4-13
LO# 3
Structured Query Language (SQL)
• 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.
• 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.
4-14
LO# 3
Structured Query Language (SQL)
• 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 a SQL
table.
• SELECT DISTINCT clause selects a column without
showing repetitive values.
• 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.
4-15
LO# 3
Structured Query Language (SQL
Six relational operators in SQL
Relational Operators
Meaning
• =
equal
• != or <>
not equal
• <
less than
• <=
less than or equal to
• >
greater than
• >=
greater than or equal to
4-16
LO# 3
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 that satisfy 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.
4-17
LO# 4
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. Typically, 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.
In fact, for a popular ERP product (SAP/ERP) installation, there are 10,000
tables that are all linked to each other!
4-18
LO# 4
Enterprise Systems
• 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.
• 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.
4-19
LO# 4
Enterprise Systems
Challenges of Enterprise System Implementation
• integrating various modules within the enterprise system,
• integrating with external systems such as the information
system of a supplier and/or customer,
• integrating with the firm’s own existing legacy systems,
• converting data from existing legacy systems to the enterprise
system, and
• getting any big project implemented at a firm.
4-20
LO# 5
Enterprise Systems
Enterprise Systems Computing in a Cloud
• In recent years, cloud computing has emerged as a potential
alternative to host enterprise systems and other firm
computing needs.
• Cloud computing is Internet-based computing where shared
resources, software, and information are provided to firms on
demand.
• Cloud Computing can easily host enterprise system
applications.
• Cloud computing, of course, has disadvantages as well.
Ensuring that any sensitive data is secure and backed up
frequently by the host is often a concern of cloud computing
clients.
4-21