ISQA 3310 Oracle SQL Developer Data Modeling Assignment

[ad_1]

ISQA 3310 Oracle SQL Developer Data Modeling Assignment

Overview

In part 3 you will implement the database designed for parts 1 and 2 of the database project in Oracle. Implement just the database portion of the project not the user interface or application code. In this assignment you will create and populate database tables and formulate the queries to extract data from these tables to support the data requirements of the system.

Task 1 – Table creation (20 points) – DDL file from the SQL Data Modeler 

  1. IMPORTANT: Prior to executing the Oracle script to create the tables, make any required changes to your design

based on the comments provided from Parts 1 and 2 of the project.

  1. Verify that you have not used any special characters or Oracle reserved words for your identifiers (i.e.: Check,

Order, Group, Online, Date, Comment, UID, etc.). b. Verify that you removed or replaced ALL special characters (including spaces) in your table and attribute

names. This includes ALL special characters other than the underscore. c. Your DDL file must include the Drop Table statements and at least 5 validation rule/constraints as 

required from Part 2 of the project. 

  1. Many changes required from Part 2 can be made directly to the DDL file, but if changes were made to your 

data model based on comments provided from Part 2 of the project, follow the specific instructions provided in the SQL Data Modeler Tutorial #1 to specify the required Data Modeler Preferences and in the SQL Data Modeler Tutorial #2 to create the DDL file from within the SQL Data Modeler software. SETTING THE CORRECT PREFERENCES AND OPTIONS WHEN CREATING THE DDL FILE IS VERY IMPORTANT.

  1. Open the Oracle SQL Developer software and connect to the UNO Oracle server for this course. See the SQL

Lab/Tutorial #1 for instructions. Load and execute your updated Part 2 DDL file to create tables. Note that the first time you execute this file, you will see an error message for every ‘Drop Table’ statement in the file as the tables will not yet exist. When you re-run this script file in the future, you will not see these errors. The CREATE TABLE statements should execute without any errors, however. Note: DROP TABLE STATEMENTS ARE REQUIRED AS PART OF YOUR DDL FILE

Task 2 – Table population (50 points) Hints: • When creating your ‘insert’ SQL statements, be sure to

insert data into all parent tables before inserting into child tables. (Child tables contain foreign keys to parent tables.) Foreign key constraints will fail if corresponding records do not exist in the parent table.

  • When inserting data into child tables (those with foreign keys), ensure any validation and foreign key constraints will be met. Foreign key values must match a primary key in the related table.  ISQA 3310 Oracle SQL Developer Data Modeling Assignment
  • Use the sample data-insertion script files provided as examples on the course Canvas page under the Module ‘SQL – Lectures, Labs, Tutorials, Exercises’ topic when writing your own data-insertion script file. Samples were also provided with the lab/tutorial file: Employee/Projects/Assignments tables, the Flights/Certifications tables, and the Pets tables from the labs. 1. Write and save to a text file at least four (4) data insertion statements for each table in your design. Save all

your data insertion statements to the same text file. You will need to ensure that your foreign key values match the values of the primary keys in the linked tables. Load and execute this script file on the Oracle server using the

SQL Developer software. Some tables will require more than 4 rows of data to support 4 rows in other tables. 

  1. Insert data into the ‘parent’ tables before inserting data into the ‘child’ tables (those with foreign keys).

Referential integrity will cause insert statements to fail if foreign key values do not match the primary key values in the related table.

  1. Some tables may need more rows to properly illustrate the database’s functionality and as required for the

specific data requirements for Task 2 – Number 2 below. 

  1. When inserting date values for your database tables, use dates between ‘1-Jan-2020’ and ‘5-May-2020’. 

Use the default date format when inserting dates: ‘dd-mon-yyyy’. 

Your insert statements for all tables MUST be saved to a single script file (text file). This script file MUST load and execute without errors on the UNO Oracle server and should be a separate file from your DDL file. See the sample database (employees and flights) files the Module ‘SQL – Lectures, Labs, Tutorials, Exercises’ for examples of script files containing valid SQL Insert statements. Do NOT create a separate text file for each table. ALL insert statements should be included in the same file.

  1. Add additional insert statements to your script file created for ‘Task 2 – Step 1’ above to support the specific data

requirements shown below. These insert statements are in addition to your minimum 4 rows per table for Task 2 step 1 above.

  1. Add the following restaurant/facility to your database:
  2. Name: ‘The American Grill’ as a full-service restaurant. ii. Contact Person: ‘Maria Davis’ iii. The risk is High and the overall rating is ‘Excellent’ iv. You may create values of your own choosing for the remaining columns for this food establishment.
  3. Add an inspection office called ‘Best Inspections’ to your database. You may create values of your

