Project Database Implementation

Implement your database structure for the project in Access.

To ensure referential integrity, define each foreign key in terms of the lookup wizard:

• Make sure the two fields have the same name
• Select lookup wizard for data type (bottom of the drop down list)

• Check “I want the lookup column to look up the values in a table or query”; Next
• Highlight the table where the foreign key resides as a primary key
• Select all the fields that you want to become columns in your lookup column, typically id and name; Next
• Keep the default check on “hide key column”
• Finish.

To be able to define a field as percentage, define it as number; go down under field size: single; under format: percent.

Give each table a name indicating that it is a table (use tbl as a prefix or suffix) to distinguish it later from the same name attached to a query, form, or report.

Maximize the relationships window. If the tables are not there, click the right mouse button and bring them all there. Move them around so they are all logically organized and close to tables to which they are related. Minimize the number of lines crossing one another. Make it look beautiful! Also extend the tables

• Vertically, so that every field in each table can be seen (scroll bar is removed)
• Horizontally, so that the full name of every field is visible

Sometimes when you make changes to your database design, Access places extra tables in your relationships diagram. If the diagram shows extra tables that you want to delete, you can do so only by deleting the relationship (connecting line) first, and then deleting the table itself. Make sure you save the changes afterwards.

Press the relationships icon. Make sure the relationships are all there with referential integrity set to yes for each relationship.

Deliverable

Print the relationships diagram. Include the full names of those group members who contributed to it.

To print the relationships, do not use the “print relationships” function under File as it gives you little control over the diagram. Instead, press the <print screen> key on your keyboard to copy the picture appearing on the screen. Then paste it into a Word document. Using the picture toolbar (under view, toolbars), crop the picture, resize and print it.

Project Data Entry

As you add more and more functionality to your project application, it would be desirable to get a sense of the output/outcome for each chunk of functionality. Hence, you need to populate your database with some sample data.

You will start with your “outer” master files (such as customer type and material type), move on to the “inner” master files (such as customer and material), and then move to the very core, which are the transactions.

First enter the sample data. Then, for each table (except for the Status lookup table), enter an equal number of records of your own made-up data. For instance:

• Two worker records are given; enter two similar records of your own
• One job record is given, enter a similar one of your own.

Deliverable

Tables, populated with data conforming to the above guidelines. To save paper, feel free to show more than one table on one page. Please make sure all pages are stapled together.