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
Purpose of a Database


The purpose of a database is to keep
track of things
Unlike a list or spreadsheet, a database
may store information that is more
complicated than a simple list
Database



Collection of electronic data
Central repository of shared data
Data Stored in a standardized and convenient form.
7
Desktop Database Systems
Organizational Database
Systems
Software: Database Management
System (DBMS)


A database management system
(DBMS) serves as an intermediary
between database applications and the
database.
The DBMS manages and controls
database activities.
Types of Databases

Databases can be classified according to:





Number of users
Database location(s)
Expected type and extent of use
Single-user database supports only one user at
a time (Desktop database)
Multiuser database supports multiple users at
the same time
Types of Databases



Centralized database: data located at a single
site
Distributed database: data distributed across
several different sites
Operational database: supports a company’s
day-to-day operations


Transactional or production database
Data warehouse: stores data used for tactical
or strategic decisions
Overview of Access

One of database management systems
software.


Access, SQL Server, Oracle, DB 2
MS Access is a relational model (database).


a collection of tables that are related to one
another based on a common field.
Relational model – developed based on predicate
logic and set theory from mathematics.
Relational model (database)
A schematic diagram of a relational database (a) and a sample part of a
relational database showing different tables (b)
Properties of Relation 1
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 Relation 2
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 Relation 3
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 Relation 4
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.
** No two rows can be identical **
Terminology
Synonyms…
Access term Table
Row
Column
General
File
Record
Field
DB specific
Relation
Tuple
Attribute
A Sample Relation
EmployeeNumber
100
101
104
107
FirstName
Mary
Jerry
Alex
Megan
LastName
Abernathy
Cadley
Copley
Jackson
A Nonrelation Example 1
Cells of the table hold multiple values
EmployeeNumber
Phone
100
335-6421,
454-9744
101
215-7789
104
610-9850
107
299-9090
LastName
Abernathy
Cadley
Copley
Jackson
A Nonrelation Example 2
No two rows can be identical
EmployeeNumber
100
101
104
100
107
Phone
335-6421
215-7789
610-9850
335-6421
299-9090
LastName
Abernathy
Cadley
Copley
Abernathy
Jackson
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.
Download and save “Restaurant 1” database.
How Access creates and saves a
new database

Create a new database


Database name: your last name + first initial of your 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.
Valle Coffee’s Restaurant DB

Valle company sells 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 billings. Barbara has
recently developed Restaurant 1 database to track
orders and billings. However, she has not been able to
develop the database fully to track and maintain other
important company data. So, she is asking for your help
in completing and maintaining the Valle 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.