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

PlainSounder PlainSounder PlainSounder BetterBox BetterBox UltraX

Product Description

AM/FM Radio, 2 speakers AM/FM Radio, 2 speakers AM/FM Radio, 2 speakers Radio, CD, 6 Speakers Radio, CD, 6 Speakers CD, MP3, 8 Speakers

Product Price

$51 $51 $51 $123 $123 $270

Customer ID Customer Name Customer Location Quantity Sold

C1 Chrysler Corp Germany 15000 C2 Circuit City California 18400 H1 G1 F1 H2 Hyundai GM Ford Motors Honda Korea Michigan Michigan Japan UltraX CD, MP3, 8 Speakers $270 B1 BMW Germany Modification anomaly: if PlainSounder model description changes 26500 23650 14000 7500 2560  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 Product Id

001 002 003

Product Name

PlainSounder BetterBox UltraX

Product Description

AM/FM Radio, 2 speakers Radio, CD, 6 Speakers CD, MP3, 8 Speakers

Product Price

$51.00

$123.00

$270.00

Customers Customer ID

C1 C2 H1 G1 F1 H2 B1

Customer Name

Chrysler Circuit City Hyundai GM Ford Motors Honda BMW

Customer Location

Germany California Korea Michigan Michigan Japan Germany

Product ID

001 001 001 002 002 003 003

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.

primary key

: An attribute uniquely identifying each row (entity), satisfying the entity integrity conditions. Null value (no entry) is not permitted for a 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

information   – rules that help ensure the quality of

Operational integrity constraints

– rules that enforce basic and fundamental information-based constraints

Business-critical integrity constraints

operational integrity constraints – rules that enforce business rules vital to an organization’s success and often require more insight and knowledge than

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