Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING THE DATABASE DESIGN
PMD has proprietary software to track its customer records and the precious metals. PMD is fairly
confident that the software accurately tracks the extensive movement of bullion and coins in and out of the
facility. However, PMD would like an additional system that tracks this movement and the guards on duty while
the precious metals are being moved. You have been hired as a summer intern to design a small database
system that records the hours each guard works at PMD and records the movement of precious metals in and
out of the building.
The owner of PMD, Jerome Argent, begins by explaining aspects of the business that are important to
your database design. Guards work around the clock at PMD, patrolling the main entrance to the building.
Once a person enters the highly secure building, he or she must use biometric identification to enter any
vault area. After a person enters the vestibule of the vault, the entry door must be closed before the door to
the vault is opened. At least two guards are on duty at all times. All guards have undergone background
checks. Information about the guards, such as their names, addresses, and phone numbers, is kept on record.
Two guards might have the same name.
Guards clock in using a time card system before each shift and clock out afterward. Guards are paid on
an hourly basis. They watch the movement of precious metals in and out of the building, and they record
information about each delivery and withdrawal. This information includes the customer number (names are
kept secret), the amount deposited or withdrawn, and the time. PMD prides itself on accurately recording the
movement of goods.
Next, Jerome explains the different products stored in the vaults. Each precious metal comes in two
formats: bullion, which is shaped like a brick or bar, and coins. The precious metals bought and sold are gold,
silver, platinum, and palladium.
Jerome has a list of requirements for the database system. First, when guards record the movement of
goods in and out of the building, they should have a computerized method of entering the information rather
than writing it down. You suggest forms as a vehicle for this input.
Other information can be gleaned from queries. For example, Jerome would like to see which guards are
working at specific dates and times, in case any suspicious activity or problems are reported. He is also
curious to know which precious metals are the most popular. For example, which metal is in greatest supply at
the depository? For each metal, which is more popular: bullion or coins? Which metal is most frequently
delivered, and which is most frequently withdrawn? Jerome would like the database to be able to break down
deliveries and withdrawals by metal type and by shape (bullion or coins). You suggest a parameter query for
each of these questions. The queries can prompt the user to specify a type of metal and specify whether it is
held in bullion or coins.
Jerome would also like you to calculate the hours worked by each guard over a specified period of
months, one month at a time. In addition, he wants you to calculate the guards
gross pay for a month and
compute the city tax for guards who live in Los Angeles. You confidently state that queries can handle these
requests, including calculations. You also suggest that some queries might be displayed through the report
generator to provide a more polished output and to group the data by guard.
Finally, Jerome tells you that some customers cancel withdrawals at the last minute, depending on how
the market is trading that day. As the withdrawal is being executed and the guard is recording the
particulars, it might be reversed. Jerome would like a fast way to delete a withdrawal recorded by a guard.
ASSIGNMENT 1: CREATING THE DATABASE DESIGN
In this assignment, you will design your database tables using a word-processing program. Pay close attention
to the tables
logic and structure. Do not start developing your Access code in Assignment 2 before getting
feedback from your instructor on Assignment 1. Keep in mind that you will need to examine the
requirements in Assignment 2 to design your fields and tables properly. It is good programming practice to look at
the required outputs before beginning your design. When designing the database, observe the following
First, determine the tables you will need by listing the name of each table and the fields it
should contain. Avoid data redundancy. Do not create a field if it can be created by a
in a query.
You will need a number of transaction tables. Think about the business events that will occur.
Avoid duplicating data.