Transcript Microsoft Access

Microsoft Access 1
Database Creation and Management
Basic DB Terms

Data: Meaningful facts, text, graphics, images,
sound, video segments


Information: Data processed to be useful in decision
making


A collection of individual responses from a marketing
research
Pattern of geographical buying habit based on analysis of a
marketing research
Metadata: Data that describes data
2
Data in Context
Large volume of facts, difficult to interpret / make
decisions
3
Information
Useful for decision making / interpretation
4
Metadata
Descriptions of the properties or characteristics of the data,
including data types, field sizes, allowable values, and
documentation (Data Dictionary)
5
Database Systems
Application
#1
Application
#2
DBMS
Application
#3
6
Database
containing
centralized
shared data
Database Systems




Collection of electronic data
Central repository of shared data
Stored in a standardized, convenient form.
Requires a Database Management System (DBMS)
7
Overview of Access Database

One of database management systems
software.


Access, SQL Server, Oracle, DB 2
MS Access is a relational database.

a collection of tables that are related to one
another based on a common field.
Relational Database
A schematic diagram of a relational database (a) and a sample part of a
relational database showing different tables (b)
Properties of a Relation
Based on the set theory
1. There are no duplicate rows.



The body of the relation is a mathematical set
(i.e., a set of rows), and sets in mathematics by
definition do not include duplicate elements.
If a "relation" contains duplicate rows, then it is
not a relation.
Properties of a Relation
2. Rows are unordered (top to bottom).


Sets in mathematics are not ordered. So, even if
a relation A's rows are reversely ordered, it is still
the same relation.
Thus, there is no such thing as "the 5th row" or
the last row. In other words, there is no concept
of positional addressing.
Properties of a Relation
3. Columns are unordered (left to right).


The heading of a relation is also defined as a set.
There is no such thing as "5th column" or the last
column.
Properties of a Relation
4. Every value is atomic.

At every row-and-column position within the
table, there always exists precisely one value,
never a list of values. Or equivalently, relations do
not contain repeating groups.
Open an existing database

To open an existing database, you must first start
Access




When Access is launched you will see the Access window,
with the task pane on the right side of the window.
From the task pane, you can open an existing database.
Or simply double-click the existing database
to open.
Practice: Create Tables on the class website
How Access creates and saves a new
database

Create a new database



Your first activity (before question #1) for the midterm
is creating a new database.
Database name: your last name + first initial of first name
When you press the Save button in Access, you are
saving the design of the Access objects and NOT the
database itself!

The Save function in Access differs from the Save function in
other Windows programs.
Characteristics of Relational DB

In a relational database, each record (row)
in a table must be uniquely identified.


Using Primary Key
A relational database is a collection of
tables that are related to one another
based on a common field.

Using Foreign Key (& Primary Key)
Primary Key (PK) & Foreign Key (FK)

PK: A field that uniquely identifies each record in a
table.



SS#, Student ID
It does not have to be first field.
FK: A field that connects one table logically with
another table

Rule of Thumb: PK = FK (value) – see next slide
Relating tables using PK and FK
The primary key in the
Employer table (EmployerID)
is the common field that
relates this table to the
Position table.
PositionID is the primary key in
the Position table. The
EmployerID field is a foreign key
in this table.
Primary keys can only have one
occurrence in a table. Foreign keys
may have multiple occurrences.
Valle Coffee’s Restaurant DB

Valle is a small distributor of inexpensive coffee beans
to various restaurants. Barbara Hennessey, the Director
of CRM, and her staff use Access to maintain company
data such as customer orders and billing. Barbara has
recently developed Restaurant 1 database to track
orders and billings. However, she has not been able to
develop the database fully. So, she is for your help in
completing and maintaining this database.
Descriptions of Restaurant DB

Valle coffee’s Restaurant 1 database will
contain five tables:




Customer table, which Barbara already has.
Order table, which you will create soon.
Product and Order Detail tables, which you
will import from FineFood database.
Billing Address table that is in Excel format
and you will import it, and then convert to Access
table.