physical design implementation

Details:

 

1. Create Oracle database tables using SQL Data Definition Language (DDL) for each table listed in Project 1 (file attached).

Make sure that entity and referential integrity are enforced by declaring a primary key for each table (these may be composite keys) and declaring all appropriate foreign keys. CREATE TABLE statements must show integrity constraints, as appropriate, for NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, REFERENCES, and CHECK constraints. Be sure to save script used to create these tables as ‘JVCproject2step1.sql’. Test script to make sure it runs without error.

 

2. Provide two examples of SQL DML (i.e., “INSERT”) commands that fail different table integrity constraints set up in one of the table. Explain why the statements fail.

Be sure to save script used to as ‘JVCproject2step2.sql’. You can include comments in the SQL script describing why the insert statements failed.

 

3. Populate each tables with at least five valid rows of data each and show the SQL used. Populate other tables in your database, as necessary, to satisfy referential integrity. Be sure to save script used to create these records as ‘JVCproject2step3.sql’. Test script to make sure it runs without error.

 

4. Write SQL to perform the following queries and updates. Be sure to save script used to create these records as ‘JVCproject2step4.sql’. Test script to make sure it runs without error:

 

a. Retrieve all customers’ names, account numbers, and addresses (street and zip code only), sorted by account number.

 

b. Retrieve all videos rented in the last 30 days and sort in chronological rental date order.

 

c. Produce a list of distributors and all their information sorted in order by company name.

 

d. Update customer names to change their maiden names to married names. You can choose which record(s) to update.

 

e. Delete customers from the database. You can choose which record(s) to delete. Make sure to use the primary key column in the WHERE clause to affect only a specific row.

 

Submissions:

1. You should submit four SQL scripts satisfying each of the steps above and meeting the requirements noted in the Project 2 Rubric (attached file). Feel free to compress the files using WinZip and save as JVCProject2.zip.

 

a.     Show the actual SQL statements executed and the results the SQL produced below the code.