Transcript MIS 301- Database

MIS 301
Information Systems in Organizations
Dave Salisbury
[email protected] (email)
http://www.davesalisbury.com/ (web site)
Things to know about Database




Using Access QBE to Query Relational
Databases (done)
Querying Multi-table Databases (done)
Using Logical Modeling to Create a
Relational Database
Translating a business situation into a
data model, and then into a relational
database
Databases & Data Modeling




Data as a resource
Samples of how databases are set up in
organizations
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?
Disadvantages of File Processing

Program-Data Dependence


Data Redundancy (Duplication of data)


Different systems/programs have separate copies
of the same data
Limited Data Sharing


All programs maintain metadata for each file they
use
No centralized control of data
Excessive Program Maintenance

80% of of information systems budget
Duplicate (Redundant) Data
Problems with Data Redundancy



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


Minimal Data Redundancy


Different users get different views of the data
Enforcement of Standards


Leads to increased data integrity/consistency
Improved Data Sharing


Metadata stored in DBMS, so applications don’t need to
worry about data formats
All data access is done in the same way
Improved Data Quality

Constraints, data validation rules
Modeling Reality


A database must mirror the real world if it is
to answer questions about the real world
Data Modeling is a design technique for
capturing reality
STUDENT
Social_Security_No
Name
Major
Using Logical Modeling to Create a
Relational Database





Most relational databases include many tables, not just one, to
avoid redundancy.
This redundancy can result in the database table taking up
unneeded storage space as well as causing problems when
trying to insert new records, delete existing records, or update
records.
These problems, typically referred to as anomalies, can harm
the integrity of the database records.
To create relational databases that avoid these problems with
redundancy, logical modeling is used.
The first step in logical modeling is to create an EntityRelationship Diagram (ERD) that allow us to focus on the “big
picture,” that is, the entities and the relationships.
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 which
you want to keep data.
How things you want to keep data
about are tied together.
How to depict how many of one thing is related to how many of another.
= zero
or
or
= one
or
or
= many
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
Entity-Relationship Diagramming
The symbols for an ERD are (where 1:1 means a oneto-one relationship and 1:M means a one-to-many
relationship):
Sample E-R Model
Places/
Placed by
Customer
Order
Contains/ is
Contained in
Product
Relational Databases

Relations (a.k.a. tables)




Each row is unique (entity instance)
Order is unimportant
Each column represents one thing
(attribute)
Entries are from the same domain (e.g.
student)
Relational Databases

Keys



Primary key (uniquely identifies a record)
Composite key, a.k.a. Concatenated key
(two elements combined are unique)
Foreign key (links tables/relations)
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)
M:N Relationships in Relational Model
Places/
Placed by
Contains/ is
Contained in
Order
Order Line
Customer
Product
Contains/ is
Contained in
How this looks in Access
1
OrderID
OrderDate
CustomerID
1
ORDER LINE
OrderID
ProductID
Quantity
PRODUCT
ProductID
Description
Price
Weight
Supplier
8
ORDER
8
CustomerID
FirstName
LastName
Address
City
State
Zip
Telephone
1
8
CUSTOMER