Transcript Introduction to MS Access

IBS 520
Introduction to Internet
Technology
Database Fundamentals
Week 4
Database Fundamentals
• A database is simply a structured collection
of similar data.
• Databases display data in an imaginary two
dimensional grid.
An Important Tutorial
• Prof Holowczak is one of the most valuable CIS
Professors at Baruch College. All the Database
instructors at Baruch College use his tutorials for
their classes.
• Read the tutorial very carefully. The content of the
tutorial will be sufficient for creating a basic
database driven website.
• http://cisnet.baruch.cuny.edu/holowczak/classes/2
200/access/accessall.html
Relational Database Management
Systems
• RDBMS is a software program that lets you create
databases and then manipulate data in the
databases.
• Data is organized in collection of tables.
– Allows you to create database structures
– It lets you easily add new records, change field values
in existing records and delete records.
– Contains built in query language
– Contains built in report generator
– Provides protection of databases.
Collection of Related Tables
• A collection of related tables is called a database
or relational database.
• Sometimes you may want the information about
Customers and orders they placed. To obtain this
information you must have a way to connect
records in the customer table to records in the
order table.
• You connect the records in the separate tables
through a common field that appears in both
tables.
Primary Key
• Each customer number in Customer table must be
unique, so that you can distinguish one customer
from one another.
• The customer number field is the Primary Key of
the Customer table.
• A Primary Key is a field or collection of fields
whose values uniquely identify each record in a
table.
• In the order table order number is the primary key.
Entity Integrity
• Access forces you to enter a value for the
primary key field in every record in the
table. This is known as entity integrity.
• If you do not enter a value for a field, you
have actually given the field a null value.
You cannot give a null value to the primary
key field.
Foreign Key
• When you include the primary key from
one table as a field in a second table to form
a relationship between the two tables it is
called a foreign key in the second table.
Guidelines for Designing a
Database
• It is a useful tool but you need to carefully
design the database.
• In database design you determine
fields,tables, and relationships needed to
satisfy the requirements.
1- Identify all the fields needed to
produce the required information
–
–
–
–
–
Customer name
Order Number
Street
City
State
–
–
–
–
–
–
–
–
Zip Code
Customer Number
Billing date
Owner Name
Invoice amount
Placed by
Phone
First contact
2- Group related fields into tables
• CUSTOMER TABLE
–
–
–
–
–
–
–
–
–
Customer Number
Customer Name
Street
City
State
Zip Code
Owner name
Phone
First Contact
• ORDER TABLE
–
–
–
–
Order Number
Billing date
Placed by
Invoice Amount
3- Determine each tables primary
key
– Customer Number can be the primary key for
the Customer Table
– Order Number can be the primary key for the
Order Table
4- Include a common field in
related tables (Foreign Key)
• With this common field Barbara can find all
the orders placed by a certain customers
Avoid Data Redundancy
• Data redundancy occurs when you store the
same data in more than one place.
• With the exception of common fields to
connect tables you should avoid redundancy
– It wastes storage space
– Can cause inconsistencies
5-Determine the Properties of
Each Field
• These properties include
–
–
–
–
–
fieldname,
fields maximum number of characters,
fields description,
fields valid values
and other field characteristics.
Guidelines for Designing Access
Tables
• A name can be up to 64 characters long
• A name cannot start with a space.
• A table or query name must be unique
within a database.
• A name can contain letters, numbers, spaces
and special characters except a period(.)
exclamation mark(!) and square brackets []
Tips
• Capitalize the first letter of each word in the
name
– CustomerNum
• Avoid extremely long names
• Use standard abbreviations Num for
Number Amt for Amount and Qty for
Quantity
• Do not use spaces in field names
Assigning Field Data Types
• You must assign a data type for each field.
• The data type determines what field values
you can enter for the field and what other
properties the field will have.
• Billing date will store Date values so you
assign Date/Time field value.
Field Name
Data Type
Field Size Description
OrderNum
Text
3
CustomerNum Text
BillingDate
Date/Time
PlacedBy
Text
InvoiceAmt
Currency
Primary Key
Foreign Key
Person who
placed order
Creating A Table
• Creating a table consists of :
– Naming the fields and defining the properties
for the fields.
– Specifying a primary key (and a foreign key if
applicable) for the table
– Saving the table structure
Referential Integrity
• When you add a record to a related table, a
matching record must already exist in the
primary table.
Cascade Update
• If you attempt to change the value of the PK
(Primary Key) in the primary table, Access
prevents this change if matching records
exist in the related table. However if you
choose “Cascade Updates” option, Access
permits the change in value to the PK and
changes the appropriate FK values in the
related table.
Cascade Delete Option
• When you delete a record in the Primary
table, access prevents the deletion if
matching records exist in a related table.
However if you choose the cascade delete
option, Access deletes the record in Primary
table and all records in related tables that
have matching FK values.
Introduction To Queries
• Querying a database
– A query is a question that you ask about data
stored in a database
•
•
•
•
•
Display selected fields and records from a table
Sort records
Perform calculations
Update data in the tables in a database
Find and display data from two or more tables
Questions
• Which customers have unpaid bills?
• Which type of coffee sells in Ohio?
The queries that ask for this information are
called select queries.
Forms And Reports
• Refer to the tutorial.