Your Perfect Assignment is Just a Click Away

We Write Custom Academic Papers

100% Original, Plagiarism Free, Customized to your instructions!

glass
pen
clip
papers
heaphones

Townson University Data Definition Language DDL SQL Script Project

Townson University Data Definition Language DDL SQL Script Project

Description

Project Step #3: Data Definition Language (DDL) SQL Script

Create a SQL script containing your data definition language (DDL) statements to create your tables, views, triggers, and other required database objects for your project. 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:

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. 

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.

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.

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.

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.

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

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.

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 Step #3 Deliverables

DDL Script in SQL or TXT: LastName_FirstName_DDL.[sql|txt]

  1. 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 Step #3 Best Practices:

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

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

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

———————————-

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

Our Service Charter

1. Professional & Expert Writers: Writing Expert only hires the best. Our writers are specially selected and recruited, after which they undergo further training to perfect their skills for specialization purposes. Moreover, our writers are holders of masters and Ph.D. degrees. They have impressive academic records, besides being native English speakers.

2. Top Quality Papers: Our customers are always guaranteed papers that exceed their expectations. All our writers have +5 years of experience. This implies that all papers are written by individuals who are experts in their fields. In addition, the quality team reviews all the papers before sending them to the customers.

3. Plagiarism-Free Papers: All papers provided by Writing Expert are written from scratch. Appropriate referencing and citation of key information are followed. Plagiarism checkers are used by the Quality assurance team and our editors just to double-check that there are no instances of plagiarism.

4. Timely Delivery: Time wasted is equivalent to a failed dedication and commitment. Writing Expert is known for timely delivery of any pending customer orders. Customers are well informed of the progress of their papers to ensure they keep track of what the writer is providing before the final draft is sent for grading.

5. Affordable Prices: Our prices are fairly structured to fit all groups. Any customer willing to place their assignments with us can do so at very affordable prices. In addition, our customers enjoy regular discounts and bonuses.

6. 24/7 Customer Support: At Writing Expert, we have put in place a team of experts who answer all customer inquiries promptly. The best part is the ever-availability of the team. Customers can make inquiries anytime.

Our Service Charter

1. Professional & Expert Writers: Writing Expert only hires the best. Our writers are specially selected and recruited, after which they undergo further training to perfect their skills for specialization purposes. Moreover, our writers are holders of masters and Ph.D. degrees. They have impressive academic records, besides being native English speakers.

2. Top Quality Papers: Our customers are always guaranteed papers that exceed their expectations. All our writers have +5 years of experience. This implies that all papers are written by individuals who are experts in their fields. In addition, the quality team reviews all the papers before sending them to the customers.

3. Plagiarism-Free Papers: All papers provided by Writing Expert are written from scratch. Appropriate referencing and citation of key information are followed. Plagiarism checkers are used by the Quality assurance team and our editors just to double-check that there are no instances of plagiarism.

4. Timely Delivery: Time wasted is equivalent to a failed dedication and commitment. Writing Expert is known for timely delivery of any pending customer orders. Customers are well informed of the progress of their papers to ensure they keep track of what the writer is providing before the final draft is sent for grading.

5. Affordable Prices: Our prices are fairly structured to fit all groups. Any customer willing to place their assignments with us can do so at very affordable prices. In addition, our customers enjoy regular discounts and bonuses.

6. 24/7 Customer Support: At Writing Expert, we have put in place a team of experts who answer all customer inquiries promptly. The best part is the ever-availability of the team. Customers can make inquiries anytime.