Learning Objectives

1
Learning Objectives

• overview of database concepts
• introduction to SQL (structured query language)
• introduction to Microsoft Access
• - how to create a database
• - how to enter data into a table
• - how to extract information from a database
• (query the database)

2
Database Approach
Program 1
Program 2
Program n
Database management systems
Database
3
Advantages of Database Approach

•    reduced data redundancy
•    improved data integrity
•    data/program independence
•    better access of data
•   improved data sharing

4
DISADVANTAGES OF DATABASE APPROACH

• high cost database management systems
• specialized staff
•  increased vulnerability

5
Database Approach
Program 1
Program 2
Program n
Database management systems
Database
6
Data Models

• Defined a conceptual scheme to organize data
  into a
• coherent structure
• Three data models
• Tree (hierarchical) data model
• Network data model
• Relational data model

7
a sample relational database (a collection of
free-standing files)
invoice
customer
invoice line item
part
8
Relational Data Model

• flat file structure (attributes fixed-length,
  single-valued)
• relationships represented by common attributes

Payroll (Name, Dpt, Salary) Elam MIS
90,000 Smith Accounting
88,000 Class (Name, Course, Time) Elam
MIS101 T200PM Smith MBA881
W900AM
Join the two files (by common attributes) Newfile
(Name, Dpt, Salary, Course, Time) Elam
MIS 90,000 MIS101 T200PM
Smith Accounting 88,000 MBA881
W900AM
9
Database Approach
Program 1
Program 2
Program n
Database management systems
Database
10
Database Management Systems

• Defined software managing the database
• A DBMS implements a data model
• Access, Oracle, Dbase relational data
  model
• Components of a DBMS
• - Data description language (DDL) describe
  database
• schema
• - Query language (data manipulation
  language)
• - Programming language (optional)

11
DDL for a relational DBMS

• Simple to describe a relational database schema
• Sample database schema

Student (SID, Name, Telephone) Grade (SID, CID,
Grade) Course (CID, Title, Time, Place)

• Specify file name
• For each file, specify attribute names, types,
  and sizes

12
Attribute Types in Access

• text character field of fixed length (up to
  255 characters)
•   memo character field of variable length (up
  to 65,535 characters)
•   number integers or real numbers
•   date/time
•   currency
•   autonumber integers that automatically
  increment for each
• new record
•   yes/no logical yes or no, true or false
•  

13
Database terminology

• a database is a collection of files
• a file is also called a table or a relation
• a file consists of one or more records
• a record is made of attribute (field) values
• each attribute value is atomic or single-valued
• a record is identified by attribute value(s)
• primary key attribute whose value uniquely
• identifies a record

14
customer
Name Street Address City
State Zip phone Tax Status
C_LIMIT CU_BALANCE AAA OIL CO. 320 W.
WASHINGTON WACO TX 76400 768-3811 Yes
5000 4598.34 ABC OIL 312 W.
ANDERSON AUSTIN TX 76402 327-2141 Yes
3000 2800.00 AMPI 910 E. MCCART WACO
TX 76401 965-5710 Yes
3500 3012.80 ARTS HOMES 377 PLAZA
PARIS TX 76402 448-6123
Yes 4800 2580.50 BC TRUCK STOP 106 W.
6TH CISCO TX
76437 555-3244 Yes 5000 3265.25 C-K
DRILLING 500 E. RIVERSIDE DUBLIN OK 71530
965-3022 Yes 3800 3412.92 CINEMA III
290 PECAN AUSTIN
TX 76405 327-3333 Yes 4200 2345.98
DEC STUDIO 20 MAIN
AUSTIN TX 76405 327-1067 Yes
4200 3100.65 DR. ZUPPE 23 1/2
RIDGEWOOD AUSTIN TX 76402 327-4141 Yes
3000 3204.50 EARTH ELEC 700 S. LOOP
WACO TX 76400 965-5511
No 4000 3757.56 EDDIE ATLAS 202 BINGHAM
LIMA NM 33702
827-7802 Yes 4200 1256.80 DGE SIX BOX
314 CHICO AK 76030
644-5135 Yes 3500 3006.12 WACO ISD
1207 DALE WACO TX
76401 968-7107 No 3000 1401.58
1 2 3 4 5 6 7 8 9 10 11 12 13
15
Basic types of query operations

• projection (select fields such as customer
  name and
• customer telephone number)
• selection (select records that meet certain
  conditions)
• join (merge files with common attributes)
• a combination of the above

16
SQL(structured query language)

• industry standard (relational database
  management systems)
• general form
• SELECT ltFIELDSgt
• FROM ltTABLESgt
• WHERE ltCONDITIONSgt

