I’ve added two new tables to the LabDataF20 account. One of them is named “Supplies” and it represents which paint and other types of supplies used in fulfilling an order. The second one is named “UsedIn” and it represents how much of a particular supply was used in fulfilling an order. These two tables have been created with no constraints at all – this is part of the assignment!
Suppose further there are two main users of this database (SupplyMGR and OrderMGR) and the queries below represent the main queries for these two main users:
- Find the total amount of all Paint(Supplies.SType) in stock. PaintStock
- Find the total amount of each supply used in September. The answer will have columns SID, SDescription, AmountInStock, and “Total Amount Used in September”. SeptUsage
- Find the Supplies needed to fulfill Order# 9001. The answer will have columns SDescription and “Quantity Needed”. SuppliesFor9001
- Determine which orders have used more than 1(Quantity Used) sheet of Transfer Paper(SType). The answer will have columns Order# and “Sheets of Transfer Paper Used”. BigJobs
Given this information, we can now fully create the Internal, Conceptual, and External Levels for this database.
- Write the above queries in SQL and test them against the data in the lab account. Be sure your queries are correct before starting on the graded part of this assignment. Also, be sure to use the fewest tables necessary to answer each question.
INTERNAL AND CONCEPTUAL LEVEL DESIGN
- (40 pts) Decide on file structures/organizations to optimize the queries above. Create the two new tables in your account; be sure to designate all primary and foreign keys. NOTE: some of these may be references to the other, existing tables in the lab account; this is not a problem. You can create tables in your account with keys linked to tables in another account. Name the tables EXACTLY as they appear in LabDataF20. Be sure to create all file structures you need for these two tables. Assume all queries are run equally frequently and that the tables are large enough to warrant file structures.
Note: you can only actually build file structures on the two tables you create in your account. If you think you need some on any of the other five tables, just state this in your submission file.
- (10 pts) Populate your tables with the data from “LabDataF20”.
EXTERNAL LEVEL DESIGN
- (40 pts) Create a view for each of the four queries listed above. The names of the views are given in bold italics at the end of the query. These views must reference the two tables in your account. Please use these names EXACTLY – I will be grading with a script that looks for those names and those names only.
- (10 pts) Grant user SupplyMGR select privilege on their two views and grant user OrderMGR select privilege on their two views.
Since all of your CREATE statements will be stored in Oracle, you do not need to submit any executable code. What I would like for you to submit is a file called “lab3.txt” which contains a description of what you decided to do. Here’s the format I would like:
<QUERY IN SQL>
File structures for this query:
I have built <File Structure Type> on <Column List> of <Table Name> [Repeat the above line format for every file structure for this query]
<BRIEF explanation as to why you chose this file structure(s)>
<Repeat this format for all 4 queries>
Don’t forget to SUBMIT “lab3.txt” on Western Online.
Also, Oracle stores your CREATE commands exactly as you enter them, so good formatting and the use of good alias names are required for this assignment.