BADM 683D Project 2: Designing Relational Database

Due date: December 5, 2004, Friday, at the beginning of class

In this project, you will use Microsoft Access to build a typical relational database. Suppose that you own a small business and all data records now reside in an Excel spreadsheet (download from http://faculty.winthrop.edu/caoq). It is a flat table with data redundancy. You are required to design tables and relationships between tables to minimize data redundancy. This assignment, Access Skill Set Handout, class notes, and Chapter 6 provide you with useful information.

Part One: Relational Database Design – Legacy System Reengineering

This part includes table design, data field design, and schema of relationships. You transfer a flat table to an Access database. You start making blueprints – the standard notations for your database, and then implement your design with Microsoft Access.

I. Design tables

In this stage, you will (1) split a flat table into tables in order to reduce redundant data; (2) create primary key for each table; (3) identify relationships among those tables and correctly connect them together; (4) present your design with the standard notations (learn methods from “Demo_Computer.xls” in the zip file downloaded from my Web site), and (5) implement the design with Microsoft Access.

Before you start your design, you download an Excel file from my Web site under Project 2 for your group:

Group 1: Sales Department Group 2: Mall Maintenance

Group 3: Job Contract System Group 4: Billing System

The following information includes: entities, data fields for each entity, candidates for primary keys, and relationship between entities. You design database on the basis of information about your flat table.

a.  Demo_Computer: The flat table includes information about three entities - Customer, Order and Product. The data fields in the flat table are: (1) the field names with pink color for Customer, (2) yellow for Order, (3) green for Product, and (4) Quantity (gray) is the units of a product required by an order. The candidates for primary keys are: (1) CustomerID uniquely identifies a customer; (2) OrderID uniquely identifies an order; and (3) ProductID uniquely identifies a product. Note that Quantity is the units of a product required by an order. The entities have the following relationships: (1) one customer may place more than one order while one order only belongs to one customer; (2) one order may require more than one product while one product may be required by more than one order.

b.  Sales Department: The flat table includes information about four entities - Salesperson, Sale, Customer and Product. The data fields in the flat table are: (1) the field names with green color for Salesperson, (2) yellow for Sale, (3) light green for Customer, (4) blue for Product, and (5) Quantity (gray) is the units of a product for a sale. The candidates for primary keys are: (1) SalepersonID uniquely identifies a salesperson; (2) SaleID uniquely identifies a sale; (3) CustomerID uniquely identifies a customer; and (4) ProductID uniquely identifies a product. Note that Quantity is the units of a product for a sale. The entities have the following relationships: (1) one customer may be involved in more than one sale while one sale is done with one customer; (2) one salesperson may take care of more than one sale while one sale is done by one salesperson; (3) one sale may require more than one product while one product may be required by more than one sale.

c.  Mall Maintenance: The flat table includes information about three entities - Location, Job and Repairer. The data fields in the flat table are: (1) the field names with green color for Location, (2) yellow for Job, (3) light green for Repairer, and (4) Effort (gray) is the number of hours for a repairer to complete an job. The candidates for primary keys are: (1) LocationID uniquely identifies a location; (2) JobID uniquely identifies a job; and (3) RepairerID uniquely identifies a repairer. Note that Effort is the number of hours for a repairer to complete a job. The entities have the following relationships: (1) one location may have more than one job while one job is only for one location; (2) one job may need more than one repairer while one repairer may be assigned to more than one job.

d.  Job Contract System: The flat table includes information about four entities - Job, Direct Material Inventory (DMI), Customer and Finished Goods Inventory (FGI). The data fields in the flat table are: (1) the field names with yellow color for Job, (2) brown for DMI, (3) light green for Customer, (4) gray for FGI, and (5) Quantity (pink) is the number of units of DMI item used for a job. The candidates for primary keys are: (1) JobID uniquely identifies a job; (2) DMStockID uniquely identifies a DMI item; (3) CustomerID uniquely identifies a customer; and (4) InventoryItemCodeID uniquely identifies a FGI item. Note that Quantity is the number of units of a DMI item for a job. The entities have the following relationships: (1) one job is for only one customer while one customer may order more than one job; (2) one job requires only one FGI item while one FGI item may fill orders of many jobs; (3) one job may need more than one DMI item while one DMI item may be used by more than one job.

e.  Billing System: The flat table includes information about four entities - Firm, Deposit, Project and Employee. The data fields in the flat table are: (1) the field names with green color for Firm, (2) yellow for Deposit, (3) light green for Project, (4) blue for Employee, and (5) Billable Hours (gray) is the number of hours for an employee to work on a project. The candidates for primary keys are: (1) FirmID uniquely identifies a firm; (2) DepositID uniquely identifies a deposit; (3) ProjectID uniquely identifies a project; and (4) EmployeeID uniquely identifies an employee. Note that Billable Hours is the number of hours for an employee to work on a project. The entities have the following relationships: (1) one firm may make more than one deposit while one deposit can only be made by one firm; (2) one firm may contract more than one project while one project is only for one firm; (3) one project may need more than one employee while one employee may be assigned to more than one project.

You now start working on your flat table and design a database based on information about the flat table.

1.  Split Table

You first read and understand the standard notations in Demo_Computer.xls. Then, use information about your flat table to do the following:

(1)  Create one table for each entity: table name and data fields.

(2)  Create table(s) for field(s) that are inappropriate for the above tables. For example, the field Quantity in Demo_Computer.

2. Create Primary Keys and Normalization

(1)  Create primary key for each table. A primary key is field or a combination of fields that uniquely identifies a record. Use information about your flat table to identify primary keys.

(2)  Check each table to determine if it is in the first normal form, second normal form, and third normal form (read Chapter 6) and present your conclusion in your written report.

3. Identify Relationship and Connect Tables

(1)  Identify relationship between two tables with information about your flat table. For example, in the flat table “Demo_Computer”, a one-to-many relationship exists between customer (“one” side) and order (“many” side), and a many-to-many relationship between order and product.

(2)  Connect tables when a one-to-many relationship exists. The primary key at “one” side serves as common field(s) to connect two tables. Simply add the common fields to the table at “many” side as a foreign key.

(3)  Connect tables when a many-to-many relationship exists. Create a third table, if no such table exists, so that the many-to-many relationship can be broken down into two one-to-many relationships. The third table at least includes the primary keys from both sides so the two sides can be connected through the third table. Mostly the combination of primary keys from both sides will be the primary key of the third table.

4. Present Design with the Standard Notations

The standard notation presents your design of relational database. It provides information of table names, data fields of each table, primary key of each table, foreign key of a table if it exists, and relationships between tables. For example, refer to the standard notations in Demo_Computer.xls.

5. Implement the Design With Microsoft Access

You now transfer the tables in the standard notation to the tables in Access database. The Make-Table Query of Access helps you split the flat table and delete duplicate records.

(1)  Upload the flat table to Access database

a.  Create a database and save it with the name of Excel file you have downloaded (refer to Exercise 1 of Handout: Access Skill Set 1).

b.  Import the flat table to your database:

a)  Click Table object, Files menu, select Get External Data and then select Import.