choosing for this office for the other columns.

  1. Add an Inspector ‘Mark Anderson’. Set his Certification Date to Jan. 12, 2020 (‘1-Jan-2020’). You may

create values of your own choosing for the remaining columns for this Inspector. Assign Mark Anderson to the ‘Best Inspections’ office.

  1. Add an additional inspector with a name of your choosing with a Certification Date of Jan. 21, 2020

(‘21Jan-2020’) and assign this inspector to the ‘Best Inspections’ office.

  1. Add an inspection record for the ‘The American Grill’ establishment conducted by ‘Mark Anderson’ on

Feb. 10, 2020 (‘10-Feb-2020’. Give the restaurant ratings of your choice with a final overall rating of ‘Fair’.

  1. Add a second inspection record for the ‘The American Grill’ establishment conducted by ‘Mark

Anderson’ on Mar. 21, 2020 (‘21-Mar-2020’). Give the restaurant ratings of your choice with a final overall rating of ‘Excellent’.

  1. Add an inspection for one of your other restaurants conducted by Mark Anderson on Mar. 21, 2020.

ISQA 3310, Managing the Database Environment Database Project Part 3 – Creating, Populating and Querying Tables 

  1. Create 2 complaints against ‘The American Grill’ restaurant. The complaints may be entered by a single person or by different people but choose different dates for each complaint. At least one complaint must be for a foodborne illness.
  2. Insert a record for at least one food establishment with a ‘Failed’ health inspection overall rating.

Task 3 – 10 Queries (50 points) Write SQL statements to meet the following requirements for your Oracle database. These SQL statements should be stored IN ORDER, 1-10, in a separate SQL (text) script file to be submitted for this assignment. Use notepad or a simple text editor to save all your queries to a text file.  ISQA 3310 Oracle SQL Developer Data Modeling Assignment

Add a comment before each query identifying it by number: For example, on the line before query 1, add the text: — Query 1. Reminder: Comments start with a double dash (–) in Oracle script files. Save this file as a ‘plain text’ file and ensure you have comments that the top with your name. The filename should end with either .txt or .sql extension.

  1. Write a single SQL SELECT statement to select the address, the risk rating, the overall sanitation rating, the name and

phone number of the point-of-contact person for one of your restaurants, and the date of the overall rating of the facility/restaurant.

  1. Write a single SQL SELECT statement to select the first and last names of each inspector in your database working

for ‘Best Inspections’. Include the hire date, certification date, and e-mail address of each inspector. Order the records by the certification date, with the most recent certification date first.

  1. Write a single SQL SELECT statement to select the most recent inspection for ‘The American Grill’ restaurant. You

should select all fields of the Inspection required for Sub-view/External Schema 3 (from Part 1), and you should only select data for the most recent inspection. You should include the name of the inspector and the name of the restaurant in your results.

  1. Write a single SQL SELECT statement to select all complaints filed against ‘The American Grill’ restaurant. Select

the name, address, and phone number of the complainant, the date of the complaint, the type of complaint, and the comments left by the complainant. Order the results by date with the newest complaint listed last and the oldest listed first.

  1. Write a single SQL SELECT statement to select all the restaurants with ‘Failed’ health inspection status. You should

include the name of the restaurant and all the contact information for the ‘point-of-contact’ for the establishment. This query should return at least one record per the requirement from Task-2, 2i above.

  1. Write a single SQL SELECT statement to select the names and addresses of all restaurants with an inspection date of

Mar. 21, 2020.

  1. Write a single SQL SELECT statement to select all the information shown in the sample report provided in

Subview/External Schema 3: Inspections (from Part 1). Be sure to include the inspector number and the name of the restaurant in your results. Include only the fields shown in the report and order the results by the name of the restaurant as shown in the report segment.

  1. The ‘The American Grill’ restaurant was just inspected on April 21, 2020 and received the highest ratings in all categories with a new overall rating of ‘Superior’. Write an SQL INSERT statement and an UPDATE statement to. ISQA 3310 Oracle SQL Developer Data Modeling Assignment
    Need Help with a similar Assignment?

The post ISQA 3310 Oracle SQL Developer Data Modeling Assignment appeared first on EssayPanthers.

[ad_2]

Source link

 
"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"

Hi there! Click one of our representatives below and we will get back to you as soon as possible.

Chat with us on WhatsApp