Question 1. Use any technique to write or generate the Oracle DDL commands necessary to create thetables, primary keys and referential integrity constraintsfor the tables and attributes listed in Table 1 and Figure 1.
It is not required to name non-key attributes the exactly same the name shown on the relational model.
I will execute the SQL commands to verify that they execute correctly.
• Show evidence that you successfully created the tables - show screenshots
• Submit a copy of the Oracle DDL as a separate file with the name DDL_part1.SQL..
Question 2. Populate the tables you created in question 1 with the data set provided. Query each table to verify that you populated the tables with the data provided - use any combination of techniques using COUNT and other functions. Finally, export the database.
• Describe the process you followed to populate the database.
• Describe the process you used to verify that you populated the tables successfully.
• Show evidence that you successfully populated your database - show screenshots.
• Show evidence that you successfully verify that you populated the tables with the data provided - show screenshots.
• Submit a copy of the Oracle commands that you used to verify that you populated the tables with the data provided as a separate file with the name TEST_Part2.SQL.
• Submit a copy of the exported entire databaseas a separate file with the name EXPORT_Part2.txt.
Place Question 2 answers and documentation here
Question 3. Test the primary keys and referential integrity constraints. Write the Oracle DML statements to INSERT duplicate primary key values and foreign key values with no matching primary key for each table where primary keys and foreign keys are defined.
Write Oracle DML statements to DROP primary key values when matching foreign key values exist.
A successful test will generate an error message from Oracle informing you of the constraint violation. Capture screenshots or error logs of these messages. I will execute the SQL commands to verify that they reasonably test the constraints of the database. I will execute the SQL commands to verify that they execute correctly.
• For each test, describe the test, the test data and the expected result of the test.
• Show evidence that you successfully tested the constraints of your database - show screenshots.
• Described any changes you would make to your database as result of your testing.
• Submit a copy of the Oracle SQL test commands with INSERT and DROP commands as a separate file with the name Test_Constraints_Part3.SQL.
• Place Question 3 answers and documentation here.
Question 4.
4.a Write the following queries with comments to identify the queries:
• Determine the state that has the most number of occurrences where Compared to National has the value "Number of Cases Too Small".
• For the state with the most number of occurrences where the Compared to National has the value "Number of Cases Too Small", list the Measure ID and Measure Name.
• For the state with the most number of occurrences where the Compared to National has the value "Number of Cases Too Small", obtain the name and type of the hospital sorted by Measure Name. Include the full state name, the hospital type description and the Measure ID Name in the result set.
• Combine one or more of the previous queries to create one or more queries illustrating the construction of a subquery.
• Show evidence that you successfully executed the queries - show screenshots below of the result of the queries.
4.b Add an index to your database that would benefit the query containing the subquery in part 3.a
4.c Evaluate the impact of your index generating and interpreting SQL execution plans on the query without the index as compared to the query with the index.
Do not create an index on a primary key
• Explain why the index is beneficial.
• Show evidence that you successfully created the index - show screenshots.
• Show evidence that you successfullyevaluated the impact of your index - show screenshots.
• Submit one file that contains the SQL commands for the queries and the index from question 4 a b. Name the file Part4.SQL.
Attachment:- Excel Project .rar