Slides Topic 4

Download Report

Transcript Slides Topic 4

Topic 4
Databases
Text Materials
Chapter 3 – Databases and Data Warehouses
Concepts
•Data should be:
- Available
- Up-to-date/current
- Error free
- Accessible
- Useful
- Secure
- Well organized
- Not redundant
Types of data, Traditional
- Customer
- Billing
- Assets
- Liabilities
- Payroll
- Sales
- Payables due
- Inventory
- Customer payment history
Types of data, Multimedia
- Pictures of products
- Training videos
- Web pages
- Product views and instructions
Data
Convenience Stores
Benefits
Departmental Database Use
• Finance and Accounting
• Human Resources
• Manufacturing, Production, Operations
• Marketing and Sales
Before Databases
?
Issues  inefficient, costly, inaccurate, slow, inflexible.
A Database is . . .
A collection of related files containing records on people, places, or
things.
•
DBMS - Software that gives the ability to create, manipulate, and
access data.
- Used to process transactions and store business information.
•
RDBMS – Relational, data is contained in Tables that are easily related to each
other. Today most popular type of DBMS. Our focus.
•
Oracle, IBM DB2, SQL, Access
Some Database Terminology
Table – A grouping of rows and columns that contain data about
something.
The rows are called records which are information about a
single item in a table.
The columns are called fields which are a single characteristic
of a record.
Common fields are used to connect records that are in
separate tables.
Concepts
RDBMS
Operating
System
Database
A Table, Records, and Fields
Database Basic Functions
• Create, Maintain, and Update Data
(such as employees, transactions, or products)
• Interrogate (called a Query)
• Generate Reports
• Build applications
An Access Table
An Access Form
An Access Report
Types of Table Relationships in a RDBMS
• One-to-Many (1:n)
One record in the first table matches many records in the second table, and one
record in the second table matches back to exactly one record in the first table.
• One-to-One (1:1)
One record in the first table matches one record in the second table, and one
record in the second table matches back to exactly one record in the first table.
• Many-to-Many (n:n)
One record in the first table matches many records in the second table, and one
record in the second table matches back to many records in the first table.
Examples:
Students and Professors
Departments and Professors
Authors and Books
Students and Cars
Employees and Desks
Customers and Orders
Object-Oriented Databases
Data Warehouses
Data Warehouses
Challenges in Data Resource Management
• Vast amounts of data
• Secure
• Available
• Back up / Archive
• Easy to use
• Handle growth
• Specialists
Trips Relational Database – A simple example
Trips Relational Database – A simple example
Trips Relational Database – A simple example
Trips Relational Database – A simple example
Database Query Results
Class Exercise
Go to the instructor website www.uta.edu/faculty/weltman and
download the rafting trip database.
Use the database and QBE (query by example) tool to show all
our Colorado trips.
Print out and turn in your query results.
Class Exercise
Class Exercise
Class Exercise
Online Analytical Processing - OLAP