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? This is for SQL 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. Finishing Up 1) Now that you have read the case, start composing your entities and the attributes. 2) Assign a primary key to each entity. 3) Determine which entities relate to one another and place your foreign keys appropriately. 4) Determine the type of relationship between your entities and draw those in. 5) Read the rubric to make sure you have included everything. Go back and double check your model. Make sure all many to many relationships are properly broken down to one to many. Make youre your attributes are attributes and not data. 6) This diagram needs to be readable. What do I mean by readable? The normal lay person should be able to understand what you are trying to do. Are you lines convoluted? Is it hard to trace how one entity relates to the other? Have you adequately described each entity? Does the diagram fit on one page? Does your diagram have your name on it? 7) Make sure your diagram can fit on one page. Export your LucidChart file to a PDF and upload it to Canvas. 8) Points will be deducted for models that are sloppy, do not have a name on them or do not fit on one page. Page 3 of 3 Rubric pts Exemplary pts Competent pts Developing Did Not Complete or Too Many Errors 5 Correctly drew entities to match all of the themes in the case 4.0 Missing one entity 3.0 Missing more than one entity 0 Missing more than 2 entities or did not complete 3 Identified and correctly assigned Primary keys for all entities 2.4 One entity does not contain a primary key 1.8 More than one entity is missing a primary key 0 More than 2 entities are missing PK or did not complete 3 Correctly identified relevant attributes for all entities 2.4 One attribute is missing or there is an issue with one attribute 1.8 More than 2 attributes are missing or there is an error 0 Missing more than three attributes, errors with more than three attributes or did not complete 7 All attributes in all themes/entities are relevant. Attributes describe the entities and should not be in a different entity. 5.6 One entity contains an attribute that belongs in another entity. 4.2 Two entities have attributes that do not belong. 0 More than two entities have attributes that do not belong or did not complete 8 All many to many relationships are resolved. 6.4 Missing one linking entity 4.8 Missing two linking entities 0 Missing more than two