Using MIS 2e

sunfloweremryologistData Management

Oct 31, 2013 (3 years and 9 months ago)

79 views

Using MIS 2e


Chapter 5: Database
Processing


David Kroenke

This presentation has been
modified from the original and
should be downloaded from the
Course Documents area in
Blackboard


Study Questions


Q1


What is the purpose of a database?


Q2


What is a database?


Q3


What are the components of a database application?


Q4


How do database applications make databases more useful?


Q5


How are data models used for database development?


Q6


How is a data model transformed into a database design?


Q6a


What is normalization and why is it necessary?


Q6b


What is the fastest way to a correct design?


Q7


What is the user’s role in the development of databases?


Q8


What are the responsibilities for database administration?


Opposing Forces Guide: “I Prefer a Spreadsheet”

5
-
2

Chapter 5: Database Processing


Every database maintains data (adds, edits, and
deletes records) in order to produce information


Various databases were developed in the
Database
Portfolio to
provide a practical (as opposed to
theoretical) appreciation of database applications


Best Realty
(A “simple” database with one table)


The

University Database
(Switchboard)


National Bank
(One
-
to
-
many relationships)


Conference Center
(Many
-
to
-
many relationships)


Computer Super Store
(Front End/Back End)


The
SD Coffee case study
illustrates the steps in
database development and is the focus of the
Competitive Design project.

Q1


What is the purpose of a database (Database Portfolio)?

5
-
3

Chapter 5: Database Processing

Q2


What is a database (Table, Record, and Field)?


An
entity

is something
you want to track of


A
field

is a
basic fact


A
record

is a set of
fields


A
table

is a set of
records


A
database

is a set of
tables


5
-
4

Chapter 5: Database Processing

Q2


What is a database (Implementing 1:N Relationships)


Primary key:

Field that
uniquely identifies a record


Foreign key:

Primary key of
another table


There is a
one
-
to
-
many
relationship
between
students and e
-
mail and
between students and visits


The primary key of the
one
table
is a foreign key in the
many table


5
-
5

Chapter 5: Database Processing

Q2


What is a database (Implementing N:M Relationships)


There is
a many
-
to
-
many
relationship
between advisors
and students


Create an additional table that
has (at a minimum) the primary
keys of the related tables. Add
additional fields as necessary
(none required in this example)


Primary key:

The field (or
combination of fields
) that
make a record unique. The
primary key of the new table is
the combination of the two
foreign keys (alternatively an
entirely new primary key may
be created)

5
-
6

Chapter 5: Database Processing

Q2


What is a database (metadata)?


A
database

is a
self
-
describing

set of tables; i.e.,
metadata

are contained within the database to describe the data.


Field Name
, and
Data Type

are
metadata.


Each field has
multiple
field

properties

that are
also considered
metadata




5
-
7

Chapter 5: Database Processing

Q3


What are the components of a database application?


Database



a self
-
describing collection of related tables


Database Management System (DBMS)



software with
which you create and retrieve data in a database


Database application



a set of forms, queries, reports,
and programs that interact with the DBMS

5
-
8

Chapter 5: Database Processing

Q3


What are the components of a database application (SQL)?


QBE

(Query by Example)


SQL

(Structured Query Language)


The Result


Query
: Find all students with Undecided major and > 30 credits

5
-
9

Chapter 5: Database Processing

A database may have one or more
applications, and each application may
have one or more users. Applications
have different purposes, features, and
functions, but they all process the same
data stored in a common database.

Each application represents a
different
front end
to a common
back end
(the inventory database)


Q4


How do database applications make databases more useful?

5
-
10

Chapter 5: Database Processing

Application programs enable
database processing over the
Internet. For this use, the
application program serves as an
intermediary between the Web
server and the database.

Q4


How do database applications make databases more useful?

5
-
11

Chapter 5: Database Processing


Enterprise DBMS


These products are for large organizations with thousands of users
and many different database applications.


Such DBMS products support 24/7 operations and can manage
dozens of different magnetic disks with hundreds of gigabytes or
more data.


IBM’s
DB2
, Microsoft’s
SQL Server
, and
Oracle

are examples of
enterprise DBMS products.


MySQL

is an open source program that is license
-
free


Personal DBMS


Such products are used for personal or small workgroup
applications that involve fewer than 100 users, and normally fewer
than 15, many with only one user.


Microsoft Access

is the only available personal DBMS.


Q4


How do database applications make databases more useful?

5
-
12

Chapter 5: Database Processing


The developers construct a logical representation known as a
data model (
Entity Relationship Diagram
) to describe the data
and the associated relationships.


The ERD is converted to a
relational database
according to the
work of
Dr. Edgar Codd.
This process is known as

database
design.


Foreign

keys

are added as necessary to implement relationships


Additional tables are created to eliminate repeating groups


