BYBG/ISGB 7973, Database Management
Assignment 3
Review the description of the PVFC application.
Create and populate the tables for PVFC in a schema on Oracle. use an existing schema or create a new schema.
Extract files from PVFC.zip to a convenient folder.
⦁ Use the script CREATEBIGPV.sql to create tables and constraints .
⦁ Use the script LOAD1BIGPV.sq to enter data into tables except for the three table mentioned below.
⦁ Use Data-> Import in SQL Developer to load data into table RAWMATERIAL_T from file RawMaterial.csv
⦁ Use Data-> Import in SQL Developer to load data into table SUPPLIES_T from the file Supplies.csv
⦁ Use Data-> Import in SQL Developer to load data into table USES_T from the file Uses.csv
Note that because of referential integrity constraints data in the table SUPPLIES_T can be entered only after a successful execution of the script LOAD1BIGPV. Similarly, tables RAWMATERIAL_T and SUPPLIES_T must be populated before we can enter data in table USES_T.
Examine properties of individual tables. Make yourself familiar with column names.
Practice building SQL queries for exercises 5-57 through 5-93 on pages 246-248. Some the queries will be very simple , some will require determining aggregates, some will require you to find additional date functions in Oracle. More you practice, better you will become at developing SQL statements.
Work to submit
⦁ Add rows to tables.
Provide values for all fields, making sure that values correspond to data types for the individual columns. You can view column names their types under the Columns tab in the worksheet for a table. To insert a row, use the Data tab. Click on the third button to create a blank row at the bottom of the table. Click on the fifth button to make changes permanent. From the message log copy the corresponding SQL statement that was executes in response to insert and commit steps.
⦁ Insert a row in table Customer_T, with Customer ID =27 and Customer Name as your own name and address as 140 West 62nd Street, New York, NY 10023.
⦁ Associate the new customer with Northeast territory by entering a row in table Does BusinessIn_T.
⦁ Insert a row in CustomerShip Address_T to provide a shipping address for the new customer as 9999 Broadway, New York, NY 10025. Use Ship AddressID as 35.
⦁ Enter an order for the new customer in table Order_T. Use OrderID=93, Sales PersonId = 3, the current date as the order date and null as fulfillment date.
⦁ For the OrderID=93, enter a row in table Order Line_T for an order of two units of ‘Oak Computer Desk’ (ProductID=3) and an order of one unit of ’48 Bookcase’ with finish ‘Oak’ (ProductID=8).
⦁ Modify tables
⦁ Modify table Product_T as specified in exercise 5-57 and test your change by entering data as specified in exercise 5-58.
⦁ Write SQL query to retrieve all columns for all products from modified table Product_T. Include the SQL query and the first five rows in the write-up.
⦁ Submit queries and resulting tables for the following for the problems with modifications as noted:
⦁ Find all orders for customers in Northeast territory that have not yet been filled
⦁ Answer the following queries from the textbook:
5-63, 5-67, 5-68, 5-77
Submit on Blackboard a MS Word or PDF document that give the following
⦁ SQL statements for the insertions in 1.a-e
⦁ SQL statement to modify table in 2.a and SQL query and result table form 2.b
⦁ SQL queries and result table for problems 3.a-b
