Project 1 Step #3: Basis Specification Expression (DDL) SQL Script
REMEMBER IS DONE ON AN ORACLE DATABASE
Create a SQL script containing your basis specification expression (DDL) statements to educe your considerations, conceptions, triggers, and other required basisbase objects for design 1. Your script must too understand queries to evidence that all objects are educed prosperously (i.e. selecting from the basisbase catalog/basis wordbook using user_objects and user_tables). Additionally, you must yield a unconnected instrument that contains the perfect textual output from popular your DDL script prosperously. Your charges earn understand the evaluation of this output recital as well-mannered-behaved-behaved as a speed, untruth-free expected run of your script in the environment. Within your DDL SQL script, the subjoined reserve fitnesss must be met:
Data Specification Expression (DDL) SQL Script Reserve Requirements:
Drop Statements for All Objects as shortnessed (5 points)
At the prelude of your script, fix that all objects that are required to be dropped are right dropped.
Create/Alter Statements for All Tables and Constraints (30 points)
In an adjust that is misspend for many preventives, fix that all considerations and constraints are right educed and/or altered.
Create Indexes for Natural, Irrelevant Key, and Constantly Queried Columns (10 points)
Unlike elementary keys which possess uncommon indexes educed unreflectiveally, you must educe indexes for incessantly unless key that is not understandd in the elementary/composite key as well-mannered-behaved-behaved as all irrelevant keys and constantly queried columns. Note: you may not yet possess queries built for your basisbase yet but you earn during design disunite 4. Keep this in belief as you earn shortness to educe indexes to patronage these queries.
Create a Reserve of Two Views (10 points)
You are required to educe at meanest two conceptions though it is recommended that you educe the sum of conceptions that is most misspend to patronage your interest fitnesss.
Create a Reserve of Two Sequences (10 points)
You are required to educe at meanest two sequences though if you are using surrogate keys this sum earn at meanest be resembling to the sum of entities that use said keys.
Create a Reserve of Two Triggers (10 points)
You are required to educe at meanest two triggers though the sum of triggers should surpass this reserve if past than two sequences are deployed and to adjudicate the unreflective population of the auditing columns (see direct fitness).
Describe the Interest Design of your Views and Triggers (5 points)
Using comments in your SQL script, anteriorly each conception and trigger supply a designation of what interest design or discharge they supply. Recommend that you too originate incorporating this basis tail into your SOW and Requirements Specification instrument as indispensable in making-ready for yieldting your conjoined lab recital during design disunite 4.
Database Catalog/Data Wordbook Queries (5 points)
Demonstrate the prosperous myth of all aforementioned objects by interrogationing the basisbase catalog/basis wordbook; see the Design Learning Demonstration for examples.
Output Recital (5 points)
All output from the preventive of the statements in fitnesss 1 through 8 must be recorded and reserved into an output recital.
Executable, Error-Free Script (10 points)
The script you yield must amply complete and be untruth-free.
Project 1 Step #3 Deliverables
DDL Script in SQL or TXT: LastName_FirstName_DDL.[sql|txt]
Script Output Report: LastName_FirstName_output.[doc|docx]
Note: the basisbase catalog/basis wordbook queries you transcribe are considered DML statements not DDL. Despite this truth, for clarity the undiminished deliverable is classified as your DDL script. Gladden fix that you understand these queries at the groundproduct of your DDL script as required overhead.
The lab design is cumulative significance that you earn yield this DDL anew during design disunite 4 after a while all untruths reformed, gladdend large, and formatting updated as required.
Project 1 Step #3 Best Practices:
1. If you use Word to educe your script, fix you adapt off the curly quotes as this earn origin an untruth in Oracle. To do this you usually go to the "Options" area, ascertain "Proofing", then "AutoCorrect" and "AutoFormat". Under "Replace", uncurb "Straight quotes" after a while "smart quotes".
2. You may shortness to put script comments in your DDL perfect to instrument your product. If you do, fix to manifest to Oracle that your voicelessnesss are "comments" and not DDL commands. Oracle earn untruth on non-commented voicelessnesss.
Example of comments
Single-line comments rouse after a while two dashes. Example: --Query amount #6
Multi-line comments are enclosed after a while /* */ Example:
The interest rate of this interrogation is to appearance all customers in the CUSTOMERS consideration */
This is a regard you can use for conception this concept: https://docs.oracle.com/cd/B14117_01/server.101/b10759/sql_elements006.htm
3. Output instrumentation - the quickest way to seize script output is to use the "Save" liberty on the inferior, "output" window exception of SQL Developer. The "Save" icon earn ask you to reserve the perfect. You can email yourself the perfect or reserve to Google stimulate if you use this. If you use screenshots, gladden put them in a Word perfect.
4. Your DROP TABLE statements - put them in derangement adjust of the CREATE TABLE statements. If you possess them adjusted reformly, you won't shortness to use CASCADE CONSTRAINTS libertys.
5. One advance voicelessness is to use: set reverberation on; at the top of your DDL perfect so your commands show anteriorly the output does. This earn succor you dispose untruths should they pop up run your DDL script twice. The 2nd run is to curb for DROPs in the reform adjust.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more