ISQA 3310 Oracle SQL Developer Data Modeling Assignment
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
based on the comments provided from Parts 1 and 2 of the project.
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.
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.
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.
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.
Referential integrity will cause insert statements to fail if foreign key values do not match the primary key values in the related table.
specific data requirements for Task 2 – Number 2 below.
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.
requirements shown below. These insert statements are in addition to your minimum 4 rows per table for Task 2 step 1 above.
choosing for this office for the other columns.
create values of your own choosing for the remaining columns for this Inspector. Assign Mark Anderson to the ‘Best Inspections’ office.
(‘21Jan-2020’) and assign this inspector to the ‘Best Inspections’ office.
Feb. 10, 2020 (‘10-Feb-2020’. Give the restaurant ratings of your choice with a final overall rating of ‘Fair’.
Anderson’ on Mar. 21, 2020 (‘21-Mar-2020’). Give the restaurant ratings of your choice with a final overall rating of ‘Excellent’.
ISQA 3310, Managing the Database Environment Database Project Part 3 – Creating, Populating and Querying Tables
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.
phone number of the point-of-contact person for one of your restaurants, and the date of the overall rating of the facility/restaurant.
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.
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.
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.
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.
Mar. 21, 2020.
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.