databases mgmt 661 - summer 2012 night #4, lecture part 1 based on textbook chapter 6

14
Databases Databases MGMT 661 - Summer 2012 Night #4, Lecture Part 1 Based on textbook Chapter 6

Upload: theodore-king

Post on 02-Jan-2016

215 views

Category:

Documents


1 download

TRANSCRIPT

Page 1: Databases MGMT 661 - Summer 2012 Night #4, Lecture Part 1 Based on textbook Chapter 6

DatabasesDatabasesMGMT 661 - Summer 2012

Night #4, Lecture Part 1

Based on textbook Chapter 6

Page 2: Databases MGMT 661 - Summer 2012 Night #4, Lecture Part 1 Based on textbook Chapter 6

Why study databases?Why study databases?databases are the central

component of TPS, CRM, etc.

Data organization impacts information quality.

Page 3: Databases MGMT 661 - Summer 2012 Night #4, Lecture Part 1 Based on textbook Chapter 6

Old Style Data Old Style Data ManagementManagement

ApplicatioApplicationn

SoftwareSoftware

Data

File

Data

File

Data

File

ApplicatioApplicationn

SoftwareSoftware

DataFile

DataFile

Many "legacy systems"still work this way.

Page 4: Databases MGMT 661 - Summer 2012 Night #4, Lecture Part 1 Based on textbook Chapter 6

textbook Figure 6-1

Terms to Know:•Field / Attribute•Record•Field Type

Page 5: Databases MGMT 661 - Summer 2012 Night #4, Lecture Part 1 Based on textbook Chapter 6

Problems with File-based Problems with File-based SystemsSystems

Data RedundancyData Inconsistency

◦different values in different files◦different names for attributes◦different attributes types for the same

attributeProgram-data Dependence

◦zip codes◦Y2K

Lack of Data Sharing

textbook p 211-212

Page 6: Databases MGMT 661 - Summer 2012 Night #4, Lecture Part 1 Based on textbook Chapter 6

Database Management Database Management SystemSystem

DatabaseDatabase

ApplicationApplication

ApplicatioApplicationn

ApplicationApplication

ApplicatioApplicationn

Page 7: Databases MGMT 661 - Summer 2012 Night #4, Lecture Part 1 Based on textbook Chapter 6

The DBMS uncouples programs and data

Relational Database◦data organized as 2D "Tables", not text

Files

Example DBMS◦Big Systems

Oracle Database Microsoft SQL Server

◦Small Systems MySQL

◦Single Computer Microsoft Access

Page 8: Databases MGMT 661 - Summer 2012 Night #4, Lecture Part 1 Based on textbook Chapter 6

Database DesignDatabase DesignTerms to KnowTableRecord

◦ info about an entity (eg a supplier)

Primary Key◦ must be unique

Foreign Key◦ used to link to

another table

Data Dictionary◦ what is this field◦ read/write

permissionstextbook Figure 6-4

Page 9: Databases MGMT 661 - Summer 2012 Night #4, Lecture Part 1 Based on textbook Chapter 6

Database DesignDatabase Design

Page 10: Databases MGMT 661 - Summer 2012 Night #4, Lecture Part 1 Based on textbook Chapter 6

Database DesignDatabase Design

securitysystem

sensor

monitors

enables/disables

tests

programsis programmed by

securitysystem

sensor

monitors

enables/disables

tests

programsis programmed by

Page 11: Databases MGMT 661 - Summer 2012 Night #4, Lecture Part 1 Based on textbook Chapter 6

SQLSQL - - Structured Query Structured Query LanguageLanguageWhich suppliers are in Ohio?SELECT Supplier_Name from Supplier WHERE Supplier_State = "OH";

Page 12: Databases MGMT 661 - Summer 2012 Night #4, Lecture Part 1 Based on textbook Chapter 6

Web-Based DatabasesWeb-Based Databases

textbook Figure 6-16

Page 13: Databases MGMT 661 - Summer 2012 Night #4, Lecture Part 1 Based on textbook Chapter 6

Misc Database TermsMisc Database TermsDistributed DatabasesData WarehouseOnline Analytical Processing

(OLAP)Data Mining

◦web mining◦text mining

Data Scrubbing

Page 14: Databases MGMT 661 - Summer 2012 Night #4, Lecture Part 1 Based on textbook Chapter 6

HomeworkHomeworkDesign the tables for a small pizza delivery shop. Design?

number of tables? fields in each table? primary keys?

Uses for this information?