Course Competencies: 6.1 Design normalized databases, 6.2 Create databases and tables, 6.3 Implement indexes effectively Read all of the Mastery Assessment directions before you get started! Designing a Database ‘The ABC Company completes projects for a number of client companies. A project can have multiple employees assigned to it, and an employee can be assigned to multiple projects. The pay rate is based on the project so an employee can have a different hourly rate per project. Each employee works for a department. Currently the data is kept in a spreadsheet format as shown below. Because the company is quickly growing, they need a more powerful tracking system and have asked you to design a database for them. WR INF Eliminate repeating groups and dent primary keys NF Eliminate paral dependencies whl sill in TNF [Fae perc ks wen etn pends on one porto of the compat primary key ne EEA LTE Dependancy exists when a fed is not avamtrt ery | recommend you read the entire assignment before starting. 1. Design a normalized database for this scenario by sketching out an EER diagram on paper showing the table names, field names, primary keys, foreign keys, and relationships. Refer back to the Database Design and Normalization video and script assigned in this module. 2. When you think you're done, double check the following to help ensure proper design and correct as needed: © Does each table have only one theme? © Does each table NOT have repeating fields? © Does each table have a PK that uniquely identifies each record? © If there's a composite key, are all other fields dependent on the whole key? © Are all other fields in each table dependent on the PK? Has all redundant data been eliminated other than PK and FK fields? Creating a Database 1. Using your database design, create the database in MySQL Workbench. 2.As you write your statements in Workbench, save them into a script file named Last_ModéScriptMAP:sal (Use your own last name). Include comments to describe each statement. © To save a script, use the File menu choose ‘Save Script As. © Start your script as shown below, except use your own database name. This will allow you to rerun the entire script during the development process and quickly recreate the database using your most recent code. Purpose: This detabase tracks projects and the employees that are assigned to thes. : Feb-16-2021 Author: Rhoades ROP DATABASE IF EXISTS Rhoades; CREATE DATABASE Rhoades; use shoadess Note: You'll also be submitting this script. Your instructor should be able to open and execute the entire script without error. 3. Create the necessary tables and include constraints for the primary keys and foreign keys so your table relationships are appropriate. 4. Enter the data shown in the spreadsheet above into your database tables. 5. Using MySQL Workbench, create an EER diagram that shows all tables and their relationships. Make sure you arrange the tables nicely so all tables, fields, and relationship are viewable. 4. Create a screenshot of your EER diagram and save it to a Word or Google document named Last _ Mod6MAP, replacing last with your own last name. Add an appropriate title to the document that includes your name, class, assignment, and date. 7. For each table, use the DESCRIBE command and paste a screenshot of the results into Last Mod6MAP. &. For each table, use the SELECT command to show all fields and all records and paste a screenshot of the results into Last _Mod6MAP. 9. Display all employees sorted by last name and then first name, with a comma and space in-between. Paste a screenshot into Last_ModéMAP. FulName: Gilbert, Tm o | James, by Jones, Lee Jones, Sam 10. Test your table relationships by producing the following report sorted by project code and then employee number. To correctly produce this, you'll need to link all your tables together and display all data from each of them. Make sure to include this statement in your SQL script along with all your other statements. Procode _mognte Proty Profudget Erte Enlast_Enghrst Woshiate DepiCode _Deptame. PCO PeworsSiten 5 MX0 SONI Sm Ame 200 oT POO PewersSystm SMSO SWOX Jones lee BS) L023 pews PCO PewoSuten 5 M®0 S200 lews Pat 200 WM I POMS SewesSsten 10 DMO SKOOL Sh Ame 1800 loa IT © POMS Ssessysten 0 DEO SII does Sem 27S W064 M0 PMS Suaesyten 10 DMD SIO Rdwds Jae U0 loos Say PCOS SuaesSystem 10 DA SIOZ Ghet Tm 2580 LB Dstaoase POS MSE 8 0 SUOM dees Bh 60 Le Mm PCG Spm 8 0% S00 Lews Pst US lI PCA MS 8 0 S02 Ghet Tm B25 LB Detbwe 11. Paste a screenshot of the results into Last_Mod6MAP. 12. Submit your Last Mod6MAP documents and your Last_ModéScriptMAP:ql script with all SQL statements used. Assessment Submission Submit the following items: + Last Mod6MAP.docx - with title and specified screenshots + Last_ModéScriptMAPsql - All SQL statements created and executed Your instructor should be able to run your script and reproduce your database and reports without error. Thanks for the hard work put into this challenging project! Module 6 Mastery Assessment Rubric Criteria Ratings Pts List ModsMAP. Sms bs. Appropriate tie with name, clas, assignment, date Full Marks. No Marks Ste Last ModsMaP. 160 om Screenshot: EER Diagram with correct tabs and thei eltionships Full Marks No Marks tap Last ModsMAP Sos oo Screenshot: DESCRIBE results for each table: Full Marks. No Marks Sols Last ModsMAP. on So Screenshot: SELECT results for each table Full Marks No Marks Sp Last ModsMaP. Spts opts Screenshot: All employees as ast, frst Full Marks No Marks Spt Last ModsMAP 1601 opts Screenshot: Report showing all roiCodes employees departments a specified Full Marks. No Marks wots Last Mods ScriptMAPsal 3pts opts Drops the ABC Company DE If I exists, creates the database. and makes It the active database Full Marks No Marks Tare Last Mods ScriptMARsal em oo Creates the appropriate tables Full Marks. No Marks Bohs Last Mods scriptMARsal pts opts Creates the appropriate PK and FK relat Full Marks No Marks 2p Last ModsScriptMAPsal Spts Opts Creates the sample data provided Full Marks No Marks Li Last ModsscriptMAPsal oo on Statements that DESCRIBE and SELECT each table Full Marks. No Marks sts Last Mods ScriptMARsal 160 opts Statement listing all projects, employees, and departments as specified Full Marks No Marks pte Other opts opts Comments weren't used to describe statements. (points wil be deducted) Full Marks. No Marks ope Other opts opts Student name is not on al documents so that infomation s not readily availble once printed of, (-5) Full Marks No Marks Ors Total Points: 100