Principles of Database Design

Download Report

Transcript Principles of Database Design

An Introduction to
Database Management Systems
R. Nakatsu
Why do we need a database?

Data is easier to manage







Too much data in individual files
Too many separate files
Need multiple views of data
Improved data sharing (concurrent updating)
Improved data security
Databases can better enforce data quality
Leads to better data integration
File-Processing Systems
Problems with File-Processing
Systems




Data are separated and isolated
Data are often duplicated
Incompatible data files
Program-Data Dependence
Databases are Centralized
What is a database?


A database is an integrated collection of logically
related data elements. A database consolidates
records previously stored in separate files.
A database can be viewed as the topmost element of
a data hierarchy:





database (a set of related files)
file (a collection of related records)
record (a group of logically related fields)
field (an attribute)
character (single alphabet, number, or other symbol)
Database Systems, 9th Edition
Related Terminology:
Three ways to refer to the same thing
Table
Row
Column
File
Record
Field
Relation
Tuple**
Attribute
**Rarely used terminology
Database Components
Page 26
© 2000 Prentice Hall
Types of Data
1.
2.
3.
4.
User data
Metadata
Indexes
Application Metadata
1. User Data




A table of data is called a relation
Columns are fields or attributes
Rows are specific instances
Relations must be structured properly
2. Metadata or
Data Dictionary
“a description of the structure of the database”
System tables store metadata, including:







table names
field names
primary key fields
data types
field size
data constraints (e.g., input masks, and validation rules)
relationships between tables
3. Indexes



Improve performance
Improve accessibility
(Overhead data)
Physical vs. Logical Storage


Physical Storage is how the computer actually
stores the data
Logical Storage is how the human end-user
views or perceives the data
Indexes create a logical view on your data.
4. Application Metadata
Stores the structure and format of:




forms
reports
queries
other application components
Hierarchy of Data Elements
Database Management
System (DBMS)
A DBMS (database management system) is a
set of computer programs that is used to
create, maintain, and provide controlled
access to user databases
DBMS: Main Functions

Data definition



Data manipulation


You can select which data you want, filter it, sort it, join
it with other data, summarize it.
Application Development


Define what data is stored, the type of data, how the
data is related.
You can also specify how the data can be formatted
and validated
Graphical tools to develop menus, forms, and reports
Data control

Allows you to specify security, transaction
management, backup and recovery
Databases for Competitive
Advantage: Walmart
Continuous Replenishment System:
Point of sale terminals record the bar code
whenever a customer purchases a product.
A database stores inventory data
Suppliers can access Walmart’s sales and
inventory data online using the Web.
Strategic Information Systems
Computer systems that help the organization
gain a competitive advantage. Systems can be
created to:
Explore, identify, and occupy new market
niches.
Understand the customer value chain better.
Learn faster and more deeply than
competitors.
RECAP






Why do we need a database?
File processing systems vs. databases
What is a database?
The DBMS
The four types of data
Databases for Competitive Advantage