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
• 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
• 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
• 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
•      

Table Name Shopper Description Shoppers who purchase items ColumnName Data Type Description PK? NULLable? FK? FK refers to Comment Example