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.

1. 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.

1. 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.

1. 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.

1. 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.

1. 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).

1. 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.

1. 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.

1. 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.

1. 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 non-commented 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