Expectations: 1. Normalize the data, provide rationale for the tables you create, create the relational tables that you identified as part of the normalization process using appropriate SQL/DDL, insert the data into the tables using SQL/DDL, create queries as indicated using SQL/DML Aim: To review Relational concepts including normalization and SQL coding. Customer Alice Address 13-14 5th Ave Ordered Plums Amount Priceperunit Onhand 40 1 100 Vendor Very Fresh Alice 1314 5th Ave Oranges 30 3 300 Garden Heaven Alice 13-14 5th Ave Pears 10 4 400 Alice 1314 5th Avenue Oranges 40 3 300 Produce One Garden Heav. Bill 55 Mapel Drive Apples 50 2 200 Very Fresh Bill 55 Maple Drive Bananas 100 5 500 Garden Heaven Bill 55 Maple Dr. Bananas 100 5 500 Garden Hyn, Caitlin 1 Oak Road Apt. 3A Plums 40 1 100 Very Fresh Caitlin 1 Oak Rd. Ap. 3A Plums 40 1 100 V. Fresh Elizabeth 16 Sunshine Lane Kiwi 30 7 700 Laura 66 East 4th Street Grapes 60 6 600 Veggie Heaven Veggie Haven Laura 66 East 4th Street Apples 10 2 200 Very Fresh • SECTION One - Normalize the table above ° ° using the methods taught in class and also the readings/videos assigned for this week split the table into separate tables. Identify the primary, secondary (foreign) and/or composite keys ° Using the example given below as your guide, document each table you created after you have normalized the data in this manner. ° EXAMPLE: Tablename1 (fieldname1, fieldname2, fieldname3, fieldname4) - fieldname1 is underlined to show it is a primary key Please note: make sure each table does not contain any unnecessary repetition and errors. Make sure each table has a unique identifier (Primary key, Composite key). Underline the primary or composite key. SECTION 2 - Describe what you created о For each table you created in section 1, a) Discuss your rationale for creating the table and, b) list all the tables that are related to the table you are describing. Please see example Example: The rationale for creating Tablenamel was that..... Tablename1 is related to Tablename2 through the key fieldname1. Tablename 1 is also related to Tablename3 through the fieldname1 etc. SECTION 3 - Use Oracle (a relational database system) to code the following queries о ° о Use SQL code to create the tables you identified sections 1 and 2 of this assignment. For each table created, copy/paste a screenshot showing the SQL used to create the table and the message indicating the table was created. Use SQL code to insert the data shown in the table into the appropriate tables. For each table populated, copy/paste a screenshot showing the SQL used to insert the data in the table. After inserting all the data in the various tables, use the SELECT command to show all the data in the table you created. Copy/paste a screenshot showing the SELECT command that you use to show the data in each of the tables. Create three reports using joins which you feel would be necessary for managers/supervisors to have in order to effectively manage the day to day business operations of this business scenario. For each report you create, copy/paste a screenshot showing the SQL used to execute the report AND the data obtained from running the SQL.
Expectations: 1. Normalize the data, provide rationale for the tables you create, create the relational tables that you identified as part of the normalization process using appropriate SQL/DDL, insert the data into the tables using SQL/DDL, create queries as indicated using SQL/DML Aim: To review Relational concepts including normalization and SQL coding. Customer Alice Address 13-14 5th Ave Ordered Plums Amount Priceperunit Onhand 40 1 100 Vendor Very Fresh Alice 1314 5th Ave Oranges 30 3 300 Garden Heaven Alice 13-14 5th Ave Pears 10 4 400 Alice 1314 5th Avenue Oranges 40 3 300 Produce One Garden Heav. Bill 55 Mapel Drive Apples 50 2 200 Very Fresh Bill 55 Maple Drive Bananas 100 5 500 Garden Heaven Bill 55 Maple Dr. Bananas 100 5 500 Garden Hyn, Caitlin 1 Oak Road Apt. 3A Plums 40 1 100 Very Fresh Caitlin 1 Oak Rd. Ap. 3A Plums 40 1 100 V. Fresh Elizabeth 16 Sunshine Lane Kiwi 30 7 700 Laura 66 East 4th Street Grapes 60 6 600 Veggie Heaven Veggie Haven Laura 66 East 4th Street Apples 10 2 200 Very Fresh • SECTION One - Normalize the table above ° ° using the methods taught in class and also the readings/videos assigned for this week split the table into separate tables. Identify the primary, secondary (foreign) and/or composite keys ° Using the example given below as your guide, document each table you created after you have normalized the data in this manner. ° EXAMPLE: Tablename1 (fieldname1, fieldname2, fieldname3, fieldname4) - fieldname1 is underlined to show it is a primary key Please note: make sure each table does not contain any unnecessary repetition and errors. Make sure each table has a unique identifier (Primary key, Composite key). Underline the primary or composite key. SECTION 2 - Describe what you created о For each table you created in section 1, a) Discuss your rationale for creating the table and, b) list all the tables that are related to the table you are describing. Please see example Example: The rationale for creating Tablenamel was that..... Tablename1 is related to Tablename2 through the key fieldname1. Tablename 1 is also related to Tablename3 through the fieldname1 etc. SECTION 3 - Use Oracle (a relational database system) to code the following queries о ° о Use SQL code to create the tables you identified sections 1 and 2 of this assignment. For each table created, copy/paste a screenshot showing the SQL used to create the table and the message indicating the table was created. Use SQL code to insert the data shown in the table into the appropriate tables. For each table populated, copy/paste a screenshot showing the SQL used to insert the data in the table. After inserting all the data in the various tables, use the SELECT command to show all the data in the table you created. Copy/paste a screenshot showing the SELECT command that you use to show the data in each of the tables. Create three reports using joins which you feel would be necessary for managers/supervisors to have in order to effectively manage the day to day business operations of this business scenario. For each report you create, copy/paste a screenshot showing the SQL used to execute the report AND the data obtained from running the SQL.
Chapter13: Views
Section: Chapter Questions
Problem 1MC
Related questions
Question
Just the section 3 only
Transcribed Image Text:Expectations: 1. Normalize the data, provide rationale for the tables you create, create the relational tables that
you identified as part of the normalization process using appropriate SQL/DDL, insert the data into the tables using
SQL/DDL, create queries as indicated using SQL/DML
Aim: To review Relational concepts including normalization and SQL coding.
Customer
Alice
Address
13-14 5th Ave
Ordered
Plums
Amount
Priceperunit
Onhand
40
1
100
Vendor
Very Fresh
Alice
1314 5th Ave
Oranges
30
3
300
Garden Heaven
Alice
13-14 5th Ave
Pears
10
4
400
Alice
1314 5th Avenue
Oranges
40
3
300
Produce One
Garden Heav.
Bill
55 Mapel Drive
Apples
50
2
200
Very Fresh
Bill
55 Maple Drive
Bananas
100
5
500
Garden Heaven
Bill
55 Maple Dr.
Bananas
100
5
500
Garden Hyn,
Caitlin
1 Oak Road Apt. 3A
Plums
40
1
100
Very Fresh
Caitlin
1 Oak Rd. Ap. 3A
Plums
40
1
100
V. Fresh
Elizabeth
16 Sunshine Lane
Kiwi
30
7
700
Laura
66 East 4th Street
Grapes
60
6
600
Veggie Heaven
Veggie Haven
Laura
66 East 4th Street
Apples
10
2
200
Very Fresh
•
SECTION One - Normalize the table above
°
°
using the methods taught in class and also the readings/videos assigned for this week split the table into
separate tables.
Identify the primary, secondary (foreign) and/or composite keys
° Using the example given below as your guide, document each table you created after you have normalized
the data in this manner.
°
EXAMPLE: Tablename1 (fieldname1, fieldname2, fieldname3, fieldname4) - fieldname1 is
underlined to show it is a primary key
Please note: make sure each table does not contain any unnecessary repetition and errors. Make sure each
table has a unique identifier (Primary key, Composite key). Underline the primary or composite key.
SECTION 2 - Describe what you created
о
For each table you created in section 1, a) Discuss your rationale for creating the table and, b) list all the
tables that are related to the table you are describing. Please see example
Example:
The rationale for creating Tablenamel was that.....
Tablename1 is related to Tablename2 through the key fieldname1. Tablename 1 is also related to
Tablename3 through the fieldname1 etc.
SECTION 3 - Use Oracle (a relational database system) to code the following queries
о
°
о
Use SQL code to create the tables you identified sections 1 and 2 of this assignment.
For each table created, copy/paste a screenshot showing the SQL used to create the table and the
message indicating the table was created.
Use SQL code to insert the data shown in the table into the appropriate tables.
For each table populated, copy/paste a screenshot showing the SQL used to insert the data in the
table.
After inserting all the data in the various tables, use the SELECT command to show all the data in the table
you created.
Copy/paste a screenshot showing the SELECT command that you use to show the data in each of
the tables.
Create three reports using joins which you feel would be necessary for managers/supervisors to have in
order to effectively manage the day to day business operations of this business scenario.
For each report you create, copy/paste a screenshot showing the SQL used to execute the report
AND the data obtained from running the SQL.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 2 steps
Recommended textbooks for you
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr