Databases

Tom Kelliher, CS14A

Apr. 14, 1997

Friday's quiz: e-mail me a memo telling me if I can afford a certain car. I'll tell you:

1. What I can afford per month.

2. My monthly gasoline bill.

3. My annual insurance bill.

4. The price of the car.

5. My down payment.

6. The annual interest rate and length of the auto loan.

Introduction

What is a database? Organized collection of information.

What are operations associated with a database?

1. Store information.

2. Organize information.

3. Retrieve information.

Two types of databases:

1. File management system --- single file.

2. Database management system --- multiple files, relationships between files.

How would an individual use a database?

How would an organization use a database?

Is the World Wide Web a database?

What are the impacts upon society?

Parts of a Database

1. File --- Usually a binary file in a proprietary format. Contains records.

2. Record --- Information about one ``entity.'' Contains fields.

3. Field --- One ``bit'' of information about an entity.

Abstract view of a database:

Note: each record has same number of fields.

Database Example

Catalog of CDs:

1. One record for each CD.

2. Fields:
   1. Title.

   2. Artist.

   3. Year.

   4. Type (alternative, classical, jazz, etc.)

   5. Label.

   6. Track number.

   7. Track title.

   8. Order number.

Can we put all the track titles into one record?

File Management Systems

1. Flat file systems.

2. Single file databases.

3. No linking to other files.

4. Only one ``view.''

5. Redundancy (consider CD example).

6. Redundant (repeated) data leads to data integrity problems.

Database Management System

1. Relational database.

2. Multiple files.

3. Relationships between files.

4. Relational model:
   1. Entity.

   2. Relationship.

5. Key field (sometimes multiple fields).

6. Redundancy elimination. Why is redundancy bad?

Example

Registrar Example

Design a relational database for the Registrar.

Important data:

1. Students. What fields? What's the key field?

2. Faculty. What fields? Key field?

3. Classes. What fields? Key field?

4. What relational files?

Example Queries (view):

1. Student: phone number, home address, current classes.

2. Faculty member: e-mail, current classes, current students.

3. Class: title, enrollment limit, current enrollment, instructor, students.