Reference no: EM132472246 , Length: word count:2000

Assignment: Building a Relational Database for Efficiency

This assignment is an extension of the scenario described in the Collaborative Exercises starting on page 150 of the textbook. Before starting the assignment, you should read the opening paragraphs of the exercise in the textbook for some additional background, but you will complete the requirements of the assignment according to the instructions below. Note that the spreadsheet presented in Figure 5-15 on page 151 is a sample on which your dataset is based; as such, the image may not show exactly what the datafile contains. Also note that this assignment is to be completed individually, not as a member of a group or team as stated on page 150.

Background:

Currently, choir members use an Excel spreadsheet to track the use of sheet music, and this system is causing them frustration. The data suffers from some quality issues and other problems. For example, choir members are allowed to take sheet music home for practice, but this occasionally results in its loss. Some of the data quality issues experienced are errors in assigning the same sheet to multiple members, and recording data in the wrong field of the database, resulting in inconsistencies.

Over time, as the spreadsheet grows, so will the issues. Members will have difficulty retrieving information quickly and conveniently. They need your advice as an information systems expert to help them with their data storage and tracking issues. As an analyst/designer, you will review the current Excel file, and then design and build an Access database that will be a more efficient and effective method of handling the data.

Instructions:

Your first challenge is to make sense of the data stored in the Excel file. After downloading the Excel spreadsheet, spend some time analyzing the data it stores. Think about the problems it is causing the choir members and how a database can help minimize or eliminate these issues. Use the information on database design in Chapter 5 and Chapter Extension 5A of the textbook to inform and support your decisions.

If you wish, you may use this SolomonEnterprise Excel file to follow along or practice importing data into an Access database.

You will need to transform the Excel data into a proper Access database. Keep in mind that retyping the data from a spreadsheet into a database is not the best use of your time and it can introduce data errors. The data in the Access database must match the Excel data (except for the obvious data inconsistencies). You can import the data directly from the Excel spreadsheet.

In Access, you will create database tables from the data stored in the Excel file you downloaded. To help you get started, we suggest that you create the following tables/fields:

ChoirMember

MusicalWork

CheckOut

In database design, each table should relate to a specific entity or event; that is, tables are often created for each noun (person, place, or thing). It is essential to eliminate redundancy as much as possible. This means that the data should appear in only one place, which results in less storage space used and, because data will only need to be entered (or changed) in one place, there will be fewer opportunities for inconsistencies or errors.

Next, you should create queries and reports that show

the name of each person who has music currently checked out, along with the name of the music (NameofWork) and the CopyNumber. This report should be sorted alphabetically by first names.

a list of all the works on file with the number of copies of each. This report should be listed alphabetically by Composer.
the most popular piece of music (the one that has been borrowed most often).
all the copies of Star Wars by J. S. Bach, whether they are checked out, and if so, by whom.

In addition to these queries, look at the data and create two additional queries, with associated reports, that show some information that would be useful for the Choir Director's decision making.

Finally, you will write an analytical report to the Choir Director. This report should be formatted as indicated in Writing An Analytical Report. Ensure that it includes responses to the following:

Explain the resulting problems the Choir Director will experience from the data integrity issues in the spreadsheet.

Include a list of your tables, including their field names, clearly identifying all primary and foreign keys.

How will your design and implementation of a database solve these issues?

Describe the considerations management will need to take into account to successfully implement the change from the Excel spreadsheet to the Access database system.
Important note:

After you have created your database file, click FILE? SAVE AS?, then SAVE the file to your hard drive according to the following assignment naming standards:

yourlastname_CMIS351_assign1.mdb
OR (depending on the file format default of your version of Access)
yourlastname_CMIS351_assign1.accdb
Once the file has been created and completed, you can upload it in Moodle.

Note: If you do not have access to a computer on which you can install Microsoft Access, you may use another database tool. If you do this, you must also submit the following:

an E-R diagram of your database that clearly shows the primary and foreign keys for each table
a screen shot of the first page of the contents of each table. Alternatively, you may create a report that dumps the contents of a table and submit a single page of that report.
queries that you designed to create each report. Show both the query design and the query output.
the reports required in the assignment

Attachment:- Building a Relational Database for Efficiency.rar