Free quote
CIS377 Database Project Little Shop of Music You have been hired by a company called the Little Shop of Music that is designing a system that allows customers to find music they are interested in both in their shop and online. Little Shop of Music wants you to design and implement a database to keep track of the organization’s operational data described below. Little Shop of Music’s system will allow customers to come into the store and purchase LPs or CDs or order them if they are not in stock. Also, for a small fee, the store will search for digital downloads. Although Little Shop of Music specializes in hard to find music, it will help customers find any type of music from pop songs to classical pieces to alternative. For music that is not in the store or for digital downloads, Customers can submit a request which has the customer’s name, the customer’s number (if known), the name of the musical work, the name of the musical performer (or performers) (if known), the date it was released (if known), and the type of product the customer desires. The store will then find the musical work. If the work is not in the store’s database, it is added. The following are descriptions of operations compiled from interviews with employees representing multiple levels of the organization: · Important information that needs to be captured about the musical works is: name of the musical work, name of the performer (or performers), songwriter (or composer), date it was released, and type of recording. · Musical works can be one of three types: CD, LP, or digital download. · For CDs, the store needs to know whether it is in stock or not. If it is, the current price is part of the information the shop maintains. · For LPs, the store needs to know whether it is in stock or not and what kind of condition the LP is in. Again, if it is in stock, the price has to be maintained. · If the CD or LP is not in stock, an employee will search the Little Shop of Music’s vendors to see if they sell the work, and if the vendor sells the musical work, the employee has to capture the vendor and the price. · For digital downloads, a customer pays an upfront fee for the search, and an employee searches for the work, captures a download location (a webpage URL), and emails the customer the location. · The store also provides ratings for the music it provides. Each musical work has a rating (from one star to five stars) and a comment. Reviews are submitted by the customers (aka reviewer). Each reviewer may submit multiple reviews, but only one review is allowed per musical work. · Little Shop of Music wants to at least know each customer’s name, address, email address, and phone number. · Little Shop of Music offers a certification course which trains employees how to appropriately verify the condition of the LPs. Some, but not all employees will take the training and earn a certificate. The date of certification is important. · LPs must be certified by an employee of Little Shop of Music before they are placed in the store to be sold. Whether or not an LP is certified is tracked in the database. In order to certify LPs, an employee must have earned a certification. Not all employees take the certification training. Employees may certify several different LPs but each LP only requires one certification before being placed in the for sale bins. · The typical information is captured about Employees including their hire code (provided in the table below.) They also have roles within the organization. Since the company is small, employees will have more than one role and roles may span departments. Roles include executive, salesperson, marketer, R&D, accountant, manager, and staff. Little Shop of Music wants to track which employees worked in specific roles throughout their careers at Little Shop of Music and the salaries they held over time. An employee’s salary is based on the most recent role the employee as been assigned. · Little Shop of Music consists of several departments (Executives, Accounting, Sales, Marketing, and Procurement). The Executives who make the strategic decisions for the store. Sales is responsible for seeking out customer interest and selling the music. Marketing creates and distributes promotional items and tracks data on popularity of the music and music media. Procurement is responsible for developing and maintaining vendor relationships. Each employee works for one or more of these departments and each department is managed by a single employee. Deliverables Part 1 Create an executive summary document for your customer (approximately one page write-up). This should be a formal business document which describes your understanding of the request. Create an ERD showing the conceptual design of the proposed database. Be sure to include any information not captured in the ERD. Create a data dictionary document to accompany the physical implementation of your conceptual design. 1. Executive summary 2. ERD diagram 3. Data dictionary Part II Implement your design. This requires creating the SQL statements that build the tables, populate the tables (See the Table Population Section below), and provide the answers to the questions below. You must use these queries to build your database in MS Access. Be sure to test each SQL query using your database. You will submit the MS Access database file with all of the SQL queries saved in the database. Create queries to perform the following information tasks: (These queries are ALL single queries. You must achieve each objective in a single statement) 4. Create and populate the tables of your database 5. Give a report of employees grouped by their department 6. List the employees that are certified to evaluate LPs. 7. Show the name, address, customer number, and gender for customer 19 and 39. 8. List the departments by name and give a count of employees in each. 9. Show how many products are in the store and group them by product type. 10. List all products that were sold and group them by product type. 11. Select a specific product and show the reviews for that musical work. 12. Display each employee, their department, and a list of the role/salaries they’ve held over time. 13. Show the ratings of the musical works. Table Population You should include: 14. Create no less than 50 different musical works with multiple media. The musical works must have been created in a time period of a year or more. Some LPs should be certified, and some should not be certified which means they are not for sale yet. I will put an Excel file of musical works on Blackboard. There are about 500 rows. You can use all of them if you want, but at least 20. Remember, the Excel table I am providing is not the table you will need in the database. 15. Create no less than 61 different customers. Include these as the first 61 employees. Use the Cust ID for the primary key of the Customer table. Cust ID First Name Mid Init Last Name Gender 1 Malena C Wildhaber F 2 Julio P Kadi F 3 Paula T Kumpf F 4 Kristi E Iwashita F 5 Yelena Q Marchand F 6 Johnsie H Lewman F 7 Glayds J Barg F 8 Vera X Paskin F 9 Terri G Poore F 10 Jodee D Cooey F 11 Leopoldo O Bravender M 12 Trudie V Cropsey F 13 Earlean Weare F 14 Vanesa Kary F 15 Caleb R Sao M 16 Oren B Fleck M 17 Brain S Vitatoe M 18 Shavonne U Dubel F 19 Bev K Bloomquist F 20 Latonya O Darroch F 21 Leo Hero M 22 Vada W Rodvold F 23 Latanya Q Younkers F 24 Albertina N Plover F 25 Jerrod Rasulo M 26 Tuan Yeropoli M 27 Jazmin O Cantarano F 28 Dorethea M Teagues F 29 Eulah C Sandora F 30 Kim Larkin F 31 Belkis G Tertinek F 32 Travis W Jedan F 33 Fern K Girardot F 34 Jeanice U Bissell F 35 Mckinley Q Magette M 36 Nydia Swartzmiller F 37 Ronda Perri F 38 Timothy F Eayrs M 39 Jarrod N Vandis M 40 Beau R Wuori M 41 Brenna D Majamay F 42 Alonzo Mansbach M 43 Nga J Dellapina F 44 Ron Huether M 45 Steven L Yearby F 46 Kellee T Kosmatka F 47 Essie P Cerbone F 48 Adena S Orsi F 49 Florinda H Gaar F 50 Gudrun Froschheiser F 51 Willene P Wesolowski F 52 Christian Espina M 53 Curtis Ajayi M 54 Lola Deluccia F 55 Cecil X Ahlemeyer F 56 Ariana J Lichtenberg F 57 Nereida Uzzo F 58 Cleta B Godine F 59 Zaida P Sprake F 60 Madelyn O Leamon F 61 Sherman J Kellermann M 16. Create no less than 20 different employees populating all roles and departments across the organization. Be sure that some employees are able to certify LPs while others are not. Include these as the first 20 employees First Name Middle Initial Last Name Gender Hire Code Haley H Kirschner F A54802 Shayne C Stinton F A51462 Sherice G Greely F A24748 Gabriele J Spehar F A98197 Sachiko Z Allsbrooks F A56407 Alene V Kempa F A89148 Sherise E Sefton F A24029 Maude X Papanikolas F A65651 Melania Lisker F A95172 Mayola X Wigington F A66864 Jae L Savo M B30957 Mira M Dobmeier F B92792 Adrian F Schuckert F B97622 Mechelle P Vitalo F B54943 Tomas Benach M B11146 Rayna Truslow F B25900 Hyman Y Bowthorpe M B10080 Augustine K Poskey M B75102 Cassaundra I Clausell F B58758 Penney Decrosta F B30151 17. Create customer reviews for at least 10 musical works. Some of these should have multiple reviews by different reviewers. 18. Assume any comment will be no more than 155 characters long. I will be using Microsoft Access to check SQL statements.