Transcript Lecture 1

IST 220 – Intro to Databases
Lecture 1
An Overview
Course Objectives

In this course, we will learn




Fundamentals of (relational) database
Several widely used database systems
DB design and implementation
Developing simple DB applications
Textbook

Exploring Microsoft
Access 2002



Grauer & Barber
Prentice Hall
Features



A good introduction to Access components
and features
Demonstrating database design and
implementation in a step-by-step setting
Introducing how to build interactive DB
applications using macros and VBA modules
The Approach

Learn relational database through practices
 Getting started with MS Access





Convenient templates for DB design and
implementation
Easy-to-use tools
Extended functionalities
Widely used in various applications
Getting exposed to SQL with Oracle


universal standard query language
Industry strength RDBMS
Major Topics

Introductions to RDBMS and MS Access
 DB building blocks




Tables – data storage
Queries – data retrieval and manipulation (insert, delete, update)
Forms and reports – user interfaces for displaying data/info
Data modeling & RDB design (steps and rules)
 Application development using switchboards,
as well as macros & VBA modules
 SQL (using Oracle)
Class Plan & Grade Breakdown
Quizzes (5 at 20 points each)
Midterms (2 at 100 points each)
Final
Homework Assignments
In-class Labs
Projects and Presentation
100 points
200 points
150 points
150 points
200 points
200 points
What Is a Database?

To answer this question, we will discuss the
following related concepts






Data
Information
Database
Relational database
Database management system
Database applications
Data

Definition by Merriam-Webster’s


factual information (as measurements or statistics)
used as a basis for reasoning, discussion, or
calculation
Raw data

Raw data (pieces of information) may not be so
helpful without proper manipulation
Data is the set of static value you
store in the tables of the database
(You store data.)
Information

Information is



Derived from raw data
Specially prepared for certain audiences
Before useful information can be obtained


Data needs to be defined and stored in a highly
organized manner
Data should be easy to add into and retrieve from a
database
Information is data that is retrieved and organized
in a way that is meaningful to the person viewing it.
(You retrieve information.)
Database

A database is a collection of related raw data
that is well-defined and organized.

Usually with data for several subjects (entities)
within a certain application (domain)





Teams
Players
Games
Scores and technical statistics
In a relational database, data are stored in tables
each of which is designated for a specific subject
Example – Hawks Roster
Hawks Roster
2002-03 Roster
Row (or
record)
NUM
PLAYER
POS
HT
WT
DOB
FROM
YRS
3
Shareef AbdurRahim
F
6-9
230
12/11/76
California '99
6
David Andersen
F
7-0
242
6/23/80
Australia
R
6
Cal Bowdler (FA)
F
6-10
253
3/31/77
Old Dominion '99
3
4
Chris Crawford
F
6-9
235
5/13/75
Marquette '97
5
15
Emanual Davis
G
6-5
195
8/27/68
Delaware State '91
5
12
Dan Dickau
G
6-0
190
9/16/78
Gonzaga '02
R
5
Dion Glover
G
6-5
228
10/22/78
Georgia Tech '02
3
44
Alan Henderson
F-C
6-9
240
12/02/72
Indiana '95
7
1
DerMarr Johnson
F
6-9
201
5/05/80
Cincinnati '03
2
2
Nazr Mohammed
C
6-10
250
9/05/77
Kentucky '99
4
14
Ira Newble (FA)
F
6-7
215
1/20/75
Miami (Ohio) '97
2
42
Theo Ratliff
C-F
6-10
230
4/17/73
Wyoming '95
7
13
Glenn Robinson
F
6-7
230
1/10/73
Purdue '94
8
30
Mark Strickland (FA)
F
6-10
220
7/14/70
Temple '92
7
31
Jason Terry
G
6-2
176
9/15/77
Arizona '99
3
Column
(or field)
A 100-Year-Old Example
Banked for Hara Talbot
Aug
13
Adah Talbot
½ day in the afternoon
$0.50
Database Management System
•
A DBMS is a software system which takes
care of



Data definition
Data storage and retrieval
Data manipulation





Insertion
Modification
Deletion
Other functions, such as reporting
By using a DBMS, applications programmer
can focus on business logic
File-Processing Systems
X
File-Processing Systems

Records are stored in separate files
 Drawbacks



Separated and isolated data
Data duplication
Application program dependency



Read from the product file for 20 bytes starting from
location 1234, from the customer file ...
The difficulty of synchronize data changes
The difficulty of representing data in the users’
perspectives
Why DBMS?
Database Systems

DB technology was developed largely to
overcome the limitations of file systems
 Advantages



Integrated data
Reduced data duplication
Program/data independence



SELECT ProductName
FROM
Product
WHERE ProductId = ‘P010’
Easier to update
Easier representation of the users’ views
Relational Databases

The term relational is used because


In RDB, data are stored in tables.
Each table is a mathematical relation on a number
of sets
Aug
13
Adah Talbot
½ day in the afternoon
$0.50

Month {Jan, …, Dec}
Day {1, 2, …, 31}
Worker {…}
Time worked {…}
Wage {…}
Tables are related in a similar way
RDBMS Capabilities

Data definition



Attributes – columns of your tables
Data types – date, text, numeric, money, etc
Relationships – among data in different tables

Data manipulation
 Data control – permission to use data


Fans can read statistics on website
The Hawks manager can add, remove, and modify
player records
Data Definition

A database is self-describing
 Metadata – data used to define user data

or data about data
Aug
13
Adah Talbot
½ day in the afternoon
$0.50
Month
Day
Worker
Time worked
Wage
Field name
User data
Text
Number
Text
Text
Money
Data type
{Jan, …, Dec}
{1, 2, …, 31}
{…}
{…}
{…}
Valid range
Metadata, or schema
Example – Data Definition in Access
Example – Relationship in Access
Components of DB Systems
DB Users

Types of Users

End users (naïve users)


Application developers


Use programming languages w/ DB accessibility
Sophisticated users


Limited access to data, through forms or reports
Use query languages and tools
Specialized users

Write special applications, such CAD, GIS, etc
DB Administrators

DBA is the central control over the system
 DBA functions include



Schema definition and modification
Storage structure definition
Granting authorization for data access




Read only
Change (add and/or modify)
Create tables and modify schema
Routine maintenance
Uses of DBMS

A significant amount of related data



Data about different subjects
Multiple uses of these data
Data used by multiple users

Changes to data should be synchronized

Accesses to data must be controlled
Sample DB Applications

In a local area network
Sample DB Applications

In a wide
area network
or over the
Internet
Characteristics of Types of DBs
Type
Sample
Application
Number of
Concurrent
users
Typical Size
of Database
Personal
Wedding list
1
< 10 MB
Small
Business
Entertainment <25
scheduling
< 100 MB
Organizational
Licensing and
registration
Hundreds
> 1 trillion bytes
Internet
Microsoft
Press Books
Possibly
Hundreds
Any