(CS-241) Database Systems
What is Data & information?
Advantages of Database Management System:
Data Consistency
Better Data Accessibility
Data Atomicity
Data Security
Data Sharing
Less Effort
Related Articles
Compactness
Report Writing
Microsoft Access - Overview
Microsoft Access stores information in what is called a database..
There are four major steps to using Microsoft Access:
Database Creation: Create your Microsoft Access database and specify what kind of data you will be storing. A retail business might create a database to store all their sales information (i.e. items sold, customer, employee, commission, etc)
Data Input: After your database is created the data the store gathers every business day can be entered into the Access database.
Query: This is a fancy term to basically describe the process of retrieving information from the database.
Report(optional): Information from the database is organized in a nice presentation that can be printed in an Access Report
TABLES
A table in Access is quite different then a table in real life. Instead of having wooden legs and being used for meals, Access Tables are a grid made up of rows and columns. Here's an example of a table in Access:
There are four key components we want you to learn right now:
tbl_Sales: The name of our table is the example is "tbl_Sales". Note that we could have simply called our sales table Sales, but by including a prefix tbl_ there is absolutely no confusion and is a great Access habit to pick up!
Columns: A column is one vertical section of the table (i.e. up-and-down sections). The vertical columns have their label at the top and these labels should describe the type of information that will be stored. The columns in this table are: Employee, Product, Price and SaleNumber.
Rows: A row is one horizontal segment of the table (i.e. left-to-right sections). One record takes up exactly one row. For example, in this table one sale at Bob's Shoe Store was a pair of slippers, which sold for $5.00. This record was entered left-to-right as follows: Employee-Bob, Product-Slipper, Price-$5.00, SaleNumber-3.
Cells: A cell is simply the intersection of a row and a column. Can you find the cell that contains the value $150.00? Which row and column intersected at this cell? When you enter information into Access it will often be one cell at a time!
These definitions may seem confusing at first and if that is the case, please read through this lab, play around in Access then revisit this page to seem if it is starting to make more sense. If you stick with it you'll be amazed at how much you can learn!
Now that we've covered the basics of Access Tables let's actually create one!
Creating an Access Table
When you create a table in Access you have to know what the table will store and what format that information will be in. For example if you wanted to store the product identification numbers involved in a sale, then you might label that column "ProductID" and specify that only numbers should be stored for that column.
We'll be creating the table tbl_Sales that you saw above, but remember this is only the table creation stage and we will not be entering data just yet!
With the Tables object tab selected, click the "Design view"
This will also bring up a prompt to name your Access Table.
Click yes and enter "tbl_Sales" for your table's name.
This will bring up the Table Design View
There are three columns here that should be explained in detail:
Field Name: This is where you type the name for your column. A common practice is to make it one word and to use capitalization for multiple words squished into one (e.g. SaleNumber)
Data Type: This column is where you specify the type of data that will be stored. If you are storing money then select Currency. The most common types of data are: Text, Number, Currency and Date/Time.
o Description: Here you can type optional notes to remind yourself or provide useful information for others who might be viewing this file later.
The first column in our tbl_Sales example was Employee, so let's enter in Employee in the Field Name column and choose Short Text from the Data Type column. If click inside the Data Type column you will see that it is actually a drop down select box with many options to choose from. Select the Short Text option.
Enter the following information for our remaining three columns of tbl_Sales:
Field Name: Product, Data Type: Short Text
o Field Name: Price, Data Type: Currency
o Field Name: SaleNumber, Data Type: Number
Before we are finished here, we need to make a Primary Key. A primary key is restriction that we place on a column stating that there can be no duplicate values in that column. We will be talking about keys later, but for now right-click in the SaleNumber row and choose Primary Key from the pop-up menu.
We have finished our table's outline so click the X in the top right to close the design view (don't close Access, just the Design Window).
To begin entering records, right-click the table and click on “OPEN” you want to add records to.
This brings up the Table Window and you can see that our table contains no data, yet.
Bob's five sales are as follows (note: Bob is currently the only "employee"):
Sneaker - $40
Sneaker - $60
Slipper - $5
Heel - $12
Dress - $150
Enter the information for Bob's first sale as follows:
Employee: Bob
Product: Sneaker
Price: $40
SaleNumber: 1
Enter the remaining four sales so that your table looks like:
Access Query
As tables grow in size they can have hundreds of thousands of records, which makes it impossible for the user to pick out specific records from that table. Queries were designed to combat this problem. With a query you can apply a filter to the table's data, so that you only get the information that you want.
Choosing a Table to Query
Before you can create a query, you have to navigate to the Create Tab in your Access database. Select Query Design from the Queries Pane.
Although you could use the Wizard, we will guide you through the process of creating an Access query with the "Design view". Add the table tbl_Sales
Your Query window should now have the tbl_Sales table added to it . You have just completed the setup process for making a query. Every time you make a query you have to first choose which table(s) you want to select data from. Currently, our database only has one table, so we don't have a lot of choices here. Now we can begin to create our custom Access query.
