Industrial Data and Systems Analysis
Advanced Relational Database Concepts
You must have completed Recitation 1 before attempting this recitation.
Your teaching assistant will explain or review the following concepts:
Throughput, yield loss, speed loss, line transitions
You will turn in this document at the end of recitation to receive credit for
attendance. If you are working in teams, list the names and NetID’s of you and all
your team members.
NetID (ex. pj16)
Create a directory to store your work. If you use the lab machine, be aware that
the directory (and all your work) will disappear when the machine is rebooted.
We recommend using a USB flash-drive.
Open Microsoft Access (MS Access). It is one of the Microsoft Office collection
Create a blank database and save it in your directory. Give it a unique name by
combining your NetID with the identifier for this recitation: ex.
“pj16REC02.mdb.” We will refer to this database as your working database.
Write down the file name here:
From the course website, download the following file to your directory:
Return to MS Access. Link all tables in “THRUPUTHistory.mdb” into your
working database. (Recitation 1 explained how to link tables.)
Relational Database Design
In a relational database, you should store information in the most efficient way
possible. That is, you want to minimize the duplication of information in a database. For
example, suppose you are logging phone conversations with a customer. When talking to
me, you might sometimes log my name as “Peter Jackson”, or as “PJackson”, or as
“Jackson, Peter”. When you want to analyze the log, you will have difficulty identifying
that all those entries identify the same person. It would be better to have a separate table
of customers with a unique identifier for each customer. That way, I would be in the table
of customers exactly once, say as Customer ID: 007, Name: Jackson, Peter. Then, in the
log file, every time you talk to me, you log it as a conversation with Customer ID 007.
The database “THRUPUTHistory.mdb” uses this approach. There are separate tables
to describe clock codes, problem types, shift names, product names, and workcenter
types. Each row in each of these tables has a unique identifier. [Hint: Design view, key]