"

Chapter 1 – 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:

  • Setup a cloud storage space using IU’s Onedrive licensing to store and manage K160 files
  • Store K160 files in cloud storage and properly understand file names and modified-date attributes
  • Understand RDMS basics, including using MS Access
  • Understand and manage field types for a variety of tables

File Storage and Management
Basic Access Setup
More Practice

File Storage and Management

  1. Let’s go to https://cloudstorage.iu.edu (or find Cloud Storage on One.IU) and connect our IU Onedrive to the IU cloud storage configurator.
    • Click to login with your IU username and password
    • Click the arrow next to OneDrive – IU
    • Click Authorize
    • Login to your IU Office account as needed
    • It should look like this when you’re done:
  2. Mac & Chromebook users, when using IUanyWare, choose either the IUanyWare Desktop:
  3. When you are logged into a computer lab computer go to This PC or My Files and your IU OneDrive will be there as a Network Location:
  4. When you are not in class, Chromebook and Mac users MUST use IUanyWare to virtually connect to a Windows computer.

If you have your own Windows computer, make sure it has Office 365 on it. You can go to office.iu.edu to download and install Office 365 for Windows.

  1. If you have your own Windows computer and you already have a personal OneDrive account set up, you can add your IU OneDrive account, too. Try to follow these steps to add your IU OneDrive as a mapped location:
    • On your personal computer, double-click This PC
    • Right-click on your personal OneDrive and select Settings.
    • Go to the Account tab and select the button to Add an Account.
    • Login with your username@iu.edu email address and passphrase.
  1. Do NOT do any of the following:
    • Use Access or Excel in your browser. If you see either Access or Excel open as a tab in your browser, that is not the correct way of using it.
    • Use Access or Excel directly on a Mac. Mac versions are not the same as Windows versions. You need to use the Windows versions of the software.
    • Use Access or Excel through apps on a Chromebook. Again, these are not the same as the Windows versions. You need to use the Windows versions of the software.

Basic Access Setup

Please rename your username-Haptown database file by replacing “username” with your IU username. The instructions here on out will still call this file username-Haptown, 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.

  1. A database is basically a collection of ______________________, not on paper, but on a computer.
  2. Access does not keep all the data in one big long list. Instead it uses _____________________ to list things in a little more detail.
  3. 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-Haptown database to learn how to use sorting and filtering tools in tables. 

  1. How many consultants are from Minnesota? 
  2. How many buildings are in excellent condition?
  3. What is employee Harold Butler’s street address?
  4. How many employees have medical benefits, but not retirement?
    For the following questions, you may need to do some of the work below and come back.
  5. How many feedback submissions came in on April 3?
  6. How many feedback submissions is Olivia Shaw responsible for?
  7. What is the salary of the employee from the Fire Department who has been with Haptown the longest?

Close all the tables. DO NOT save any changes because you were just using the sorting and filtering tools.

tblEmployee table

  1. Each employee will be listed once and only once. Two or more employees should never have the same EmployeeID, but every employee should be given a EmployeeID by default. Set the EmployeeID field as the primary key for this table.
  2. Save space in the database by changing the field size for the employee’s First Name to 20.
  3. A employee’s last name should never be more than 40 characters long. Change the appropriate property to save space in the database.
  4. Change the appropriate property so that all the last names appear in uppercase letters.
  5. Change the appropriate property so that the names of the city in which the employee lives are not allowed to be longer than 30 characters.
  6. 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.
  7. HireDate
  8. Salary

Save and close the tblEmployee table.

tblConsultant table

  1. ConsultantID
  2. Email
  3. ZipCode
    • 4-digit correction
  4. HireDate
  5. HourlyRate
  6. Certifications

tblAsset table

  1. AssetID
  2. AcquisitionDate
  3. AcquisitionCost
  4. CurrentValue

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-Haptown database.

tblEmployee table

  1. How many employees have medical plans?
  2. In what building does James Moore work?
  3. How many employees have a salary of at least $70,000?
  4. How many employees are there in the Library department?

tblConsultant table

  1. Which consultant lives in Colorado?
  2. How many consultants have some sort of Microsoft certification?
  3. How many consultants are paid less than $90/hour?

tblAsset table

  1. Which asset is in fair condition and only has 5 years left of usefulness?
  2. How many assets does the main firehouse have?
  3. How many assets cost no more than $200 to purchase initially?

Using multiple tables

  1. Name a consultant assigned to a feedback submission from a cyclist.
  2. Give the first and last names of an employee who has easy access to an iPad Mini. As you do this, record notes about what tables you are using and what fields you are using to get to your answer.
  3. What is the name of the vendor name of someone who would normally interact with Officer Jose Campbell? As you do this, record notes about what tables you are using and what fields you are using to get to your answer.
  4. Name an employee who lives in the same city as the ITIL Foundations certified consultant. 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.

tblMarketVendor table

  1. ContactEmail
  2. PhoneNumber size and add 812?
  3. Website
  4. ZipCode size

Save and close the tblMarketVendor table.

Make sure all objects are closed. Close the database. Double-check the Last Modified date for your database file.

License

K160: Introduction to Business Decision Making 2e Copyright © 2024 by Trustees of Indiana University. All Rights Reserved.

Share This Book