TASK 1: MAKE UPDATES AND CORRECTIONS Based on Part 1 Comments-20 Points: 1. Update the data model: Make any required changes to your design based on the comments provided for Part 1 of the project. Replace Oracle reserved words you used for your identifiers (i.e.: Class, Check, Order, Group, Online Date, UID, etc.) with non-reserved words Remove or replace ALL special characters (including spaces) in your table and attribute names. This includes ALL special characters other than the underscore. The underscore is the only special character accepted by Oracle d. a. b. c. Update table prefixes if needed. All attributes that are part of a table should share the same prefix other than foreign keys to other tables. 2. If you did not set the SQL Data Modeler preferences specified in the Data Model Tutorial #1, go back to this tutorial and follow the instructions for setting the required preference values. SETTING THE CORRECT PREFERENCES AND OPTIONS IS VERY IMPORTANT AND WILL AVOID SOME ERRORS WHEN GENERATING THE SOL TABLE CREATION SCRIPT FILE IN TASK 4 BELOW TASK 2: INTERNAL SCHEMA 30 Points In this task, you will transform the Conceptual Schema (Logical Model) the project to an internal schema in which you define each table, making sure to specify the primary keys and whether or not a given column has a NOT NULL constraint. 1. Remove all many-to-many relationships and implement relationship (associative/composite) entities for ALL many-to-many relationships that may exist in your logical schema 2. Determine primary keys for each entity. You can select an existing attribute or combination of existing attributes. Alternatively, you may want to create a new attribute that will function as the primary key for each entity a. Note: The address fields (combined) are unique for each restaurant/facility but are not a good choice as the primary key. I suggest adding a restaurant ID or number to be the primary key. 3. Notice that the Data Modeler software SHOULD HANDLE all of the transforming of relationships to foreign key attributes for you. If you modeled attributes that have been replaced by foreign keys, be sure to delete the modeled version of the attribute. If you don't see a foreign key in a related entity, double-dlick the relationship line to open the Relationship Properties window, then dose the relationship properties window. This should update the foreign key. Some foreign key additions are not automatically updated by the software. If you have questions about foreign keys, be sure to ask. TASK 3: NORMALIZATION-20 Points: In this task, you will verify your design using the Normalization process. Ensure all your tables are normalized to 3NF. For each of your tables, identify the primary key, all the functional dependencies in the table, and the level of normalization of the table. All tables MUST be in 3NF when submitted with Part 2. The only exception are tables containing the Zip Code. Those tables may remain in 2NF as long as all other attributes are fully determined by the primary key. For any other table that is not in 2NF, move columns to their appropriate tables until all tables are in 3NF. continued next page... You will need to try to visualize the data values you will be storing in your database to complete this task. To verify your final tables are in 3NF (or 2NF with a Zip Code), create a simple spreadsheet or table in the following format and fill out the values shown for each table. Submit this table with your other assignment files TABLE NAME PRIMARY KEY COLUMN(S) NORMALIZATION LEVEL FUNCTIONAL DEPENDENCIES TASK 4: ASSIGNING DATA TYPES (Attribute Domains and Data Types: The Data Dictionary) (40 points) A data dictionary will be defined from within the Data Modeler software. This data dictionary will specify the domain and definition for each table column. You do not need to define domains from within the Data Modeler, but all attributes must be assigned a valid Orade datatype (see table below for recommended types). 1. EVERY attribute MUST be assigned to a data type. 2. If an attribute does not have a user defined domain, ensure the attribute has been assigned the 3. When assigning data types, be sure to specify both a data type and a size (number of allowable characters) for any character string data types. 4. Recommended Data Types for attributes: Oracle Data Type Oracle Data Type Data Value Data Value Variable length strings: VARCHAR2(size) Date or Date-Time Date Do NOT select Text CHAR (size) Floating Point Values and Currency: Fixed length strings: Float or Decimal Integer or Number VARCHAR2(size) Whole Numbers: Times without dates: TASK 5: TABLE CREATION SCRIPT (Database Schema) (20 points) Using the Oradle Data Modeler software, create an Oradle script file (DDL file) containing all the SQL statements necessary to areate the tables defined in your ER model. In grading your assignment, I will load and execute this saript to create your tables on the Orade server. IMPORTANT: See the Data Modeler Tutorial #2 posted to Canvas for this course for detailed instructions for creating the script file using the Data Modeler software. ERRORS: IF ANY ERRORS ARE REPORTED WHEN THE FILE IS GENERATED, YOU MUST CORRECT THE ERRORS. ANY ATTRIBUTES WITH UNKNOWN' LISTED AS THE DATA TYPE MUST BE CORRECTED, ENSURE ALL ATTRIBUTES ASSIGNED VARCHAR2' AND 'CHAR' ALSO INCLUDE THE MAXIMUM NUMBER OF ALLOWABLE CHARACTERS IN PARENTHESIS NEXT TO THE DATA TYPE When viewing the table creation statements, if you have errors, use CTRL-F to find the word 'Error. This will take you to the line that has an error. Ensure your table creation file contains 'Drop Table' statements for each of your tables. These will appear at the top of the script file if you followed the instructions in the tutorial to create the file Save the table creation script file (the DDL) file to your computer 3. 1. 2. Save your model to your computer. TASK 6: CREATE AT LEAST 5 VALIDATION RULES/CHECK CONSTRAINTS FOR COLUMNS IN YOUR TABLES 10 Points 1. Edit the saved .DDL file created in Task 5 above using a text editor. Avoid using a word processor to edit this file. 2. Add validation constraints to at least 5 of your table attributes/columns. You may select any column that can be constrained further than just by data type. See samples on the next page of this document. a. Add at least one validation rule that limits the data to specific values similar to the Aircraft Model in sample 2 shown below. b. Pay careful attention to the required syntax - commas, parentheses, single quotes, etc. 3. Save the updated .DDL file for submission with your model files. Save the file as a text file do not save it as a Word Document. Examples of how to manually add validation check constraints: Sample 1: Original with no validation check constraint CREATE TABLE Pilots INTEGER NOT NULL VARCHAR(20), DECIMAL(8,2) Pilot ID Pilot Name Pilot Salary Sample 1 UPDATED: Updated with 1 validation check constraint CREATE TABLE Pilots Pilot ID Pilot Name Pilot Salary INTEGER NOT NULL VARCHAR(20), DECIMAL(8,2) CHECK (Pilot Salary > 0) Sample 2: Original with no validation check constraint CREATE TABLE Aircraft Aircraft Model Airaraft Manufacturer VARCHAR(20), Aircraft CruisingRange INTEGER VARCHAR(20), Sample 2 UPDATED: Updated with 2 validation check constraints CREATE TABLE Aircraft Aircraft Model VARCHAR(20) CHECK (AircraftModel IN (747, 727', 'DC-10', 'L-1101', 'F-2')) Aircraft Manufacturer VARCHAR(20), Aircraft_Cruising Range INTEGER CHECK (Aircraft_Cruising Range> 0)

using SQL developer data modeler can someone help me with this