MIS 301Information Systems in Organizations

Dave Salisburysalisbury@udayton.edu (email)

http://www.davesalisbury.com/ (web site)

Things to know about Database

Translating a business situation into a logical data model

Translating a logical data model into a relational database

Using access QBE to query relational databases (in Access)

Querying multi-table databases (in Access)

Databases & Data Modeling

Data as a resource Basic logical modeling of data Basic physical modeling of data

Data Integrity Normalization

Student ROI (Return on Investment)

Your investment of time and effort in this course will result in your being able to answer these questions:

How do knowledge workers use Access QBE to query a single-table database?

How is Access QBE used to query multiple-table databases?

How do knowledge workers use logical modeling to create effective relational database systems?

How do you as a user participate in helping to build a database for your organization?

The Basics(this’ll come up again in technology)

Binary Digits (Bit) 0 or 1

Byte 8 bits (usually), combined to mean

something Coding Schemes

ASCII – American Standard Code for Information Interchange

EBCDIC – Extended Binary Coded Decimal Interchange Code

The Data Hierarchy

The data hierarchy is a way of organizing stored data in progressively larger levels of complexity

Bit Character Field Record File Database

File Processing Systems

Before the development of database systems, most organizations used file processing systems.

With file processing, each application uses it own set of files.

Even though files may be related by an application, they are not necessarily stored or managed together.

Traditional file processing problems

Program-Data Dependence All programs maintain metadata for each

file they use Data Redundancy (Duplication of data)

Different systems/programs have separate copies of the same data

Limited Data Sharing No centralized control of data

Excessive Program Maintenance As much as 80% of of information systems

budget

Duplicate (Redundant) Data

Data redundancy is bad because…

Waste of space to have duplicate data Causes more maintenance headaches The biggest Problem:

When data changes in one file, could cause inconsistencies

Compromises data integrity Lack of coordination and central control Non-standard file formats

Database

Central repository of shared data Data is managed by a controlling

agent Stored in a standardized,

convenient form Requires a database management

system (DBMS)

Advantages of Database Approach

Program-Data Independence Metadata stored in DBMS, so applications don’t

need to worry about data formats Minimal Data Redundancy

Leads to increased data integrity/consistency Improved Data Sharing

Different users get different views of the data Enforcement of Standards

All data access is done in the same way Improved Data Quality

Constraints, data validation rules

Relational Data Model The relational data model was created by E. F. Codd in

the early 1970’s. It is a method of logically storing data in a database that

is independent of the method used to physically store the data.

Relational database management systems (RDBMS) are the most popular way of storing large amounts of data.

The Relational Data Model

The relational data model stores data in one or more tables corresponding to entities.

Tables consist of records which correspond to rows in the tables.

Records store data on a single instance of an entity.

A record consists of one or more fields. Fields are often referred to as attributes

and correspond to columns of the tables.

Related Tables

Each table has a unique field called the primary key.

Tables are related via matching fields. If the primary key in one table is the

reference key to another table, it is referred to as a foreign key.

Using a Database

Designing a database and storing data in it is just the first step.

The power of a database comes from the capability to work with the data stored there.

The primary method of using a database is to query it.

The standard way of doing this for a RDBMS is to use the Structured Query Language (SQL), or for Access, a Query Language known as Query by Example (QBE).

Modeling Reality

A database must mirror the real world if it is to answer questions about the real world

Hence, we must first model reality using data modeling

STUDENT

Social_Security_NoNameMajor

Building a logical data model

Initial step to building a relational database – don’t focus on technology at this point

Identify the things an organization needs to track and how they are related

Focus on the “big picture” – things one cares about and how they are related

Entity-Relationship Modeling

One type of data modeling Entities – things you want to keep

track of Relationships – how those things

are related to one another

Notation in an ER diagram

ENTITY RELATIONSHIP

Something about whichyou want to keep data.

How things you want to keep data about are tied together.

= zero

or

= one

or

or

or = many

How to depict how many of one thing is related to how many of another.

Entity-Relationship Modeling

Relationships imply constraints on how many entities may occur on one side (or the other) of a given relationship.

Types of Relationships one-to-one 1:1 one-to-many 1:N many-to-many N:M

Sample E-R Model

Customer

Order

Product

Places/Placed by

Contains/ isContained in

The goal is a well-designed relational database

Tables Each row is unique (entity instance –

one example of a given entity) Order is unimportant (can be sorted

using queries later) Each column represents one thing

about the entity instance - attribute Entries are from the same domain (e.g.

student)

Well-designed relational databases

Multiple tables One for each class of things one cares about Avoid redundancy

Each class of things gets its own table Only information about the relevant class goes in

a table

Design to limit redundancy Redundancy leads to waste of storage space Redundancy causes modification anomalies

on inserts, deletes or updates

Data Integrity

Rules to make sure your data is valid Entity integrity constraint

Primary key cannot be null Referential integrity

Ensures valid relationships between data

Cannot add a row with no parent Cannot delete a parent without deleting

child (cascading)

Relational database keys

Primary key (uniquely identifies a record)

Composite key, a.k.a. Concatenated key (two elements combined are unique)

Foreign key (links tables/relations)

Many-to-many relationships

Can’t exist in a relational database They do, however, exist in real life How to translate them into something that

CAN be modeled in a relational database? Make an intersection table between the two

tables in a many-to-many relationship Use one element of two tables to tie them

together put in the intersection table The resulting table will either have a defined

key, or will use a combination of the keys of the two tables being tied together

M:N Relationships in Relational Model

Customer

Order

Order Line

Places/Placed by

Contains/ isContained in

ProductContains/ isContained in

How this looks in Access

CustomerIDFirstNameLastNameAddressCityStateZipTelephone

OrderIDOrderDateCustomerID OrderID

ProductIDQuantity

ProductIDDescriptionPriceWeightSupplier

CUSTOMER

ORDER

ORDERED_PRODUCT

PRODUCT1

8

1

8

1

8