Session Date: Monday Mar 27, 2017
Access: Relational Database Management Systems
Preparations for this Session
Read this
In Access 2007: The Missing Manual, 1st Edition,
read 1. Creating Your First Database.
It wouldn't hurt to read
- the Wikipedia entry on Relational Databases for background
- and Microsoft's Database design basics
- If you would like a bit more audio-visual approach, try Microsoft's online Access training course
Read this linked article twice
- Read it prior to the first relational database session so you will have an idea about why relational databases are useful.
- Then read it again after the tables session and prior to the session about relationships
Although we will not get into relationships until the third relational database session, an understanding of how to decompose your task into related tables will help you decide how to create the tables you will later need to relate.
If you use a Mac, you have two choices to be able to use Access
Best choice: virtualization software
Good choice: virtual server support
Pick one choice and be ready with it by the next class session.
Definitions
A database is a collection of related data in multiple tables.
A database management system (DBMS) is designed to add, delete, and update, data in the database and to provide various ways to view on screen or in print the data. A flat file database is contained in a single table as in Excel. (You may encounter the term CRUD, which stands for Create, read, update and delete.)
Relational database are used to eliminate redundancy in storage. Relational databases make it much easier to distribute data to multiple locations. However, those responsible for a particular table, or portion of the database, must ensure that they do not alter the data in a way that breaks the relational systems.
In summary, a relational database will have multiple tables, possibly maintained in multiple locations. The relationship between these tables are carefully defined so that the entire database works together.
Entity Relationship Model of a Database
An entity-relationship model (ERM) is an abstract and conceptual representation of data. Entity-relationship modeling is a database modeling method, used to produce a type of conceptual schema or semantic data model of a system, often a relational database, and its requirements in a top-down fashion. Diagrams created by this process are called entity-relationship diagrams (or ER diagrams).
Access Introduction
Mac Users
If you are using a Mac, you will need to set up a way to run Access. If you chose not to install virtualization software or the ITS Virtual Lab system, you will need to use campus Windows machines for your tasks. you are also free to use the computers in our classroom.
All Users Using Campus Windows Systems
Regardless of your operating system, you will need to save your work to a flash drive or to your OneDrive as the computers in this lab are wiped every night.
Demonstration
In this session will demonstrate the following steps:
- Start Access
- Create a new database; discuss naming conventions
- Discuss the concept of Objects:
- Tables
- Queries
- Forms
- Reports
- Create a Table and set some Table Properties
- Discuss fields and field properties
- Create some fields and set properties
- Discuss Primary keys and Foreign Keys
- Generate a Query and view the resultant SQL
- Create a Form
- Create a Report
I will have a slideshow with graphics available before this session starts.
This demonstration will just give you an overview of the program. Before yo start plugging in real data you should first make a plan.
Plan the Database First
- Determine the need or purpose of the database
- Decide which kinds of things (the tables) the database should include
- Specify the fields that comprise the tables and determine which fields in each table contain unique values
- Decide how the tables are related to each other, then review and finalize the design
- Construct and relate the tables and populate the database with sample data
- Create forms, queries, and reports to use with the data