Transcript Relational Databases

IS605/606: Information Systems
Instructor: Dr. Boris Jukic
Relational Database Systems in
Enterprise Information Systems
File systems


File systems rely on coded file management programs to access,
insert and modify their content
As such, file systems are plagued by two main problems
 Structural dependence refers to the fact that if a file structure is
changed (such as deletion or addition of a field), the related file
management programs have to be modified accordingly
 Data dependence refers to the fact that the changes in data
characteristics, such as changing a field from integer to decimal
(or even just changing the length of the field), will cause the
related file management programs to be changed
 Finally, there is a problem of data redundancy
Data Redundancy

When the same data is stored in more than
one location (in multiple files or multiple fields
within one file) It may lead to:

Data integrity (inconsistency) problems


may be caused by either data entry errors or failure to
update all multiple copies of the same data
Data anomalies: modification, insertion and
deletion
Data Redundancy in File Systems and
Resulting Anomalies
Product ID
001
001
001
002
002
003
003


Product Name Product Description
Product Price Customer ID Customer Name Customer Location Quantity Sold
PlainSounder AM/FM Radio, 2 speakers
$51
C1
Chrysler Corp
Germany
15000
PlainSounder AM/FM Radio, 2 speakers
$51
C2
Circuit City
California
18400
PlainSounder AM/FM Radio, 2 speakers
$51
H1
Hyundai
Korea
26500
BetterBox
Radio, CD, 6 Speakers
$123
G1
GM
Michigan
23650
BetterBox
Radio, CD, 6 Speakers
$123
F1
Ford Motors
Michigan
14000
UltraX
CD, MP3, 8 Speakers
$270
H2
Honda
Japan
7500
UltraX
CD, MP3, 8 Speakers
$270
B1
BMW
Germany
2560
Modification anomaly: if PlainSounder model description changes
Insertion Anomaly: if a new customer (Toyota for example) is
added to the list of those who buy BetterBox product
Database Systems

Database Systems achieve data independence and
structural independence



If data type of as filed is changed or a field is eliminated or
a new one added, the existing management programs
(queries) do NOT have to be modified
If properly designed, databases have a low level of
redundancy, eliminating most of the insertion,
deletion and modification anomalies
Logically related data instead of physically
separated and unrelated files
Four (Logical) Data Models

Hierarchical Model (Legacy)


Standard tree-like structure
Network Model (Legacy)

More than one parent allowed

Relational Model

First truly data and structurally independent model
 No predetermined navigational maps as in two older
models
 The Database technology of choice
Object Model


Tables become objects
RELATIONAL DATABASE
ADVANTAGES

Database advantages from a business
perspective include





Increased flexibility
Increased scalability and performance
Reduced information redundancy
Increased information integrity (quality)
Increased information security
Relational Database Management System


In RMDBS, all data appears to be stored in a
collection of tables (or relations), which are
independent of one another, but can be
linked through common entries in one of the
tables' columns or fields (controlled
redundancy)
Relational Schema: The graph depicting
relationship types between tables
Relational Schema
Reduced Data Redundancy
Products
Customers
Product Id
Product Name
Product Description
Product Price
001
PlainSounder
AM/FM Radio, 2 speakers
002
BetterBox
Radio, CD, 6 Speakers
$123.00
003
UltraX
CD, MP3, 8 Speakers
$270.00
Customer
ID
Customer
Name
Customer
Location
Product
ID
C1
Chrysler
Germany
001
C2
Circuit City
California
001
H1
Hyundai
Korea
001
G1
GM
Michigan
002
F1
Ford Motors
Michigan
002
H2
Honda
Japan
003
B1
BMW
Germany
003
$51.00
Tables in RDBMS





Tables: Logical constructs containing individual entity sets.
Tables are always two-dimensional: rows and columns
 each row represents a single entity (or entity instance) from
the entity set
 each (uniquely named) column represents one attribute
 each row-column intersection results in a single data value
Each table must have a primary key : An attribute uniquely
identifying each row (entity), satisfying the entity integrity
conditions. Null value (no entry) is not permitted for a
primary key.
The order of rows and columns within the table is irrelevant
Foreign Key is an attribute in one table whose values must
either match the value of a primary key in another table or
be set to null (no value). These conditions are known as
referential integrity constraint.
Relationships within the relational
database:
•
•
One-to-one relationships:
One-to many relationships:
•
Examples:
•
•
•
professor - class
department - employee
Many to-many relationships
•
•
Examples: parts – product, student – class, …
it is recommended to break it into a set
(usually two) of one-to-may relationships
through a so called composite (bridge) entity
Enterprise data planning
•
A large component of the business
informational needs can be captured by
the mapping of all entities the
organizations need to keep track of and
the relationships among them
•
E-R (Entity-Relationship) modeling is a
standard technique that provides a simplified
picture of the relationship among entities.
Keys and Relationships: transaction
processing system example
RELATIONAL DATABASE
ADVANTAGES

Database advantages from a business
perspective include





Increased flexibility
Increased scalability and performance
Reduced information redundancy
Increased information integrity (quality)
Increased information security
Increased Flexibility

A well-designed database should:



Handle changes quickly and easily
Provide users with different views
A database has only one physical view


Physical view – deals with the physical storage of
information on a storage device such as a hard disk
A database can have multiple logical views

Logical view – focuses on how users logically access
information to meet particular business needs
Increased Scalability and Performance

A database must scale to meet increased
demand, while maintaining acceptable
performance levels


Scalability – refers to how well a system can
adapt to increased demands
Performance – measures how quickly a system
performs a certain process or transaction
Reduced Information Redundancy

Databases reduce information redundancy


Redundancy – the duplication of information or
storing the same information in multiple places
Inconsistency is one of the primary problems
with redundant information
Increase Information Integrity
(Quality)

Information integrity – a measure of the quality of information

Integrity constraints – rules that help ensure the quality of
information
 Operational integrity constraints – rules that enforce
basic and fundamental information-based constraints
 Business-critical integrity constraints – rules that
enforce business rules vital to an organization’s success
and often require more insight and knowledge than
operational integrity constraints
Increased Information Security

Information is an organizational asset and
must be protected

Databases offer several security features
including:



Passwords – provide authentication of the user
Access levels – determine who has access to the
different types of information
Access controls – Determine types of user
access, such as read-only access
DATABASE MANAGEMENT
SYSTEMS

Database management systems (DBMS) –
software through which users and application
programs interact with a database
INTEGRATING INFORMATION
AMONG MULTIPLE DATABASES

Organizations typically maintain multiple
systems, each with its own database

Integration – allows separate systems to
communicate directly with each other
INTEGRATING INFORMATION
AMONG MULTIPLE DATABASES

Forward integration – takes information entered into a given system
and sends it automatically to all downstream systems and processes
INTEGRATING INFORMATION
AMONG MULTIPLE DATABASES

Backward integration – takes information entered into a given system
and sends it automatically to all upstream systems and processes
INTEGRATING INFORMATION
AMONG MULTIPLE DATABASES

Building a
central
repository
specifically
for
integrated
information