A formal (iterative) process known as
normalization

ensures that
there is no redundancy in the finished design.


The
database

application

is created from the finished design by
adding required forms, queries, reports, and programs.


User involvement

is critical throughout the entire process
because the database design and associated application(s)
depends on how users view their business environment.

Q5


How are data models used for database development (Overview)?

5
-
13

Chapter 5: Database Processing

Q5


How are data models used for database development (ERD)?


A
data model (Entity Relationship Diagram or ERD)
is similar
to blueprints for a house. It’s a logical representation that
describes data and their relationships in a database.


An

entity

is something the user wants to track; e.g., Student,
Advisor, or Department.


Every entity has multiple
attributes

(
fields
) that describe its
characteristics; e.g., StudentName, HW1, or HW2


Every entity has a unique identifier known as the
primary key


Each entity type is represented by a rectangle within the ERD

5
-
14

Chapter 5: Database Processing


The
relationship

between two entities is represented by a
line connecting them.


An arrow (crow’s foot) at the end of one line represents a
one
-
to
-
many (1:N)

relationship; e.g., one department has
many advisers.


An arrow at both ends denotes a
many
-
to
-
many
relationship (N:M)
; e.g., one adviser has many students
and one student has many advisers

1:N

N:M

Q5


How are data models used for database development (Relationships)?

5
-
15

Chapter 5: Database Processing

1.
Students have many majors with a different adviser for each
major. Thus one adviser has many students and
one student
has many advisers.




2.
Students have many majors, but only one adviser in the major
department. One adviser has many students, but
each student
has only one adviser.




3.
Users

are the final judges as to what data the database
should contain and how tables are related to one another.

Q5


How are data models used for database development (User Involvement)?

5
-
16

Chapter 5: Database Processing


The crow’s
-
foot notation shows the maximum number of
entities (
maximum cardinality
) in a relationship; e.g. 1:N
(one
-
to
-
many) or N:M (many
-
to
-
many)


The minimum requirements are called
minimum cardinalities
.
A vertical line (number 1) means at least one entity is
required; an oval (a zero) means optional entry


A department is not required to have an adviser, but an adviser must
have a department


An adviser is not required to have a student, but a student must have
an adviser

Q5


How are data models used for database development (Cardinalities)?

5
-
17

Chapter 5: Database Processing

Q6


How is a data model transformed into a database design (1:N relationship)?


Each
entity

has its own
table in the database


The
primary

key

is the field
that uniquely identifies a
record


A
foreign

key

is the
primary key of another
table; it is added in order
to implement a 1:N
relationship


The primary key of the
“one” table becomes a
foreign key in the “many”
table

5
-
18

Chapter 5: Database Processing

Q6


How is a data model transformed into a database design (N:M relationship)?


Create an additional table
that has (at a minimum) the
primary keys of the related
tables. Add additional fields
as necessary (none
required in this example)


The primary key of the new
table is the combination of
the two foreign keys
(alternatively an entirely
new primary key may be
created)

5
-
19

Chapter 5: Database Processing

Overview:

Bob’s Better Burgers is a national corporation that offers franchises
for fast food restaurants. The company requires a database that will track its
restaurants, the individuals who own those restaurants (franchisees), and the
contracts that govern those restaurants.

Q6a


What is Normalization and why is it necessary (eliminate redundancy)?

There are several problems (
anomalies
) in data entry with a single table:

1.
Changes to existing records have to be made in multiple places (e.g., the
franchisee Grauer moves)

2.
Additions are difficult (e.g., can’t add a new franchisee or contract without a
corresponding restaurant)

3.
Deleting a restaurant may have unintended consequences (e.g., deleting restaurant
R5 loses data for Coulter and contract C3)

Normalization creates additional tables (one for each entity), adds foreign keys to
implement the 1:N relationships and eliminates the anomalies

5
-
20

Chapter 5: Database Processing

1.
Identify the physical entities

2.
Create the required tables

3.
Implement the relationships:

a.
There is a 1:N relationship between
franchisees and restaurants.

b.
There is a 1:N relationship between
contracts and restaurants.

4.
All problems in data entry are gone

Q6a


What is Normalization and why is it necessary (The Franchise)

5
-
21

Chapter 5: Database Processing

Q:

Who owns restaurant R4? What is
his/her phone number?

A:

Glassman (212) 458
-
5054

Q:

How many restaurants are
governed by contract C1? What
was the total initial fee?

A:

Three restaurants (R1, R2, and R4);
$750,6a0 in total

Q6a


What is Normalization and why is it necessary (The Franchise)

5
-
22

Chapter 5: Database Processing

Overview:
The registrar seeks to
implement a database that will track
students and the courses they take. The
initial design includes two tables, one
for courses, and one for students.