b)  Select Microsoft Excel from Files of Types drop-down menu. Select drive, directory and filename of Excel file for your flat table. Click Import button.

c)  Check Show Worksheets radio button, select the worksheet for your flat table in the text box and then Next button.

d)  Check First Row Contains Column Headings, and Next button.

e)  Check In a New Table radio button and Next button.

f)  Select “No” in the Indexed text box and Next.

g)  Check No Primary Key radio button. The primary key will later be assigned by you. Next button.

h)  Type table name (e.g., Flat Table) and Finish button.

(2)  Run Make-Table Query to spilt the flat table

a.  Create a query for creating a new table. For example, create a table “Customer” in database “Demo_Computer” with the flat table uploaded. First, create a query in design view (refer to Exercise 5 in Handout: Access Skill Set 1), add the flat table to the query and select data fields according to the table “Customer” presented in the standard notation.

b.  Make the query a Make-Table Query: click Query menu and select Make-Table Query command. After Make Table window pops up, type the table name in Table Name text box and OK. Right-click on the design grid and select Properties. After the Query Properties window pops up, set “Yes” for Unique Values box and close the query properties window. Run the query and then close it with no save.

c.  Specify a primary key for this table according to the standard notation.

(3)  Repeat Step (2) until all tables in the standard notation are created.

(4)  Delete the flat table fro Access database.

II. Design data fields

The purpose for design of data field is to reduce update and change anomalies. If you have identified pattern for values of a data field, you can change some properties, such as data type, field size, validation rule, validation text, etc. (refer to Exercise 4 of Handout: Access Skill Set 1). For example, in Exercise 4, a pattern exists for Loan ID: four-character long, and L followed by three digits. Therefore, properties, such as Data Type “Text”, Field Size “4”, Validation Rule of “Like”, and the message sent through Validation Text, serve as the gate guard against data entry errors. First, check to see if any patterns exist for some data fields. Second, reset some properties of those data fields to protect your tables from data entry error. Third, you later in your written report discuss why your property settings can protect your tables from data entry error and present data dictionary (refer to Exercise 4 of Handout: Access Skill Set 1) for those data fields.

III. Create a schema for relationships

You have to complete the above steps before you do the following. Otherwise, you may need to delete the relationships in schema before you make changes to properties of data fields and restore the relationships after the changes are made. Create relationships as designed in the standard notation (refer to Exercise 5 of Handout: Access Skill Set 2). To this point, the database on paper (standard notation) comes true (Access).

Part Two: Application and User Interface

This part includes query design, report design, and interface design. The purpose of this stage is to make a user-friendly database application.

I. Create queries

Users will use queries to extract information as answers to their questions. You may read Exercises 5 through 7 of Handout: Access Skill Set 1 before you create regular and parametric queries. Your written report should include a “wish list” of questions that each query answers. You are required to create four queries. Three of them must answer the following questions and the fourth query answers a question made by you. Each question should include information about (1) criteria to select record, (2) whether the value of criteria is specified by users, (3) the data fields to show, and (4) how to sort. The following is my “wish list”:

1. If you work on Sales Department, you must create queries for the following questions:

§  Who made the sales with Sales ID “12”? Show fields in the order of Salesperson’s "FirstName", "LastName", “Product Description” (ProdDesc), and "Quantity". Data should be sorted ascending by Product Description. Save the query as “Who made sales”.