Open Source Research Project

tutorthammy

11058_Chapter_5_Presentation.pdf

Home >Business & Finance homework help >Management homework help >Open Source Research Project

Database Processing

Chapter 5

5-2

“No, Drew, You Don’t Know Anything About Creating

Queries."

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

• AllRoad Parts operational database used to determine which parts to

consider for 3D printing.

• If Addison and Drew depend on Lucas (or IT dept) to produce reports,

they will (a) wait a long time and (b) probably not get what they want.

• Once they get data, Addison create queries and reports without help

from Lucas.

• Relying on own skills gives more freedom and better results.

• Relational database technology not appropriate for 3D parts files or

graphics used for query-by-graphics. MongoDB a better choice.

5-3

Study Questions

Q1: What is the purpose of a database?

Q2: What is a database?

Q3: What is a database management 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: 2024?

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

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 - use a database

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-5

A List of Student Grades Presented in a Spreadsheet

– Single Theme

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-6

Student Data Form With Multiple Themes

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-7

Q2: What Is a Database?

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-8

Hierarchy of Data Elements

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-9

Components of a Database

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-10

Example of

Relationships

Among Rows

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-11

Sample Metadata (in Access)

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-12

Ethics Guide: Querying Inequality?

• MaryAnn has a data mart.

• Business professional majored in HR, now "expert" in SQL.

• Uses SQL to do her job faster and better.

• What are your personal, professional, social responsibilities?

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-13

Ethics Guide: Querying Inequality? (cont’d)

• You can query databases to learn all sorts of patterns and trends.

– Be sure what answers you want before starting query.

• How strongly do you feel about social and personal responsibility,

considering your needs and those of your family?

• How important is social responsibility posture of an employer to

you? Is that something you want to add to your criteria for finding

a job?

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-14

Q3: What Is a Database Management System

(DBMS)?

• Program used to create, process, and administer a

database.

• Licensed from vendors such as IBM, Microsoft, Oracle, and

others.

• DB2 from IBM, Access and SQL Server from Microsoft,

Oracle Database from Oracle Corporation.

• MySQL - open source, license-free for most applications.

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-15

Processing the Database

Four DBMS operations

1. Read

2. Insert

3. Modify

4. Delete data

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-16

Processing the Database

• Structured Query Language - SQL (see-quell)

– International standard

– Used by most popular DBMS

INSERT INTO Student

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

VALUES (1000, ‘Franklin, Benjamin’, 90, 95, 100)

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-17

Administering the Database

• Used to set up a security system involving user accounts,

passwords, permissions, and limits for processing.

• Permissions can be limited in very specific ways.

• Backing up database data, adding structures to improve

performance of database applications, removing unwanted

data.

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-18

Summary of Database Administration Tasks

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-19

Summary of Database Administration Tasks (cont'd)

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-20

Elements of Database Applications

Forms View data;, insert new, update existing, and delete

existing data

Reports Structured presentation of data using sorting,

grouping, filtering, and other operations

Queries Search based upon data values provided by the user

Application Programs

Provide security, data consistency, and special

purpose processing, e.g., handle out-of-stock

situations

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-21

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

• Data has resale value.

• Data on everything customers do.

• Use to target customers for offerings they care about, and

avoid those they don’t.

• Costly and difficult to replace data collected over many

years.

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-22

Q4: How Do Database Applications Make Databases

More Useful?

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-23

Example of a Student Report

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-24

Query Example

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-25

Browser Forms, Reports, Queries, and Applications

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-26

Account Creation Browser Form

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-27

Browser

Report

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-28

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

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

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

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

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

Multi-User Processing Problem

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-29

Q5: How Are Data Models Used for Database

Development?

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-30

• Something users want to track

• Order, customer, salesperson, item, volunteer, donation

Entities

• Describe characteristics of an entity

• OrderNumber, CustomerNumber, VolunteerName, PhoneNumber

Attributes

• Uniquely identifies one entity instance from other instances

• Student_ID_Number Identifier

Components of the Entity-Relationship Data Model

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-31

Student Data Model Entities

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-32

Example of Department, Adviser, and Student Entities

and Relationships (cont'd)

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-33

Sample of Relationships―Version 1

Crow’s

Feet

N:M

An Adviser ma have many

students, and one student

may many advisers

1:N

One department can have

many advisers, but an

adviser may be in only one

department

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-34

Sample of Relationships─Version 2

1:N

A student has only one

advisor, but an adviser

may advise many

students

N:M

A department has many

advisors, and an advisor

may advise for more

than one department

“Crow’s

Foot”

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-35

Crow’s-Foot Diagram Version

Maximum cardinality─maximum number of entities involved in a

relationship. Vertical bar on a line means that at least one entity

is required.

Minimum cardinality—minimum number of entities in a

relationship. Small oval means entity is optional; relationship

need not have an entity of that type.

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-36

Q6: How Is a Data Model Transformed into a

Database Design?

• Normalization

– Converting poorly structured tables into two or more well- structured tables.

• Goal

– Construct tables with data about a single theme or entity.

• Purpose

– To minimize data integrity problems.

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

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

5-37

Data Integrity Problems

• Data integrity problems produce incorrect and inconsistent

information, users lose confidence in information, and the

system gets a poor reputation.

• Can only occur if data are duplicated.

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-38

Poorly Designed Employee Table Causes Data

Integrity Problem

C o p y r i g h t © 2 0 1 5 P e a r s o n E d u c a t i o n , I n c .

5-39

Two Normalized Tables

Single

Themes

5-40

Summary of Normalization

5-41

Representing 1:N Relationships

5-42

Representing an N:M Relationship: Strategy for

Foreign Keys

5-43

Q7: What Is the Users’ Role in the Development of

Databases?

• Users are the final judges of:

– What data database should contain.

– How tables should be related.

• Users review data model to ensure it accurately reflects

users’ view of the business.

 Mistakes will come back to haunt them.

5-44

Q8: 2024?

• Volume of database data will continue to grow, probably

exponentially.

• Cheap, unlimited storage space and greater processing

speeds do in relational databases.

• Relational model is not needed, not natural fit with business

documents.

• Store new types of data such as images, audios, and videos.

5-45

Q8: 2024? (cont'd)

• More reliance on open source community.

• Many NoSQL databases will exist.

• New opportunities and career paths will develop around

NoSQL databases.

5-46

Q8: 2024? (cont'd)

NoSQL DBMS (Not Relational DBMS)

Supports very high transaction rates.

Relatively simple data structures.

Replicated on many servers in the cloud.

• Examples

Dynamo (Amazon)

Bigtable (Google)

Cassandra (Facebook)

5-47

Q8: 2024? (cont'd)

• Will NoSQL Replace Relational DBMS Products?

– Conversion very expensive and disruptive.

– Very technical, limited to those with a deep background in

computer science.

– Requires years of training to use.

– Organization may choose NoSQL products for specialized

applications.

5-48

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

Construct a data model and have users validate it.

• Design database.

• Implement database, and fill it with data.

• Design, build, and test database applications.

• Write procedures.

• Train users.

• Maintain system.

5-49

Guide: No, Thanks, I’ll Use a Spreadsheet (cont'd)

• 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-50

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-51

Active Review

Q1: What is the purpose of a database?

Q2: What is a database?

Q3: What is a database management 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: 2024?

5-52

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.

5-53