4018.1.1:Conceptual Models to Physical Schemas
The graduate creates conceptual data models and translates them into physical schemas.
4018.1.2:Create Databases
The graduate creates databases utilizing SQL Data Definition Language (DDL) in MySQL environment.
4018.1.3:Create/Modify Tables and Views
The graduate creates and modifies tables and views employing SQL Data Definition Language (DDL) in MySQL environment.
4018.1.4:Create Primary Keys/Foreign Keys and Indexes
The graduate creates and modifies primary keys (PKs) and foreign keys (FKs) and indexes with SQL Data Definition Language (DDL) in MySQL environment.
4018.1.5:Populate Tables
The graduate populates tables with insert, update, and delete using DML in the MySQL environment.
4018.1.6:Create Simple and Complex Queries
The graduate creates simple Select-From-Where (SFW) and complex 3+ table join queries with Data Manipulation Language (DML) in MySQL environment.
e. Explain how you assigned attributes to the NF tables and determined the cardinality of the relationships between your 3NF tables Complete the "Final Physical Database Model" section of the attached “Nora's Bagel Bin Database Blueprints” document by doing the following; a. Copy the table names and cardinality information from your 3NF diagram into the “Final Physical Database Model” and rename the attributes. b. Assign one of the following five data types to eachattribute in your 3NF tables: CHAR(), VARCHAR(), TIMESTAMP, INTEGER, or NUMERIC(). Each data type must be used at least once. ate a database using the attached "Jaunty Coffee Co. ERD" by doing the following: Develop SQL code to create each table as specified in the attached “Jaunty Coffee Co. ERD” by doing the following: a. Provide the SQL code you wrote to create allthe tables. b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server's response. Develop SQL code to populate each table in the database design document by doing the following: Note: This data is not provided. You will be fabricating the data for this step. a. Provide the SQL code you wrote to populate the tables with at least three rows of data in eachtable. b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server's response. Develop SQL code to create a view by doing the following: a. Provide the SQL code you wrote to create your view. The view should show all of the information from the “Employee” table but concatenate eachemployee’s first and last name, formatted with a space between the first and last name, into a new attribute called employee full name. b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server's response. Develop SQL code to create an index on the coffee_name field by doing the following: a. Provide the SQL code you wrote to create your index on the coffee_name field from the “Coffee” table. b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server's response. Develop SQL code to create an SFW (SELECT-FROM-WHERE) query for any of your tables or views by doing the following: a. Provide the SQL code you wrote to create your SFW query. b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server's response. Develop SQL code to create a query by doing the following: a. Provide the SQL code you wrote to create your table joins query. The query should join together three different tables and include attributes from allthree tables in ts output. b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server's response. SCENARIO You are a database designer and developer who has been hired by two local businesses, Nora's Bagel Bin and Jaunty Coffee Co, to build databases to help them manage their businesses. First, you will design a normalized physical database model to store data for Nora's Bagel Bin's ordering system. Then, you will use an existing database design document for Jaunty Coffee Co. to create its database. Once the tables have been built, you will load them with sample data and create a view and an index to protect and improve query performance. Finally, you will create both a simple quer and amore complex table joins query to produce meaningful reports from the newly created database. REQUIREMENTS Your submission must be your original work. No more than a combined total of 30% of a submission and no more than a 10% match to any one individual source can be directly quoted or closely paraphrased from sources, even if cited correctly. An originality report is provided when you submit your task that can be used as a guide. You must use the rubric to direct the creation of your submission because it provides detailed criteria that will be used to evaluate your work. Each requirement below may be evaluated by more than one rubric aspect. The rubric aspect titles may contain hyperlinks to relevant portions of the course. Tasks may not be submitted as cloud links, such as links to Google Docs, Google Slides, OneDrive, etc, unless specified in the task requirements. All other submissions must be file types that are uploaded and submitted as attachments (e.g. .doc, pdf ppt). A. Construct a normalized physical database model to represent the ordering process for Nora's Bagel Bin by doing the following: Note: Before proceeding, familiarize yourself with the ordering process for Nora's Bagel Bin by reviewing the following documents in the Supporting Documents section of this task: the shop's unnormalized sales order form (‘Bagel Order Form’) and the first normal form (INF) provided in the ‘Nora's Bagel Bin Database Blueprints.” 1. Complete the second normal form (2NF) section of the attached “Nora's Bagel Bin Database Blueprints” document by doing the followin a. Assign eachattribute from the INF table into the correct 2NF table. b. Describe the relationship between the two pairs of 2NF tables by indicating their cardinality in each of the dotted cells: one-to-one (1:1), one-to-many (1:M), many-to-one (M:1), or many- many (M:M). Note: Cardinality is read left to right and top to bottom, and the preferred method of notation is crow’s foot. . Explain how you assigned attributes to the 2NF tables and determined the cardinality of the relationships between your 2NF tables. 2. Complete the third normal form (3NF) section of the attached “Nora's Bagel Bin Database Blueprints” document by doing the following: a. Assign eachattribute from your 2NF "Bagel Order" table into one of the new 3NF tables. Copy all other information from your 2NF diagram into the 3NF b. Provide each 3NF table with a name that reflects its contents. c. Create anew field that will be used as a key linking the two 3NF tables you named in part A2b. Ensure that your primary key (PK) and foreign key (FK) fields are in the correct locations in t NF diagram. d. Describe the relationships between the INF tables by indicating their cardinality in eachof the dotted cells: one-to-one (1:1), one-to-many (1:M), many-to-one (M:1), or many-to-many (MM). gram. 3/27/23, 8:23 PM WGU Performance Assessment https://tasks.wgu.edu/student/001136025/course/24260006/task/3273/evaluation-report 1/7 VHT2 — VHT2 TASK 1: NORMALIZATION AND DATABASE DESIGN DATA MANAGEMENT - APPLICATIONS — C170 PRFA — VHT2 EVALUATION REPORT — ATTEMPT 1 — APPROVAL NEEDED Overall Evaluator Comments EVALUATOR COMMENTS To support you in demonstrating proficiency, you will be unable to resubmit your task until you have consulted with a course instructor. Your submission includes a detailed walkthrough of the database design for Nora's Bagel Bin and database development for Jaunty Coffee Co, including appropriate screenshots of the functioning SQL code. Please see the comments in the rubric for aspects needing revisions. A1a. 1NF Table Attributes to 2NF Table EVALUATOR COMMENTS: ATTEMPT 1 The Bagel Order and Bagel Order Line Item tables contain all the appropriate attributes. The Bagel table contains an incorrect attribute. A1b. Relationships Between 2NF Tables EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspect A1a is complete. A1c. Explanation of Attributes and Cardinality of 2NF Tables Approaching Competence The submission assigns at least 1 but not all attributes from the 1NF table into the correct 2NF table. Not Evident The submission does not describe the relationship between the 2 pairs of 2NF tables by indicating their cardinality in each of the dotted cells. TASK OVERVIEW SUBMISSIONS EVALUATION REPORT 3/27/23, 8:23 PM WGU Performance Assessment https://tasks.wgu.edu/student/001136025/course/24260006/task/3273/evaluation-report 2/7 EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspects A1a and A1b are complete. A2a. 2NF Table Attributes to 3NF Table EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspects A1a, A1b, and A1c are complete. A2b. Naming of Tables EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspects A1a, A1b, A1c, and A2a are complete. A2c. 3NF Keys Between Tables EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspects A1a, A1b, A1c, A2a, and A2b are complete. A2d. Relationships Between 3NF Tables Not Evident The submission does not explain how the attributes were assigned to the 2NF tables and how the cardinality of the relationships between the 2NF tables was determined. Not Evident The submission does not assign any attributes from the 2NF "Bagel Order" table into one of the new 3NF tables. Not Evident The submission does not provide a name for any 3NF table. Not Evident The submission does not provide a new field that will be used as a key linking the 2 3NF tables. Not Evident The submission does not describe the relationships between the 3NF tables by indicating their cardinality. 3/27/23, 8:23 PM WGU Performance Assessment https://tasks.wgu.edu/student/001136025/course/24260006/task/3273/evaluation-report 3/7 EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspects A1a, A1b, A1c, A2a, A2b, and A2c are complete. A2e. Explanation of Attributes and Cardinality of 3NF Tables EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspects A1a, A1b, A1c, A2a, A2b, A2c, and A2d are complete. A3a. Copying Information and Renaming Attributes EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspects A1a, A1b, A1c, A2a, A2b, A2c, A2d, and A2e are complete. A3b. Five Data Types EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspects A1a, A1b, A1c, A2a, A2b, A2c, A2d, A2e, and A3a are complete. B1a. SQL Code: Tables EVALUATOR COMMENTS: ATTEMPT 1 The appropriate SQL code is written to create tables. Not Evident The submission does not explain how the attributes were assigned to the 3NF tables and how the cardinality of the relationships between the 3NF tables was determined. Not Evident The submission does not copy any table names and cardinality information from the 3NF diagram into the “Final Physical Database Model” or does not rename the attributes. Not Evident The submission does not assign any of the 5 data types to any attribute in the 3NF tables. Competent The submission provides accurate and logical SQL code written for all the tables. 3/27/23, 8:23 PM WGU Performance Assessment https://tasks.wgu.edu/student/001136025/course/24260006/task/3273/evaluation-report 4/7 B1b. Tables: Screenshot Of SQL Code Results EVALUATOR COMMENTS: ATTEMPT 1 Screenshots showing the SQL code and server results are provided. B2a. SQL Code: Data Population EVALUATOR COMMENTS: ATTEMPT 1 SQL code populating tables with data is provided. The SQL tables are not all populated with at least three rows of data. B2b. Data: Screenshot of SQL Code Results EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspect B2a is complete. B3a. SQL Code: View for Employee Information EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspect B2a is complete. B3b. View: Screenshot of SQL Code Results Competent The submission demonstrates that the code was tested by providing a screenshot showing the SQL commands and the database server’s response. Approaching Competence The submission provides inaccurate or illogical SQL code written to populate the tables, or 1 or more tables does not include at least 3 rows of data. Approaching Competence Not applicable. Not Evident The submission does not provide the SQL code written to create the view. 3/27/23, 8:23 PM WGU Performance Assessment https://tasks.wgu.edu/student/001136025/course/24260006/task/3273/evaluation-report 5/7 EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspect B3a is complete. B4a. SQL Code: Index of Coffee Information EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspect B2a is complete. B4b. Index: Screenshot of SQL Code Results EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspect B4a is complete. B5a. SQL Code: SFW Queries EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspect B2a is complete. B5b. SFW Queries: Screenshot of SQL Code Results Not Evident The submission does not demonstrate that the code was tested by providing a screenshot showing the SQL commands and the database server’s response. Not Evident The submission does not provide the SQL code written to create an index. Not Evident The submission does not demonstrate that the code was tested by providing a screenshot showing the SQL commands and the database server’s response. Not Evident The submission does not provide SQL code written to create the SFW query. 3/27/23, 8:23 PM WGU Performance Assessment https://tasks.wgu.edu/student/001136025/course/24260006/task/3273/evaluation-report 6/7 EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspect B5a is complete. B6a. SQL Code: Join Query EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspect B2a is complete. B6b. Join Query: Screenshot of SQL Code Results EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when aspect B6a is complete. C. PDF Submission EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when all other aspects are complete. D. Professional Communication Not Evident The submission does not demonstrate that the code was tested by providing a screenshot showing the SQL commands and the database server’s response. Not Evident The submission does not provide the SQL code written to create the table joins query. Not Evident The submission does not demonstrate that the code was tested by providing a screenshot showing the SQL commands and the database server’s response. Approaching Competence The submission provides a PDF of parts A and B, but each part is not clearly labeled. Approaching Competence Content is poorly organized, is difficult to follow, or contains errors in mechanics, usage, or grammar that cause confusion. Terminology is misused or ineffective. 3/27/23, 8:23 PM WGU Performance Assessment https://tasks.wgu.edu/student/001136025/course/24260006/task/3273/evaluation-report 7/7 EVALUATOR COMMENTS: ATTEMPT 1 This aspect will be evaluated when all other aspects