Drop Temporary Table if Exists Customers Program Project Instructions are attached. They should be everything that is needed. If there any questions please

Drop Temporary Table if Exists Customers Program Project Instructions are attached. They should be everything that is needed. If there any questions please do not hesitate to ask. Discussion:
1. Refer to your project and explain different types of SQL (DDL, DML & DCL) with suitable examples.
Post your ERD for ease of understanding by fellow class-members.
2. What is a view? What is it used for? Give examples when a view can be used for your project.
Project:
Create a SQL script containing your data definition language (DDL) statements to create your tables,
views, triggers, and other required database objects for project 1. Your script must also include queries
to demonstrate that all objects are created successfully (i.e. selecting from the database catalog/data
dictionary using user_objects and user_tables). Additionally, you must submit a separate document that
contains the complete textual output from running your DDL script successfully. Your score will include
the evaluation of this output report as well as a live, error-free expected run of your script in the
environment. Within your DDL SQL script, the following minimum requirements must be met:
Data Definition Language (DDL) SQL Script Minimum Requirements:
1. Drop Statements for All Objects as needed (5 points)
At the beginning of your script, ensure that all objects that are required to be dropped are properly
dropped.
2. Create/Alter Statements for All Tables and Constraints (30 points)
In an order that is appropriate for repeated executions, ensure that all tables and constraints are
properly created and/or altered.
3. Create Indexes for Natural, Foreign Key, and Frequently Queried Columns (10 points)
Unlike primary keys which have unique indexes created automatically, you must create indexes for ever
natural key that is not included in the primary/composite key as well as all foreign keys and frequently
queried columns. Note: you may not yet have queries built for your database yet but you will during
project part 4. Keep this in mind as you will need to create indexes to support these queries.
4. Create a Minimum of Two Views (10 points)
You are required to create at least two views though it is recommended that you create the number of
views that is most appropriate to support your business requirements.
5. Create a Minimum of Two Sequences (10 points)
You are required to create at least two sequences though if you are using surrogate keys this number
will at least be equal to the number of entities that use said keys.
6. Create a Minimum of Two Triggers (10 points)
You are required to create at least two triggers though the number of triggers should exceed this
minimum if more than two sequences are deployed and to accommodate the automatic population of
the auditing columns (see next requirement).
7. Describe the Business Purpose of your Views and Triggers (5 points)
Using comments in your SQL script, before each view and trigger provide a description of what business
purpose or function they provide. Recommend that you also begin incorporating this data back into your
SOW and Requirements Definition document as necessary in preparation for submitting your
consolidated lab report during project part 4.
8. Database Catalog/Data Dictionary Queries (5 points)
Demonstrate the successful creation of all aforementioned objects by querying the database
catalog/data dictionary; see the Project Learning Demonstration for examples.
9. Output Report (5 points)
All output from the execution of the statements in requirements 1 through 8 must be recorded and
saved into an output report.
10. Executable, Error-Free Script (10 points)
The script you submit must fully execute and be error-free.
Project 1 Step #3 Deliverables
1. DDL Script in SQL or TXT: LastName_FirstName_DDL.[sql|txt]
2. Script Output Report: LastName_FirstName_output.[doc|docx]
Note: the database catalog/data dictionary queries you write are considered DML statements not DDL.
Despite this fact, for clarity the entire deliverable is classified as your DDL script. Please ensure that you
include these queries at the bottom of your DDL script as required above.
The lab project is cumulative meaning that you will submit this DDL again during project part 4 with all
errors corrected, content expanded, and formatting updated as required.
Project 1 Step #3 Best Practices:
1. If you use Word to create your script, ensure you turn off the curly quotes as this will cause an error in
Oracle. To do this you usually go to the “Options” area, find “Proofing”, then “AutoCorrect” and
“AutoFormat”. Under “Replace”, uncheck “Straight quotes” with “smart quotes”.
2. You may want to put script comments in your DDL file to document your work. If you do, ensure to
indicate to Oracle that your notes are “comments” and not DDL commands. Oracle will error on noncommented notes.
Example of comments
•
Single-line comments start with two dashes. Example: –Query problem #6
•
Multi-line comments are enclosed with /* */ Example:
/*Query 4
The business value of this query is to show all customers in the CUSTOMERS table */
This is a reference you can use for understanding this
concept: https://docs.oracle.com/cd/B14117_01/server.101/b10759/sql_elements006.htm
3. Output documentation – the quickest way to capture script output is to use the “Save” option on the
lower, “output” window section of SQL Developer. The “Save” icon will ask you to save the file. You can
email yourself the file or save to Google drive if you use this. If you use screenshots, please put them in a
Word file.
4. Your DROP TABLE statements – put them in reverse order of the CREATE TABLE statements. If you
have them ordered correctly, you won’t need to use CASCADE CONSTRAINTS options.
5. One further note is to use: set echo on; at the top of your DDL file so your commands display before
the output does. This will help you locate errors should they pop up run your DDL script twice. The 2nd
run is to check for DROPs in the correct order.
———————————Learning Resources
See Project Learning Demonstration posted under Course Content/Hands-on Resources for step-by-step
instructions.
https://www.w3schools.com/sql/sql_create_table.asp
https://docs.oracle.com/database/121/SQLRF/sql_elements.htm#SQLRF002

Purchase answer to see full
attachment