Transcript Document

Database Concepts
Introduction
7/22/2015
Database Concepts
1
Your Instructor
• Meg McManus
• My Background
– 15 years in Database Administration
– 3 years in Computer Operations
– 2 years at UWF as Computer Science
Instructor in Software Engineering
– 8 years at OWC as Computer Science
Instructor
7/22/2015
Database Concepts
2
Syllabus
•
•
•
•
•
How to reach me
Purpose of course
Textbook
Grading
Policies
– Attendance
– Allergies
• Schedule
7/22/2015
Database Concepts
3
http://owcc-r-06.owc.edu/business/McManus/index.html
Course Website includes all
PowerPoint Presentations
Homework Assignments
Reference Material
Test Reviews
7/22/2015
Database Concepts
4
Get your facts first, and
then you can distort them as
much as you please.
Mark Twain
7/22/2015
Database Concepts
5
Chapter 1
Database Environment
7/22/2015
Database Concepts
6
Data vs. Information
• Data
– Raw material
• Examples: PayRate, HoursWorked, PayPeriod,
Commission Rate, SSN, Fname, Lname
• Information
– Data processed to increase knowledge in the
person using the data
– In other words…the Meaningful output
• The Paycheck!
7/22/2015
Database Concepts
7
Database
• In the strictest sense –
– Organized collection of logically related
data
– Includes that which provides meaning in
the user’s environment
• Structured: numbers, text, dates
• Unstructured: images, sound, video segments
and documents
7/22/2015
Database Concepts
8
Database
• Organized
– Means that the data is
• Easily stored
• Easily manipulated
• Easily retrieved by users
• Related
– Means that the
• data describe a domain of interest to a group of users
• users can use the data to answer questions concerning
that domain
7/22/2015
Database Concepts
9
Context of Data
Class Roster
Course:
CGS2541
Semester:
Fall, 2006
CourseName:
Database Concepts
RefNo:
46409
Name
ID
Major
GPA
Mouse, Mickey
SID-01
IT
4.00
Mouse, Minnie
SID-02
Networking
4.00
Mulder, Fox
SID-03
CIS
4.00
Scully, Dana
SID-04
IT
4.00
Kirk, James T.
SID-05
IT
4.00
Reynolds, Malcolm
SID-06
CIS
4.00
Tam, River
SID-07
EE
4.00
Crichton, John R.
SID-08
CIS
4.00
7/22/2015
Database Concepts
10
Meaningful Information
Percent by Major (2005)
IT
37%
CIS
37%
Networking
13%
7/22/2015
EE
13%
Database Concepts
11
Metadata
• Data that describe the properties or
characteristics of other data such as
– Name
– Data Type
– Length, Min and Max values
– Description
– Source
7/22/2015
Database Concepts
12
Metadata Example
Name
Course
Data Item
Type
Alphanumeric
Reference Integer
Length
30
1
Value
Min Max
Description
Course ID and
name
Reference
1
9 Number
Semester and
year
Semester Alphanumeric
10
Name
Alphanumeric
30
ID
Integer
9
Major
Alphanumeric
4
GPA
Decimal
3 0.00
7/22/2015
Student name
Source
Academic Unit
Registrar
Registrar
Student
Student ID (SSN) Student
Student major
Student grade
4.00 point average
Database Concepts
Student
Academic Unit
13
File Processing
7/22/2015
Database Concepts
14
History
• Original method
– for storing and
– manipulating data
• Outmoded
– given complexity of current data and user
requirements
• Replaced
– by database processing systems
7/22/2015
Database Concepts
15
Disadvantages of File Processing
• Program-Data Dependence
– All programs maintain metadata for each
file used
• Duplication of Data
– Different systems/programs have separate
copies of same data
• Limited Data Sharing
– No centralized control of data
7/22/2015
Database Concepts
16
More Disadvantages of File
Processing
• Lengthy Development Timing
– Programmers must design file formats from
scratch
• Excessive Program Maintenance
– 80% of information systems budget
– When 60% is considered normal for
maintenance phase…
7/22/2015
Database Concepts
17
Data Dependency Issues
• Data maintained by each programmer
• Each application program requires code
for each file’s metadata
• Each application program requires own
processing routines
– for reading, inserting, updating and deleting
data
• Lack of coordination and central control
• Non-standard file formats
7/22/2015
Database Concepts
18
File Vs. Database Processing
File-oriented Information System
Customer
Records
Customer
Service
Dept.
Payroll
Records
Payroll
Dept.
Employee
Records
Personnel
Dept.
Inventory
Records
Purchasing
Dept.
Database-oriented Information System
Consolidated
Customer
Purchasing
Database
Service
Dept.
Dept.
Payroll Dept.
7/22/2015
Personnel Dept.
Database Concepts
19
Data Redundancy Issues
• Wastes space with
duplicate data
• Maintenance Migraines
• Compromises data integrity
– When data is changed in one
file, not necessarily changed
in all relative files
7/22/2015
Database Concepts
20
The Problem was
Information Processing…
The Solution?
Database Processing
7/22/2015
Database Concepts
21
Database Processing
• Provides
– Central repository of shared data
– Data is managed by a controlling agent
– Data is stored in a standardized,
convenient format
7/22/2015
Database Concepts
22
Database Processing Example
Database Information System
Consolidated
Database
Customer
Service
Dept.
Payroll Dept.
Purchasing
Dept.
Personnel Dept.
Database Management System
Customer Service Dept.
Metadata
Payroll Dept.
Personnel Dept.
Database
Applications
Purchasing Dept.
7/22/2015
Database Concepts
DBMS
Customers
Payroll
Employees
Orders
23
Database Processing Advantages
• Creates program-data independence
– Data stored in repository
– Allows organization to change and evolve
• Minimizes data redundancy
– Reduces wasted space
• Improves data consistency
– Data is updated once instead of in each file
• Improves data sharing
– Create different user views for different parts of
the data
7/22/2015
Database Concepts
24
Database Processing Advantages
• Improves data quality
– Uses constraints on data
• Enforcement of standards
– Naming conventions
– Data quality standards
– Uniform procedures of accessing, updating and
deleting data
– Lack of control is one of the most common
sources of failures in database administration
7/22/2015
Database Concepts
25
Database Processing Advantages
• Increases application development
productivity
– Reduces time and cost in development
• Improves data accessibility and
responsiveness
– End users have greater access to data
• Reduces program maintenance
– Can make changes
• Improves decision support
7/22/2015
Database Concepts
26
Risks using Database Processing
• Requires specialized, often new
personnel trained in the database
program
• Installation & Management costs and
complexity
– Requires conversion costs when using
legacy data
• Requires explicit backup & recovery
• Organizational conflicts
7/22/2015
Database Concepts
27
Components of the
Database Environment
• CASE Tools
– computer-aided software engineering
• Repository
– centralized storehouse of metadata
– Including data definitions, data
relationships, screen and report formats
7/22/2015
Database Concepts
28
Components of the
Database Environment
• Database Management System (DBMS)
– software for managing the database
including control over access to data
• Database
– storehouse of the data
– Designed to meet the needs of multiple
users
7/22/2015
Database Concepts
29
Data Models
• Capture the nature of and relationships
among data
• Used at different levels of abstraction
– During conceptualization
– During design
7/22/2015
Database Concepts
30
Components of the
Database Environment
• Application Programs
– software used to create and maintain the
database and provide information to the user
• Database Administrators
– personnel responsible for maintaining the
database
• System Developers
– personnel responsible for designing databases
and software
7/22/2015
Database Concepts
31
Components of the
Database Environment
• User Interface
– Languages, text and graphical components
designed to interact with various users
• End Users
– people who use the applications and
databases
– All requests for data are routed through the
DBMS
7/22/2015
Database Concepts
32
Components of the Database
Environment
7/22/2015
Database Concepts
33
Database Systems Evolution
•
•
•
•
•
•
•
•
Flat files - 1960s - 1980s
Hierarchical – 1970s - 1990s
Network – 1970s - 1990s
Relational – 1980s - present
Object-oriented – 1990s - present
Object-relational – 1990s - present
Data warehousing – 1980s - present
Web-enabled – 1990s - present
7/22/2015
Database Concepts
34
7/22/2015
Database Concepts
35
Database Elements
•
•
•
•
•
Enterprise Data Model
Relational Databases
Entities
Use of Internet Technology
Database Applications
7/22/2015
Database Concepts
36
Enterprise Data Model
• Is a graphical model
• Demonstrates
– High-level entities
– Relationships for the organization
• Uses data warehouses
– Collection of historical data
– Aids in identifying patterns and trends
7/22/2015
Database Concepts
37
Relational Databases
• Is a database technology that uses
common keys to relate entities
• Includes:
– Tables
– Relationships between entities
– Types of keys representing relationships
• Examples: Primary and Foreign
7/22/2015
Database Concepts
38
Entities
• A person, place, object, event, or
concept
• Exists within the user’s environment
• Of interest to management
7/22/2015
Database Concepts
39
Relationships
• The relationship between entities so
that useful information can be retrieved
• Types
– 1 to 1
– 1 to Many
– Many to Many
7/22/2015
Database Concepts
40
Use of Internet Technology
•
•
•
•
•
Networks
Telecommunications
Distributed databases
Client-server
3-tier architectures
7/22/2015
Database Concepts
41
Database Applications
• Are application programs
• Used to perform database activities
– Create
– Read
– Update
– Delete
• For Users
7/22/2015
Database Concepts
42
Pine Valley Furniture Company
Product
Customer
Places
Has
Is placed by
Is for
Contains
Order
7/22/2015
Is contained in
Database Concepts
Order Line
43
Pine Valley Furniture Company
Customer
Places
Is placed by
One customer
may place
many orders, but
each order is placed
by one customer.
This creates a
One-to-many
relationship
Product
Has
Is for
Contains
Order
7/22/2015
Is contained in
Database Concepts
Order Line
44
Pine Valley Furniture Company
Customer
Places
One order has many
order lines, but each
Product
order line is associated
with a single order.
Has
This creates a
One-to-many relationship
Is placed by
Is for
Contains
Order
7/22/2015
Is contained in
Database Concepts
Order Line
45
Pine Valley Furniture Company
Customer
Places
One product can be in
many order lines, but
each order refers to a
single product.
This creates a
One-to-many
relationship
Is placed by
Product
Has
Is for
Contains
Order
7/22/2015
Is contained in
Database Concepts
Order Line
46
Pine Valley Furniture Company
Customer
Places
Each order involves many
Product
products and one product
is involved in many orders.
Has
This creates a
Many-to-many relationship
Is placed by
Is for
Contains
Order
7/22/2015
Is contained in
Database Concepts
Order Line
47
Pine Valley Relationship
• Relationship
between
Order_line and
Orders showing
one-to-many
relationship
• Example from
Microsoft Access
7/22/2015
Database Concepts
48
Relationships established in special columns that provide
links between tables
7/22/2015
Database Concepts
49
Client/
Server
System
Architecture
7/22/2015
Database Concepts
50
Summary of Database
Applications
Adapted from White, 1995
7/22/2015
Database Concepts
51
Typical
Data from
a Personal
Database
7/22/2015
Database Concepts
52
Workgroup Database with
Local Area Network
7/22/2015
Database Concepts
53
An
Enterprise
Data
Warehouse
7/22/2015
Database Concepts
54
Homework Assignment
• Read Chapters 1 & 2
• Homework Assignment 1
• Read Project Case
– Become familiar with Mountain View Project
• Copy MVCH2.mdb file
– Rearrange Relationships window
• No line overlaps
• All fields within tables visible
• Print Relationships Report
7/22/2015
Database Concepts
55
7/22/2015
Database Concepts
56