17
projection
SELECT CUSTOMER.NAME, CUSTOMER.PHONE FROM CUSTOMER
Name Phone AAA OIL CO.
968-3811 ABC OIL 327-2141 AMPI
965-5710 ARTS HOMES
448-6123 BC TRUCK STOP 555-3244 C-K
DRILLING 965-3022 CINEMA III
327-3333 DEC STUDIO 327-1067 DR. ZUPPE
327-4141 EARTH ELEC 965-5511 EDDIE
ATLAS 827-7802 LODGE SIX
644-5135 WACO ISD 968-7107
18
selection
SELECT CUSTOMER.NAME, CUSTOMER.PHONE FROM
CUSTOMER WHERE CUSTOMER.CITY AUSTIN
Name Phone DR. ZUPPE
327-4141 ABC OIL
327-2141 CINEMA III 327-3333 DEC
STUDIO 327-1067
19
selection
SELECT CUSTOMER.NAME, CUSTOMER.PHONE, CUSTOMER.CIT
Y FROM CUSTOMER WHERE CUSTOMER.CITY AUSTIN
OR CUSTOMER.CITY WACO
Name Phone City WACO ISD
968-7107 WACO DR. ZUPPE 327-4141 AUSTIN ABC
OIL 327-2141 AUSTIN EARTH ELEC
965-5511 WACO CINEMA III 327-3333 AUSTIN AAA
OIL CO. 968-3811 WACO AMPI
965-5710 WACO DEC STUDIO 327-1067 AUSTIN
20
selection
SELECT CUSTOMER.NAME, CUSTOMER.PHONE, CUSTOMER.STA
TE FROM CUSTOMER WHERE CUSTOMER.STATE ltgtTX
Name Phone State LODGE
SIX 644-5135 AK EDDIE
ATLAS 827-7802 NM C-K DRILLING 965-3022 OK
21
selection
SELECT CUSTOMER.NAME, CUSTOMER.PHONE, CUSTOMER.CIT
Y FROM CUSTOMER WHERE CUSTOMER.CITY LIKE TIN
Name Phone City ABC OIL
327-2141 AUSTIN CINEMA III
327-3333 AUSTIN DEC STUDIO 327-1067 AUSTIN DR.
ZUPPE 327-4141 AUSTIN
WILD CARD, ? PLACE HOLDER
22
sorting
SELECT CUSTOMER.NAME, CUSTOMER.PHONE FROM
CUSTOMER WHERE CUSTOMER.CITY AUSTIN ORDER BY
CUSTOMER.NAME
Name Phone ABC OIL
327-2141 CINEMA III 327-3333 DEC
STUDIO 327-1067 DR. ZUPPE 327-4141
23
sorting
SELECT CUSTOMER.NAME, CUSTOMER.PHONE FROM
CUSTOMER WHERE CUSTOMER.CITY AUSTIN ORDER BY
CUSTOMER.NAME DESC
Name Phone DR. ZUPPE
327-4141 DEC STUDIO 327-1067 CINEMA III
327-3333 ABC OIL 327-2141
24
join
PAYROLL
Name Dpt
Salary ELAM ACCOUNTING
90000 SMITH FINANCE 95000
CLASS
Name Course ELAM
ACC101 SMITH MBA881
SELECT PAYROLL.NAME, PAYROLL.SALARY, CLASS.COURSE
FROM PAYROLL, CLASS WHERE PAYROLL.NAME
CLASS.NAME
Name Salary Course ELAM
90000 ACC101 SMITH
95000 MBA881
25
join plus selection
PAYROLL
Name Dpt
Salary ELAM ACCOUNTING
90000 SMITH FINANCE 95000
CLASS
Name Course ELAM
ACC101 SMITH MBA881
SELECT PAYROLL.NAME, PAYROLL.SALARY CLASS.COURSE
FROM PAYROLL, CLASS WHERE PAYROLL.NAME
CLASS.NAME AND CLASS.COURSE MBA881
Name Salary Course SMITH
95000 MBA881
26
customer
Name Street Address City
State Zip phone Tax Status
C_LIMIT CU_BALANCE AAA OIL CO. 320 W.
WASHINGTON WACO TX 76400 768-3811 Yes
5000 4598.34 ABC OIL 312 W.
ANDERSON AUSTIN TX 76402 327-2141 Yes
3000 2800.00 AMPI 910 E. MCCART WACO
TX 76401 965-5710 Yes
3500 3012.80 ARTS HOMES 377 PLAZA
PARIS TX 76402 448-6123
Yes 4800 2580.50 BC TRUCK STOP 106 W.
6TH CISCO TX
76437 555-3244 Yes 5000 3265.25 C-K
DRILLING 500 E. RIVERSIDE DUBLIN OK 71530
965-3022 Yes 3800 3412.92 CINEMA III
290 PECAN AUSTIN
TX 76405 327-3333 Yes 4200 2345.98
DEC STUDIO 20 MAIN
AUSTIN TX 76405 327-1067 Yes
4200 3100.65 DR. ZUPPE 23 1/2
RIDGEWOOD AUSTIN TX 76402 327-4141 Yes
3000 3204.50 EARTH ELEC 700 S. LOOP
WACO TX 76400 965-5511
No 4000 3757.56 EDDIE ATLAS 202 BINGHAM
LIMA NM 33702
827-7802 Yes 4200 1256.80 DGE SIX BOX
314 CHICO AK 76030
644-5135 Yes 3500 3006.12 WACO ISD
1207 DALE WACO TX
76401 968-7107 No 3000 1401.58
1 2 3 4 5 6 7 8 9 10 11 12 13
27
Index file on Name
Index files system created files indicating
locations of records
Name AAA OIL CO. ABC OIL
AMPI ARTS HOMES BC TRUCK STOP
C-K DRILLING CINEMA III DEC
STUDIO DR. ZUPPE EARTH ELEC
EDDIE ATLAS DGE SIX WACO ISD
Record 1 2 3 4 5 6 7 8 9 10 11 12 13
28
Index file on Phone
Phone Record
327-1067 8 327-2141 2 327-3333 7 327-4141 9
448-6123 4 555-3244 5 644-5135 12 768-3811 1 8
27-7802 11 965-3022 6 965-5511 10 965-5710 3 9
68-7107 13
29
Microsoft Access

• implements the relational data model
• supports two query languages
• - SQL
• - Query by example (QBE)