Lecture24 sections 9'19'7

About This Presentation

Title:

Description:

Number of Views:24

Avg rating:3.0/5.0

Slides: 10

Provided by: imadr

Category:

Tags: access | control | lecture24 | primary | sections

Transcript and Presenter's Notes

Title: Lecture24 sections 9'19'7

1
Lecture-24 (sections 9.1-9.7)

2
Introduction

What is a database?
Collection of related data arranged into related
tables of rows columns
Different from Excel?
Suitable for large data sizes
Models a complete business
Provides stronger built-in data access
functionalities
Rather than building them from scratch as in
Excel
Provided thru a DBMS
Works with data of interest only
What is a database management system (DBMS)?
A collection of complicated software programs
that organizes the data in a database and allows
users to insert, update, delete or retrieve
subsets of the data
Other provided features include Access Control,
Recovery, Concurrency etc

3
Introduction

A Table holds data for one entity of the business
University example
Table is divided into rows called records (or
rows)
One row describes one unit in our table
E.g. one employee, product, customer, etc
Row is divided into fields (or columns)
Properties of the entity
E.g. properties of an employee Name, address,
salary, position, etc
Student table in a university database?
Fields? Records?
Every table must have a unique key
Unique value for every record in the table to
assist in maintaining data integrity (Key field)
If more than 1 key exist, one is designated as
primary key
Student table in university database
Unique attributes (key fields?)

4
Designing a Table in Access

Office Button?New? under Blank Database on the
right
Name your database file
Choose its storage location
Click on Create button
A window appears under the AllTables section
on the left
Click on the down arrow
Select Object Type
Click on the down arrow again and observe that
now you can see all objects that can be created
in Access Forms, Tables, Queries, Reports,
Macros, etc
Select Tables and double click on the only
table in the menu (usually called Table1)
In the upper menu (right above Table1) Click
on View and select Design Viewsave table if
prompted
Field part Name and description of every field
Properties part specific properties for each
field

5
Designing a Table in Access

In addition to the type (Field Part), we need to
set the following properties (Properties Part)
Required Determines if the user is required to
enter a value for this field for every record
Default Value
Input Mask (for Text and Date fields only)
Sets a suitable pattern mask for data to be
entered
like -- for SSN
We can choose from common masks
? number, space, or plus or minus
L ? A letters
A ? a letter or number

6
Designing a Table in Access

Validation Rule
Determines what is a valid for this field
Department in EMPLOYEE table must be limited to
certain names
In (Sales, Production, Management,
Design)
lt 1/1/1995
LIKE "-
LIKE "smith"
LIKE "??00"
Validation Text specifies the message output to
the user in case in valid input is provided (e.g.
Not a Valid Department)
Set one field as the primary key
Select the field
Click on the key icon on the toolbar (notice the
key appears next to the field)

7
Queries

A selection function
selects certain fields and records from a table
according to certain criteria displays results
in a new table for a report
Record filter
Update, delete, insert data into a table
Select Queries are very common
The produced table is a subset of the database
Limited by selected fields and matching records
Used to summarize or do simple calculations on
the data in the database

8
Queries

Click on the down arrow on the left next to where
it says Tables
Select Queries and double click on the only
table in the menu (usually called Table1)
On the top menu, click on the Create Ribbon and
then click on the Query Design button in the
Other group
to be displayed in the query or
that participate in the selection criteria
(Not limited to the given fields)
Derive others

9
Queries