Chapter 10 – Database Table Design
INTRODUCTION
A relational database is made up of a series of tables of data (think of spreadsheets from Excel) which are inter-connected in some fashion. The database engine, MS Access in our case, will store the data in these tables and keep track of those connections. We will also have things such as queries, forms, and reports in an Access database.
For this chapter, we need to understand the following concepts and skills to get started in data modeling:
- Understand RDMS basics, including MS Access basics
- Understand and manage field types for a variety of tables
Introductory Steps
Please rename your username-HomeGoodsStore database file by replacing “username” with your IU username. The instructions here on out will still call this file username-HomeGoodsStore, but know we are referring to your own file you’ve renamed.
Answer these questions as we go through the basic introductory materials for the relational database model.
- A database is basically a collection of ______________________, not on paper, but on a computer.
- Access does not keep all the data in one big long list. Instead it uses _____________________ to list things in a little more detail.
- The databases you’ll be working with in Access can actually understand how different lists and their contents relate to one another. This is called a ___________________________ database.
Guided Exercises
Use the username-HomeGoodsStore database to learn how to use sorting and filtering tools in tables.
- How many customers are from Indiana?
- How many states are in the West region?
- What is employee Anthony Gardner’s cell phone number?
- How many employees have medical benefits, but not dental benefits?For the following questions, you may need to do some of the work below and come back.
- How many orders were shipped in April of 2012?
- How many items were in the order that Kaden Kirkland made?
- What is the salary of the employee who sold the largest order on 12/24/2011?
Close all the tables. DO NOT save any changes because you were just using the sorting and filtering tools.
tblCustomer table
- Each customer will be listed once and only once. Two or more customers should never have the same CustomerID, but every customer should be given a CustomerID. Set the CustomerID field as the primary key for this table.
- Save space in the database by changing the field size for the customer’s First Name to 20.
- A customer’s last name should never be more than 40 characters long. Change the appropriate property to save space in the database.
- Change the appropriate property so that all the last names appear in uppercase letters.
- Change the appropriate property so that the names of the city in which the customer lives are not allowed to be longer than 30 characters.
- The State field will be the postal abbreviation for the state in which the customer lives. Change the appropriate properties to save space in the database and so that the state abbreviations always appear in uppercase letters.
- The Zipcode field will never be used in calculations. Change the data type to allow the field size to also be changed to the maximum number of characters needed for a United States zip code.
- The PhoneNumber field will also never be used in calculations. Change the data type to allow the field size to also be changed to the maximum number of characters needed for a United States phone number that is written in standard form, such as (812) 855-4848.
- Change the data type for the EmailAddress field so that it appears as a clickable link.
- Add a field called Notes and choose the appropriate data type so that lengthy entries can be added as needed.
Save and close the tblCustomer table.
tblOrder table
- The combination of a ReceiptID and a CustomerID should always be unique. Set these two fields as the primary key. Since two fields are being used, this is called a composite key.
- Choose the appropriate data type for the PurchaseDate field. Format this field so that the dates appear as Medium Dates.
- Change the field size of the Quantity field to Integer.
- Identify the longest entry in the Status field. Change the field size to allow for that status type but nothing longer than that.
Save and close the tblOrder table.
tblRegion table
- Use the appropriate tool to designate the StateAbbr field as the unique identifier for each record so that no state can appear more than once. Also change the field size to an appropriate number of characters.
- Identify the longest entry in the StateName field. Change the field size to allow for that state’s name but nothing longer than that.
- The RegionID field will be a one-digit character that groups states into regions. Since there will be no calculations done with the RegionID value, change the data type to allow the field size to be set to 1.
- Identify the longest entry in the RegionName field. Change the field size to allow for that region’s name but nothing longer than it.
Save and close the tblRegion table.
Make sure all objects are closed. Close the database. Double-check the Last Modified date for your database file.
More Practice
Work you should do before our next class
Open your username-HomeGoodsStore database.
tblEmployee table
- How many full-time employees are there?
- In what building does Desiree Snow work?
- How many employees have a salary of at least $70,000?
- How many hourly employees are there in the Manufacturing department?
tblOrder table
- How many orders containing no more than 5 items were shipped?
- How many orders were placed between 11/25/2011 and 12/24/2011?
Note: In Access, “between” is inclusive, meaning, the dates given should be included in the results.
tblProduct table
- How many natural colored furniture items does the store carry?
- How many of the store’s products cost less than or equal to $100?
Using multiple tables
- Give the first and last names of one of the customers who had the highest order quantity. As you do this, record notes about what tables you are using and what fields you are using to get to your answer.
- What is the name of the product that Brynn Hinton cancelled from her June 2012 order? As you do this, record notes about what tables you are using and what fields you are using to get to your answer.
- What is the name of the product that employee Alicia Franklin sold the most of? As you do this, record notes about what tables you are using and what fields you are using to get to your answer.
Close all the tables. DO NOT save any changes because you were just using the sorting and filtering tools.
tblEmployee table
- Use the appropriate tool to designate the EmployeeID field as the unique identifier for all the records in this table.
- Set the appropriate property so that First Names can only be 20 characters in length.
- Set the appropriate property so that Last Names can only be 40 characters in length.
- Identify the longest Building. Change the field size to allow for that building but nothing longer than it.
- Set the appropriate property so that Department names appear in uppercase letters.
- Identify the longest Status. Don’t forget that a space counts as a character. Change the field size to allow for that Status but nothing longer than it.
- Choose the appropriate data type for the HireDate field. Format this field as a Medium Date.
- For all three Benefit fields, change the data type to Yes/No so that a check box appears in those fields.
- Change the Salary field to the appropriate data type to reflect that these values represent amounts of money.
Save and close the tblEmployee table.
tblProduct table
- Notice that the ProductID field is already set as the primary key. Its data type is Autonumber. This means that when a new product is entered into the field, the data entry person does not have to assign a ProductID to that product, it will automatically be given a unique ProductID by the database. Do not make any changes to the ProductID field.
- Use the appropriate property so that the ProductName cannot be longer than 75 characters.
- Identify the longest Department. Don’t forget that a space counts as a character. Change the field size to allow for that department name but nothing longer than it.
- Identify the longest Category. Change the field size to allow for that category name but nothing longer than it.
- Set the field size for the Size field to 20.
- Change the data type of the Price field to reflect that these values represent amounts of money.
- Change the data type of the Description field so that lengthy data entries can be made.
Save and close the tblProduct table.
Make sure all objects are closed. Close the database. Double-check the Last Modified date for your database file.