Org. Data you will be writing SQL and editing a spreadsheet You will be submitting these two
Fantastic news! We've Found the answer you've been seeking!
Question:
Org. Data
you will be writing SQL and editing a spreadsheet
- You will be submitting these two files:
- IA08_DB_model.xlsx (Where you design your tables)
- IA08_SQL.txt (The SQL that CREATEs all your tables, and INSERTs the data)
I will be adding the following tables:
- Shopper
- Order
- OrderLine
I wont be giving the names and types of the columns. I
need to determine that.The names must be consistent with the tables and columns I start you
off with in the Product and Manufacturer tabs. That is,
capitalization and the naming convention. e.g., I don't use underscores in any column names.
This is the question i have to solve:
- A shopper named John David Smith creates an order for 1 game of Chess,
and 2 games of Sorry.- That implies that the Shopper table will need to store a
FirstName, MiddleName, and LastName- And, of course, an ID column to uniquely identify the shopper
- It also tells us that an Order table must store the
Shopper.ID field as a foreign key- That's how we know who placed the order
- The OrderLine table must have two FKS.
- It needs to hold Order.ID so we know what order it is part of
- It needs to hold Product.ID so we know what product was purchased
- And since there are two products purchased, there will be two
OrderLine records
- That implies that the Shopper table will need to store a
- When John placed the order, the items were on sale (10% off) at the time of
purchase.- That implies that the OrderLine table will need to have a Price
column, because it may not always match Product.Price
- That implies that the OrderLine table will need to have a Price
- Other info about John, which help us decide what columns are needed for the
Shopper table- His phone number is 215-867-5308
- His email address is jdsmith@psu.edu
- His address is:
- 1313 Mockingbird Lane
- Apt 5a
- Yonkers, NY 10470
- Seeing that tells us that our Shopper table needs to have these
columns:- Address1
- Address2
- City
- State
- Zip
- A shopper named Jane Doe creates an order for 2 game of Life, and 3
games of Checkers.- That implies that a shopper does not require a middle name. So it can be
nullable. - However, a shopper does require a first an last name.
- You can decide the phone, email, and address for this shopper on your own
- That implies that a shopper does not require a middle name. So it can be
- You will also place rows of data, just like the existing Product and
Manufacturer tables do
The SQL
- Once you've completed the spreadsheet, write the CREATE and INSERT statements
in APEX (Links to an external site.) to match what you have in the spreadsheet.- e.g., Capitalizing SQL keywords
- Create them in a an APEX script named IA08.
- When completed, copy/pasted that script into a file named IA08.txt
Related Book For
Introduction to Managerial Accounting
ISBN: 978-1259105708
5th Canadian edition
Authors: Peter C. Brewer, Ray H. Garrison, Eric Noreen, Suresh Kalagnanam, Ganesh Vaidyanathan
Posted Date: