ACC 3300

Access Assignment

The assignment requires you to use some of what you will have learned about data modeling, REA diagrams, table design and the features of Access. You may do the assignment in 2010 version of Access or in the older Office version of Access. The safest way is to use a single version of the software for all of your work.

Consider the following REA (and Entity Relationship) diagram for part of a small Accounting Information System in a business that creates and sells custom watches called Custom Time

Custom Time is a small business that employs several Master Watch Makers, one of whom is the owner of the business. Custom watches are ordered by customers, who call in and place the order with one of the watch makers, since the business is too small to require the services of another employee. Each watch is personally designed by one of the watch makers (sometimes the business owner.) The design determines what parts will be needed to create the watch. The job of creating the watch is then assigned to the master watch maker who designed it.

The REA diagram above reflects the following documentation conventions and cardinalities:

• There is a M:N (many to many) relationship between the Parts Inventory file and the Custom Watch Order file. The notation on the REA diagram means that for each record in the Parts Inventory file there may be no related records in the Custom Watch Order file, or one or many related records. For each record in the Custom Watch Order file, there must be at least one, but may possibly be many related records in the Parts Inventory file.
• There is a 1:M (one to many) relationship between the Custom Watch Order file and the Customer file. The notation on the REA diagram means that for each record in the Custom Watch Order file there must be one and only one related record in the Customer file, that is, each order relates to one and only one customer. For each record in the Customer file, there may be no related order records, or one, but possibly many related records in the Custom Watch Order file. In other words, a given customer may have placed no orders yet, one order, or many orders, each for a different custom watch.
• There is a 1:M (one to many) relationship between the Custom Watch Order file and the Master Watch Maker file. The notation on the REA diagram means that for each record in the Custom Watch Order file there must be one and only one related record in the Master Watch Maker file, that is, each order is being handled by one and only one Master Watch Maker. For each record in the Master Watch Maker file, there may be no related order records, or one, but possibly many related records in the Custom Watch Order file. In other words, a given Master Watch Maker may not be assigned to any of the active orders or be a new employee, but also may be working on one order, or many orders, each for a different custom watch.

As in other types of systems documentation, the conventions used in the creation of entity-relationship, or REA documents may vary somewhat from one IT installation to another, but the variations should be easy to learn and interpret for anyone who has mastered the general meaning of cardinalities. These variations are sometimes also found across AIS and MIS texts.

The Assignment

Your assignment is to complete the following required steps:

1. Using MS Access create the tables as shown in the REA diagram above. Each table must have a unique primary key, such as customer number, order number, etc. The primary key must be identified within each file.
2. Remember that for each M:N (many to many) relationship you need to also create an additional relationship table or join table to represent that relationship. The new table will have a maximum of one record for each related pair of records in the original tables. It is created by using the primary key from each of the original tables to create a composite primary key for the new table. (Note that you can specify a composite primary key in Access by holding the CNTL key while selecting the fields to be included and then clicking on the primary key icon, which looks like a key!)
3. Create relationships among your tables in Access as indicated in the REA diagram. Remember that you cannot create M:N relationships in Access or any relational database. This is done by creation of the relationship/join/junction table. Each of the original tables has a 1:M relationship to the relationship table! Foreign keys must be specified correctly.
4. Once you have established the relationships, create some records in your tables. You should have a minimum of 5 but not more than 8 records in each table unless you have specified a good reason that more records are needed.
5. Create a simple select query using more than one of the tables in your database. Save the query so that I will be able to see it and see the result of running it when I look at your database.

Tips: You will be asked by Access to save your database at the time you create it. If you are working in the lab, you should save it to your flash drive and not use the default, which may be the hard drive of the lab computer. This is much easier than trying to move the finished file later! Also, remember that your database is saved as a single file, so you need to save the whole thing by closing the database file itself, not just individual tables, etc.

Remember that this is an individual assignment which means that, although you may want to work with or confer with friends in the class about the assignment in general, you need to do your own work on all of the required files and parts of the assignment by yourself, from scratch. This means that no two assignments will be exactly, or even extremely close to, the same!