database processing chapter 5. 5-2 gearup: “you don’t know anything about report writing”...

Database Processing

Chapter 5

5-2

GearUp: “YOU Don’t Know Anything About Report Writing”

• GearUp needs operating data to analyze for cost-cutting decisions

• Need to extract and combine data from multiple systems

• Will use Access to create reports

Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall

5-3

Study Questions

Q1: What is the purpose of a database?

Q2: What is a database?

Q3: What are the components of a database application system?

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?

Q7: What is the users’ role in the development of databases?

Q8: 2023?


5-4

Q1: What Is the Purpose of a Database?

• Organize and keep track of things• Keep track of multiple themes• General rule:

Single theme store in a spreadsheet Multiple themes require a database


5-5

A List of Student Grades, Presented in a Spreadsheet


5-6

Student Data Shown in a Form, from a Database


5-7

Q2: What Is a Database?


5-8

Components of a Database


5-9

What Are Relationships Among Rows?


5-10

Sample Metadata (in Access)


5-11

Ethics Guide: Nobody Said I Shouldn’t

1. Chris made copy of backup database, took it home

2. Queried sysTables to find table and field names

3. Found data on orders, customers, salespeople4. Discovered peculiar sales discounts5. Mentioned it to Jason (sales clerk)6. Chris fired next business day


5-12

Q3: What Are the Components of a Database Application System?


5-13

Adding a New Column to a Table (in Access)


5-14

Processing the Database

Four DBMS operations

1. Read

2. Insert

3. Modify

4. Delete


5-15

Structured Query Language (SQL)

• SQL (see-quell)

– International standard – Used by most popular DBMS

• SQL statement:– INSERT INTO Student

([Student Number], [Student Name], HW1, HW2, MidTerm)

– VALUES (1000, ’Franklin, Benjamin’, 90, 95, 100);


5-16

Summary of Database Administration Tasks


5-17

Using MIS InClass 5: How Much Is a Database Worth?

• Data has resale value• Data on everything customers do • Use to target customer for offerings they

care about, avoid those they don’t• Costly and difficult to replace data collected

over many years


5-18

Q4: How Do Database Applications Make Databases More Useful?


5-19

What Are Forms, Reports, and Queries?


5-20

Sample Query Form Used to Enter Phrase for Search and Result


5-21

Why Are Database Application Programs Needed?• Process logic specific for a business need

• Enable processing via Internet


5-22

Multi-User Processing Problem


1.Process A reads customer record from file containing customer’s account balance.

1

2.Process B reads same record from same file, now has its own copy.

2

3.Process A updates account balance in its copy of customer record and writes record to file. 3

4.Process B has original stale value for account balance. Updates customer’s phone number and writes customer record to file.

4

5.Process B writes stale account balance value to file, causing changes made by Process A to be lost.

5

5-23

Enterprise DBMS vs. Personal DBMS

• Enterprise DBMS process large organizational and workgroup databases for 100s/1000s of users

• Personal DBMS designed for smaller databases used by 1 to 100 users.


5-24

Q5: How Are Data Models Used for Database Development?


Database Development Process

5-25

What Is the Entity-Relationship (E-R) Data Model?

• Tool for constructing data models• Describes contents of data model by

defining entities and relationships among entities

• Unified Modeling Language (UML), less popular, tool for data modeling


http://en.wikipedia.org/wiki/Unified_Modeling_Language

http://en.wikipedia.org/wiki/Unified_Modeling_Language

5-26

Student Data Model Entities


5-27

Example of Department, Adviser, and Student Entities and Relationships


5-28

Sample E-R Diagrams


1:N N:M

Version 1

Version 2

Crow’s Feet

5-29

Crow’s-Foot Diagram Version


Minimum and Maximum Cardinality

5-30

Q6: How Is a Data Model Transformedinto a Database Design?


Poorly structured Employee

table

5-31

Normalizing for Data Integrity


Two normalized

tables

5-32

Summary of Normalization


5-33

Steps for Transforming Data Model Into a Database Design

1. Construct Adviser table and Student tables with key fields

2. Represent relationships by adding foreign keys

3. Create new table for N:M relationships


5-34

Transforming a Data Model into a Database Design: 1:N Relationship


5-35

Representing an N:M Relationship


5-36

Q7: What Is the Users’ Role in theDevelopment of Databases?

• Define what data database must contain

• User review of data model is crucial– Does model accurately reflect your

view of the business?

• Final approval of data models

• Devote time to do it right


5-37

Q8: 2023?

• Volume of database data to grow exponentially

• Relational databases will look much different

• Use of non-relational data stores, called NoSQL will be more common

• Major DBMS vendors lose out to open-source products and shift focus to services supporting open source software like Bigtable, Dynamo, Cassandra?

• Database models will be very different


5-38

Guide: No, Thanks, I’ll Use a Spreadsheet

• Databases take time to build

• Complicated to operate

• Need IS people to create and keep them running

• Salesman doesn’t want to share data

• Spreadsheets sometimes better option, especially if data needs are simple


5-39

Guide: Immanuel Kant, Data Modeler

• Perception of reality is based on our perceptive apparatus.

• Brain processes perceptions into something meaningful.

• Human’s model what appears to be.

• Users must determine if a data model fits their perception of reality.


5-40

Active Review

Q1: What is the purpose of a database?

Q2: What is a database?

Q3: What are the components of a database application system?

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?

Q7: What is the users’ role in the development of databases?

Q8: 2023?


5-41

Case Study 5: Fail Away with Dynamo, Bigtable, and Cassandra

• Current relational DBMS products not designed for large, multi-server systems

• NoSQL databases – Dynamo, Bigtable, Cassandra

• Amazon: Dynamo

• Google: Bigtable processes petabytes of data on hundreds of thousands of servers

• Both created to be elastic

• Cassandra used by Facebook, Twitter, Digg, Reddit


database processing chapter 5. 5-2 gearup: “you don’t know anything about report writing”...

Documents