INSY 3304
Project 2
This project must be completed using an Oracle database with all statements manually typed into an SQL file and run as a batch in Oracle, and the output must be written to a text (.txt) file. Statements and results (feedback from Oracle) must be included in the output file for submission. Directions for downloading the software and for creating and running the SQL file will be posted in Canvas.
Project 2 Relational Schema
I. A) Based on the 3NF relational schema from Project 1 (shown above), analyze the tables, their relationships, and the sample data you were given in Project 1. Create the tables shown, using the best data types based on your analysis. Follow each table name with an underscore and your first, middle, and last initials (if you do not have a middle initial, use X). For example, if your name is Alice B Chandler, you would name the Customer table Customer_abc. Also, keep in mind that foreign key relationships require the same data types on both sides (e.g., if you declare CustID as NUMBER(5) in the Customer table, it must be declared as INTEGER in the Rental table, too). All CREATE table statements and their resulting output (e.g., “Table created”) must be included in your printed output.
B) Execute a DESCRIBE statement for each of the tables. All DESCRIBE statements and their resulting output (i.e., the table structure) must be included in your printed output).
II. A) Insert the sample data from Project 1 into each table. Execute a COMMIT statement to permanently save your changes. All INSERT statements and COMMIT statements, along with their resulting output (e.g., “Row inserted”) must be included in your printed output.
B) Execute a SELECT statement on each table to list all contents (all columns and all rows). All SELECT statements and their resulting output (i.e., the table and its data) must be included in your printed output.
III. Execute the transactions below to modify/add to the data entered in the previous step. Execute a COMMIT statement to permanently save your changes. All INSERT, UPDATE, and COMMIT statements, along with their resulting output (e.g., “Row inserted”) must be included in your printed output.
Customer table
Change the phone number of Customer B200 to ‘2145551234’
Add Customer G119 (Amanda Green, no phone number), SalesRep 14
Order table
Change the order date for Order 108 to 1/28/22
Add an order:
OrderID OrderDate CustID
109 1/28/22 G119
OrderDetail table
Change the price of Product 235 in Order 108 to $62
Add the following products to Order 108:
ProductID ProdQty ProdPrice
407 1 $5.25
618 2 $2.15
Add the following products to Order 109:
ProductID ProdQty ProdPrice
121 1 $8.25
480 1 $3.75
IV. Execute a SELECT statement on each table to list all contents (all columns and all rows), sorted in ascending order by its primary key (in the OrderDetail table, sort by OrderID first, then by ProductID). All SELECT statements and their resulting output (i.e., the table and its data) must be included in your printed output.
Submission:
Add comment lines at the top of your SQL file (after the spool and echo commands) that contain your name, section number, and project number. Submit via the link provided in Canvas by 11:59 pm on the due date. Late submissions will incur a 20-point penalty per day they are late.