There is a problem with the Students
table in that the records are of different
lengths, due to the variable number of
courses taken by each student
.

Q6a


What is Normalization and why is it necessary (repeating groups)?

Normalization creates an
additional Student Course
table to eliminate the
repeating groups

5
-
23

Chapter 5: Database Processing

Q6a


What is Normalization and why is it necessary (Student Database)?

1.
Identify the physical entities

2.
Create the required tables

3.
Implement the relationships:

a.
There is a N:M relationship between
students and courses that requires
an extra table.

b.
The primary key in the new table is
the combination of Student Number,
Course Number, and Semester

4.
The repeating groups are gone

5
-
24

Chapter 5: Database Processing

Q:

Which classes did Jones take? How
many credits did he complete?

A:

Jones (student S4) took ENG16a
and MTH100 for a total of 7 credits

Q:

Which students took ACC101?

Q:

Adams (S1) and Baker (S3)

Q6a


What is Normalization and why is it necessary (Student Database)?

5
-
25

Chapter 5: Database Processing

1.
Identify the
entities

in the physical system

2.
Create the
required tables

and identify the
primary

key

(additional fields need not be added at this time)

3.
Identify and implement the relationships:

a.
One
-
to
-
many (1:N):

The primary key of the “one”
table is a foreign key in the “many” table

b.
Many
-
to
-
many (N:M):

Create additional table that has
(at a minimum) the primary keys of the related tables

4.
Complete each table by including other fields necessary
to produce the requested information

5.
Ask questions of the database to ensure the
completeness of the design

Q6a


What is the fastest way to a correct design
(The Money Slide)!


The key, the whole key, and nothing but the key”, so help me Codd”

5
-
26

Chapter 5: Database Processing

Q7


What is the user's role in the development of databases?


Your role in the database development process is to decide
what data should be included and how records should relate
to one another.


The best time to change a database structure is during the
data modeling stage. It’s easier and cheaper to change your
mind before anything is actually built. The costs of correcting
the database in time and money later on may be very high.


Each entity must contain all the data you need to do your job.


Each relationship must accurately reflect the appropriate view
of your business.


You must be the final judge of how well the database will
serve your needs. Do not go forward until the data model is
accurate.

5
-
27

Chapter 5: Database Processing


Most organizations have created a staff function called
database administration
.


Smaller organizations typically have only one person in this function;
larger organizations assign several people to an office of

database
administration.


Thus depending on the context, the letters DBA either stand for the
database administrator or for the office of database administration.


The purpose of database administration is to manage the
development, operation, and maintenance of a database so
as to achieve the organization’s objectives.


This function requires balancing conflicting goals: protecting
the database while maximizing its availability for authorized
use.

Q8


What are the responsibilities for database administration?

5
-
28

Chapter 5: Database Processing

Q8


What are the responsibilities for database administration?

5
-
29

Chapter 5: Database Processing

Opposing Forces Guide


I Prefer a Spreadsheet






I’m not buying all this stuff about databases.


“I’ve tried them and they are too complicated to set up. You have to
have professional IS people to create it and keep it running”


“A spreadsheet works just as well most of the time. When I want
something, I use Excel’s Data Filter and/or pivot tables and I can
usually get what I need.” Excel also has multiple database functions.


“Besides, I don’t really want to share my data with anyone. I work
pretty hard to develop my client list. Why would I want to give it
away?”


Advantages of Access:


Easier data entry via user forms


Report quality output


Relational databases and multiple user access!


5
-
30

Chapter 5: Database Processing

Summary


A
database

is a collection of related tables


A
Database Management System (DBMS
) is software with which
you create and retrieve data in a database


A
database application

is a set of forms, queries, and reports that
interact with the DBMS


The development process consists of:


A
data model

(Entity Relationship Diagram) describes the data and the
associated relationships in a system


Database design

is the process of converting a data model into tables,
relationships, and constraints. The data are stored in two
-
dimensional
tables according to the relational model developed by
Dr. Edgar Codd



Normalization

is a formal process that eliminates redundancy


Structured Query Language (SQL)

is an international standard for
defining and processing database data.


The
DBA

manages the development, operation, and maintenance
of a database

5
-
31

Chapter 5: Database Processing

Review: Select the Appropriate Term

1.
The primary key of another table
Foreign key

2.
A DBMS approach in which entities are presented as two
dimensional
-
tables
Relational database

3.
A program used to create, process, and administer a
database
DBMS

4.
The individual credited with the relational database approach
Dr. Edgar Codd

5.
A technique used to simplify complex databases and
eliminate data redundancy
Normalization

6.
Describes the data in a database
Metadata

7.
A set of fields

Record

8.
A set of records
Table

Table


Metadata


Foreign Key


Normalization


Relational Database


Edgar Codd


DBMS


Record

5
-
32

Chapter 5: Database Processing