Introduction to Databases

1
Introduction to Databases

• CIS 5.2

2
Where would you find info about yourself stored
in a computer?

• College
• Physicians office
• Library
• Grocery Store
• Dentists office
• Verizon
• IRS

3
General Definition

• A DATABASE is a collection of related data.
• Data known facts that can be recorded and have
  implicit meaning.

4
Miniworld

• Each database can be viewed as representing a
  part of the real world, called a miniworld.

5
Size of a database

• Small database example
• your personal phone directory
• Large database example
• database of the IRS

6
DBMS Database Management System

• A collection of programs that enables users to
  create and maintain a database.

7
MS Access is a DBMS

• In ACCESS, a DATABASE consists of a collection
  of tables.

8
Example of a Database
9
Table

• A table is defined by its columns, called fields.
• Each row (or entry) in a table is called a
  record. A record is the information about a
  specific person, product, or event.

10
Starting ACCESS

• Begin ACCESS
• Open a blank Database although a database
  consists of many files, in Access, each database
  is viewed as a single file, hence a single icon.

11
Defining a TABLE

• A TABLE is defined by its fields (column
  headings).
• Field Name unique name
• Data Type type of info a field can
  contain.
• Description comments about field.

12
PRIMARY KEY

• Each table must have a primary key which is a
  unique identifier for each record.
• Eg. ID number for a student, course number for a
  course, driver number for a driver, etc.

13
Two VIEWS of a Table

• Design View view the tables definition, I.e.
  the fields and their properties.
• Datasheet View view the actual records in the
  table.

14
Database Design

• Arranging data into tables and fields.
• This process can be long and difficult.

15
Remove Redundancy

• WHY?
• Wastes space
• Updating is difficult
• Inconsistent data may result

16
Maintenance

• Modifying the data
• Add records
• Delete records
• Update records
• Modifying the design
• Add fields into tables
• Remove fields from a table
• Change characteristics of existing fields

17
Query Types

• Select Query chooses data from a table and
  displays it.
• Update Query updates records in a table
• Delete Query deletes records from a table.

18
Validation Rules

• Validation Rules are rules that a user must
  follow when entering data into a database.
• Validation Text the message that appears if the
  user enters the wrong type of data.

19
Validation Rules

• Indicate that a field is required
• Entry must lie within a given range
• Specify a valid collection of values
• Specify a default value
• Change lowercase to UPPERCASE

20
Referential Integrity

• Primary Key - unique identifier for each record
  in a table.
• Foreign Key a field in a table whose values
  must match a primary key in another table.

21
Referential Integrity

• We can define a relationship between two tables.
• ONE to MANY Relationship
• Primary key Foreign Key

22
Indexes

• An INDEX in the back of a book contains important
  words or phrases (in alphabetical order) together
  with a list of pages on which the given word or
  phrase can be found.

23
Indexes

• An INDEX on a table in ACCESS is a list of names
  in alphabetical order and their locations in the
  table.
• The INDEX can be constructed on any field, not
  only on the names. The field on which the index
  is built is called the index key.

24
Index

• An index is used instead of physically sorting
  the records.
• An index is automatically created for the primary
  key of every table.

25
When should you create an INDEX?

• Frequent need of sorted data.
• Frequent searches on that field.
• Note updates on an indexed field will be slower
  since the index must also be updated.