How will your design and implementation of a database

Assignment Help Database Management System
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

Reference no: EM132472246

Questions Cloud

Write about fractional ownership and its relation : Write at least 500 words on fractional ownership and it's relation to cloud computing. Use at least one example from another industry.
Describe the perspective of the kung : Describe the perspective of the Kung on sex, sexuality, extramarital affairs, and the female body and compare the!Kung perspective on sex and sexuality to your
Estimate the population mean amount : A random sample of 50 cans is selected, Construct a 99% confidence interval estimate for the population mean amount of paint included in a 1-gallon can
Determine and post journal entries into t-accounts : Determine and Post journal entries into T-accounts (make sure you have a total amount for each account). There should be no hardcoded numbers on the T-accounts
How will your design and implementation of a database : Explain the resulting problems the Choir Director will experience from the data integrity issues in the spreadsheet and How will your design and implementation
What adjusting entry is needed on December : A company acquired a building on January 1, at a cost of $1,000,000. What adjusting entry is needed on December 31 to record the depreciation for entire year
Compute ptrs book and tax basis in the equipment : Several years ago, PTR purchased business equipment for $50,000. Compute PTR's book and tax basis in the equipment. Compute PTR's book
Determine would kyle be considered a resident of canada : Determine would Kyle be considered a resident of canada for the current year because he has primary residential ties to canada?
What is the cards EFF : Annual Percentage Rate (APR) on their monthly statements. If the APR is stated to be 13.50%, with interest paid monthly, what is the card's EFF%?

Reviews

Write a Review

 

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd