Could you please tell me all the entities and their attributes along with the primary key and the foreign keys also relationships in the model?

General Instructions Read the Case Description below. Once you understand the case start formulating your entities and attributes. You can draw these out on paper as a rough draft and then transfer them to LucidChart. There is a template in LucidChart that you can use for this assignment if you want. You can also create a brand new file and select the ERD template. Decide on a primary key for each entity. Determine how your entities relate to one another and draw the relationship lines. If there is something about the case that needs further explanation, please post your questions in the weekly discussion forum. Case Description You are working for a software consulting firm that specializes in writing custom applications for small businesses. Recently your boss assigned you to the Bobs Bath & Design account, who need an application that will be available on a Windows PC, web site and mobile devise. The application needs a database and that is where you come in. Your boss knows you are taking a database class and needs you to assist the database design and implementation team with the building of the database that will be at the center of this project. Your team has their first meeting with the owner and business manager for Bobs Bath & Design and you are excited to attend. At the meeting the owner and business manager are thrilled your team is starting work on the project. The owner of Bobs Bath & Design explains to you what the database needs to do and how the company operates. Bobs Bath & Design remodels bathrooms. They offer full or partial redesigns. Meaning they can remodel an entire bathroom or just portions of it, like replace a cabinet and sink. They offer for sale stock cabinets and custom cabinets along with fixtures and countertops. There are lots of options when someone choses to remodel. When they get a call for a quote, a sales representative is sent out to the site. The sales representative speaks with the customer to get an idea of what they want done as well as take some measurements. Based on these requirements a quote is generated. This quote is very general and does not include particulars such as what type of tile, fixtures, cabinets, counter tops, etc. This initial visit is to just provide a ball park estimate for the job. These quotes are stored in the database for future reference and follow-up. They are not directly connected to the orders. When a customer decides to go forward with a sale, the database needs to keep track of the sale, items on the sale and which employees assisted with completed project. Your team asks for a sample copy of what the quote would look like and a sample copy of what an invoice looks like. The invoice is listed below:

Page 2 of 3 As you review the invoice you notice that labor is a separate line item on the invoice along with the different purchased products. In a follow-up discussion you ask how labor is charged. If a customer chooses to have Bobs Bath & Design install something, the labor is calculated based on an estimate of how long it should take to install that item. For example, in the invoice above on line item 2, the cost for labor is $100. When installing vanities, it normally takes 2 hours at $25 per hour. Since this is a larger vanity, the amount of time is doubled. All installation prices are listed this way. Each service has an estimate of how much time it should take along with the cost per hour. As your meeting draws to a close, the business manager begins to describe the reports their system will need. The database needs to show which workers went on which jobs. You can see that many workers can be assigned to many jobs and a job can contain many workers. The business manager also needs to run quarterly reports to see which products and services are selling the most. Finally, since the sales team works on commission, the database needs to track the monthly sales of the different sales representatives. You and your team leave the meeting with Bobs Bath and Design and head back to the office. Your team then meets to discuss the materials they gathered and to go over some notes. Your team summarizes the data requirements to be the following: Customers: contact data (first name, last name, address, city, etc.) Products: This includes bath fixtures, lights, doors tile, etc. The database needs to store the name of the product, its description, retail price and wholesale price. It also needs to track who the vendor is. Services: This includes all of the labor for all of the different installations. Labor is charged based on the estimated time that item should take to install multiplied by the cost per hour. Vendors: contact data and the main person who is called or consulted. Quotes: the date the quote was taken, the customer and the sales rep. who took the quote. Additionally, the quote will have a projected start date and the various items being quoted. Orders: order date, start date, end date, sales rep. any services and items on the order. Additionally, the database needs to store which employee completed which item. Employees: contact data Your team ends your meeting by dividing up who will compose the various parts of the ERD. You agree to meet in a week to discuss your draft so that you can schedule a follow up meeting with the owner and business manager of Unique Bath and Design.