MIS-3210 Activity #11 Using Microsoft Access to Create a Database

MIS-3210Activity #11
Using Microsoft Access to
Create a Database
Learning Objectives
• How to create a database with Access
• How to create and use Keys to Link Related Tables
• How to create and run a JOIN query to link Tables
• How to Export an Access Table into Excel.
Microsoft Access
Example Access Desktop
Access Data Types
• Short Text (max=255 characters)
• Long Text (up to 1 GB)
• Number (numeric data)
• Currency (numeric as currency)
• Date & Time (dates or times)
• Yes/No (True/False, 0/1, aka “Boolean”)
• Rich Text (text with formatting retained)
• Attachment (link to a separate object)
• Hyperlink
• Calculated
The five types of objects
Access enables you to create
these objects in your
database:
• Tables
• Queries
• Forms
• Reports
• Macros
Creating a Functional Relational
Database
Business Need:
Setting up a simple database for an
animal veterinary clinic
Create a New Access Database
Name of the DB:
VincentVetClinic
We will create two Tables in our
Database
1.Owners
2.Pets
Owners
Pets
-Smith, Bob
–
Fido (owner=Smith)
Crinkles (owner = Smith)
–
Tabby (owner = Jones)
–
Lunati (owner=Gottlieb)
-Jones, Terry
-Gottlieb, Josef
Designing & Viewing Tables
• Design View – Create or modify the columns (aka
Fields) in a table
• Datasheet View – Enter/ Modify data in a table
Copyright owned by Dr. Margaret Schultz, University of Memphis, Memphis, Tennessee
Close the “default” table in
DATASHEET view and Open DESIGN VIEW
Access “assumes” that you want to use DATASHEET view to
create a table. But we want to use DESIGN view, not
DATASHEET view.
Close the Table1 in the DATASHEET
window by clicking on the “X” next to the Table name.
Copyright owned by Dr. Margaret Schultz, University of Memphis, Memphis, Tennessee
Create a new table in DESIGN view
Create a new table starting in DESIGN view
If it asks you for a Name – call it OWNERS
If you do not get asked
to input a Name just
yet, it will probably
happen at a later step
– so carry on
Copyright owned by Dr. Margaret Schultz, University of Memphis, Memphis, Tennessee
Add field names & Data types
in DESIGN view
Design View
Create the structure
of the Table using
the next slide and
selecting the data
type
Right click on Table 1 or
OWNERS if you
renamed it and select
Design View
Copyright owned by Dr. Margaret Schultz, University of Memphis, Memphis, Tennessee
Table1 or Owners
Design View
(Table Columns
or Fields)
Select
OwnerID
datatype as
AutoNumber
and the
remaining as
Short Text
Datasheet View (Enter Data)
Right-Click on Table Name on Left panel and
“Open” to enter data as shown.
DO NOT ENTER ANYTHING IN OWNERID
This is an AutoNumber and Access will create it
Close table by selecting “X”
near the table name and if
asked, name it as OWNERS
Close the Table Design View
• Close the Owners Table by clicking on the “X”
next to Table Name on top.
When you close the table, Access will ask if
you want to create a “Primary Key”
Select YES
Copyright owned by Dr. Margaret Schultz, University of Memphis, Memphis, Tennessee
Primary keys
Check Design
View
Notice the
small key icon
next to the
OwnerID. This
is the PRIMARY
KEY of the
table
A PRIMARY KEY is a field that holds UNIQUE
VALUES for every record in the table.
That means each OwnerID record will exist
only once – no duplicates!
Copyright owned by Dr. Margaret Schultz, University of Memphis, Memphis, Tennessee
Table#2: Pets
Design View
Create a new second
table by selecting
Create>Table design
and call it Pets
IMPORTANT:
Note here that
OwnerID is a
“Number” data
type
Datasheet View
Enter data as
shown.
There should
be atleast
two pets that
belong to
same owner
In this case PetID
will be the Primary
key
Relationships
• To link the two tables we will establish a
“relationship”.
• IMPORTANT: All tables in the workspace MUST
be closed before you can define a relationship
Copyright owned by Dr. Margaret Schultz, University of Memphis, Memphis, Tennessee
Establish Relationship
“Join the tables”, 1:many relationship
Primary
key
Primary key
Foreign key
Note the “join” (or link) using
the OwnerID as a common
data element in both tables
This is tricky!
Select Owners: OwnerID from
Owners Table and “DRAG
CURSOR” to Pets: OwnerID.
But wait – what’s this popup!!
See NEXT SLIDE
Relationships: Referential Integrity
The 3
checkmarks here
mean – Keep the
tables in SYNC
•
No orphan
records
• Modify in
sync
• Delete in sync
If you get an error here, check to make sure your data is
correct. Check the data you just created.
OwnerId in PETS table must also exist in Owners Table
Copyright owned by Dr. Margaret Schultz, University of Memphis, Memphis, Tennessee
Query and Join
• Now, we have created TWO tables – Owners
and Pets that are linked together with a
common field called OwnersID.
• What if we want to export the information into
a single file with data from both tables into a
single worksheet?
• Using JOIN we can create a NEW result table
which has selected columns from our linked
tables which we can then export into Excel
Copyright owned by Dr. Margaret Schultz, University of Memphis, Memphis, Tennessee
FYI only:
Other Query functions under Create
Append one
table to the
end of
another
Pull selected
data
Create a
new table
Update
content of
selected
records
Summarize detailed
records
(similar to Pivot Table)
Delete
selected
records
Create a Query
Close all tables before
creating a Query
Copyright owned by Dr. Margaret Schultz, University of Memphis, Memphis, Tennessee
Create a Join Query
Select both tables
– drag them to
the workspace
In the lower half, select all the columns of Pets table and all the columns of Owners Table
Pets.*
Owners.*
Joined Table
(result of running the query)
Run the Query
Result!
Error message: Type mismatch?
This usually happens if you forgot to make OwnerId in the Pets
table a Number type to match the autonumber in the Owners
table.
Export the Joined Table to Excel
Select location/name for exported file
Save your file
to your specific
folder
Leave
everything else
unchecked.
Save the Excel file & Rename it
VincentVetClinic
Completed Project
Concepts Addressed:
• How to create a database with Access
• How to create and use Keys to Link Related Tables
• Primary key and referential integrity
• How to create and run a JOIN query to link Tables
• How to Export an Access Table into Excel.
• Upload to Act#11 Assignment Folder:
– Access Database (VincentVetClinic)
– Extracted data (Excel Workbook)
MIS 3210 – Unit 4
Databases, Database Management
Systems and SQL
Learning Objectives
• What is a Database?
• What does a DBMS do? How is it useful?
• What it means to use the Relational Model to
structure databases.
• An introduction to SQL.
• An introduction to MS Access.
What is a database?
• A collection of data arranged into a structure
• Usually, the structure is a table formation
Two very common types of databases
A collection of tables linked together
An Excel worksheet (1 table)
The Structure of Most Databases
• Most databases conform to a relational model
• That means:
– Data is arranged into tables
– Tables have rows called “records”
– Tables have columns called “fields”
– Cells contain only one data item
What is a DBMS?
(Database Management System)
Front end (User Interface) + Backend (Database Engine)
Relational Database Management System
RDMBS
Relational database was proposed by Edgar Codd (of IBM Research) around 1969. Today,
there are many commercial Relational Database Management System (RDBMS) products,
such as Oracle, IBM DB2 and Microsoft SQL Server. There are also many free and opensource RDBMS, such as MySQL.
A relational database organizes data in tables (or relations). A table is made up of rows and
columns. A column is also called a field (or attribute). The relationships that can be created
among the tables enable a relational database to efficiently store huge amount of data, and
effectively retrieve selected data.
RDBMS Products:
Oracle, DB2 (IBM), SQLServer
(Microsoft), MySQL
(OpenSource/Oracle)
Access (Microsoft)
The elements of a database…
Bits (0,1)
Bytes (“A”)
Field (LastName)
Record
PatID, LastName, FirstName,
Address, Etc.
Table
(Patient_Info)
Patient_Info
Patient
System
– Patient #1
– Patient #2
– Patient #3
– etc.
Providers
PatID
Labs
Admissions
A Business
Application
Accounting
(AP, AR, etc.)
Facilities
An Integrated
Enterprise System
Examples of DBMS
• Microsoft: Access
• Microsoft: SQLserver
• Oracle: DB
• IBM: DB2
• Open Source: MySQL
Programming to interact with
Databases (SQL)
What is SQL?
• Structured Query Language.
• The standard language used to communicate
with a database.
Extracting Data from a Relational DB
Structured Query Language (SQL):
SQL: the DBMS language
Some of The Most Important SQL Commands:
• SELECT – extracts data from a database
• UPDATE – updates data in a database
• DELETE – deletes data from a database
• INSERT INTO – inserts new data into a database
• CREATE DATABASE – creates a new database
• CREATE TABLE – creates a new table
• DROP TABLE – deletes a table
• CREATE INDEX – creates an index (search key)
Use ‘SELECT’ to query a database
SELECT column_name(s)
FROM table_name
WHERE some criteria is true
optional
SELECT fullname FROM mailingList WHERE major=“MIS”;
SELECT statement: Example
Table Name = mailingList
Fullname
age
email
major
degree
Bob Smith
22
bsmith @memphis.edu
MIS
BBA
Mary Carr
24
mcarr@memphis.edu
MGMT
BBA
Chris Adams
Betty Brown
30
22
cadams@memphis.edu
SCM
MBA
bbrown@memphis.edu
ACCT
BBA
Ed Jackson
28
ejack@memphis.edu
dday@memphis.edu
ACCT
MBA
Doris Day
21
MIS
PhD
Select the name of all students who are over 25 years old
SELECT fullname
FROM mailingList
WHERE age>25;
Chris Adams
Ed Jackson
A database is typically a set of tables that are connected
(related) using common elements.
Redundancy and Efficiency
“Normalization”
Tracking Employee Dependents
EmployeeID
111-22-3333
222-33-4444
333-44-5555
LastName FirstName Address
Anderson Andrew
111 First
Brown
Betty
222 Second
Carson
Carl
333 Third
Each Entity in a
separate table
City
Memphis
Memphis
Bartlett
State Zip
CellPhone
TN
38112 901-123-4567
TN
38104 901-234-4567
TN
38138 901-345-4567
EmployeeID LastName
111-22-3333 Anderson
222-33-4444 Brown
333-44-5555 Carson
EmployeeID “links”
the dependent back
to the employee so
we know who is a
dependent of whom.
Spouse ChildName1 ChildName2 ChildName3
Mary
Amy
Andrew
Cindy
Susan
FirstName Address
Andrew
111 First
Betty
222 Second
Carl
333 Third
Sallie
Sade
Sophie???
City
State Zip
CellPhone
Memphis TN
38112 901-123-4567
Memphis TN
38104 901-234-4567
Bartlett TN
38138 901-345-4567
DependentID EmployeeID
AND001
111-22-3333
AND002
111-22-3333
AND003
111-22-3333
AND004
111-22-3333
BRO001
222-33-4444
CAR001
333-44-5555
CAR002
333-44-5555
CAR003
333-44-5555
CAR004
333-44-5555
CAR005
333-44-5555
CAR006
333-44-5555
Dep_Lname Dep_Fname
Anderson Andrew
Anderson Mary
Anderson Amy
Anderson Andrew Jr
Brown
Betty
Carson
Carl
Carson
Cindy
Smith
Susan
Smith
Sallie
Smith
Sade
Carson
Sophie
Primary Key and Foreign Key
• A primary key is a field in a
table designed to uniquely
identify each table record.
– No duplicates
– Cannot be NULL (none)
• A foreign key is a field in a
table that maps to a
primary key in another
table. It is used to provide a
link between data in two
tables.
EmployeeID in the EMPLOYEE
table is what we designated to
be the primary key.
EmployeeID in the DEPENDENT
table is a foreign key
DependentID in the DEPENDENT
table is a primary key (although
it might not be necessary for any
linkages.
Data Types
•
•
•
•
•
•
•
•
•
Short Text: This is the default option and is best for most text
in Access. You should also choose it for numbers you don’t
plan to do math with, like postal codes and phone numbers.
Number: This is best for numbers you might want to do
calculations with, like quantities of an item ordered or sold.
Currency: This automatically formats numbers in the
currency used in your region.
Date & Time: This allows you to choose a date from a popout calendar.
Yes/No: This inserts a checkbox into your field.
Rich Text: This allows you to add formatting to text,
like bold and italics.
Long Text: This is ideal for large amounts of text, like product
descriptions.
Attachment: This allows you to attach files, like images.
Hyperlink: This creates a link to a URL or email address.
The five types of objects
Access enables you to create
these objects in your
database:
• Tables
• Queries
• Forms
• Reports
• Macros
Next Up:
Activity #11 (Build a Database)