QuestionQuestion

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 (I have attached part 1 and part 2 of this project for reference). 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
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
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
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
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
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
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
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.

Database Catalog/Data Dictionary Queries
Demonstrate the successful creation of all aforementioned objects by querying the database catalog/data dictionary; see the Project Learning Demonstration for examples.

Output Report
All output from the execution of the statements in requirements 1 through 8 must be recorded and saved into an output report.

Executable, Error-Free Script
The script you submit must fully execute and be error-free.

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

set echo on;

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

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

BEGIN
FOR rec IN
    (
      SELECT DISTINCT OBJECT_NAME
          FROM USER_OBJECTS
       WHERE OBJECT_TYPE = 'TABLE'
    )
LOOP
    EXECUTE immediate 'DROP TABLE '||rec.OBJECT_NAME || ' CASCADE CONSTRAINTS';
END LOOP;
END;
/


-- 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 TABLE CUSTOMER
(
EMAIL VARCHAR2(50) NOT NULL
, NAME VARCHAR2(50)
, ADDRESS VARCHAR2(50)
, PHONE VARCHAR2(20)
, DOB DATE
, CONSTRAINT CUSTOMER_PK PRIMARY KEY
(
    EMAIL
)
);

CREATE TABLE BOOKING
(
BOOKING_ID NUMBER NOT NULL
, BOOKING_DATE DATE
, TOTAL_PRICE NUMBER
, STATUS VARCHAR2(20)
, EMAIL VARCHAR2(50)
, CONSTRAINT BOOKING_PK PRIMARY KEY
(
    BOOKING_ID
)   
);



CREATE TABLE TRANSPORTATION
(
TRANSPORTATION_ID NUMBER NOT NULL
, NAME VARCHAR2(50)
, TRIP_TO VARCHAR2(50)
, TRIP_FROM VARCHAR2(50)
, START_FROM VARCHAR2(50)
, END_AT VARCHAR2(50)
, CONSTRAINT TRANSPORTATION_PK PRIMARY KEY
(
    TRANSPORTATION_ID
)
);

CREATE TABLE PACKAGE
(
PACKAGE_ID NUMBER NOT NULL
, START_DATE DATE
, END_DATE DATE
, PRICE NUMBER
, TYPE VARCHAR2(20)
, TRANSPORTATION_ID NUMBER
, CONSTRAINT PACKAGE_PK PRIMARY KEY
(
    PACKAGE_ID
)
);

CREATE TABLE TOUR
(
TOUR_ID NUMBER NOT NULL
, COLUMN1 VARCHAR2(50)
, TRIP_TO VARCHAR2(50)
, TRIP_FROM VARCHAR2(50)
, START_DATE DATE
, END_DATE DATE
, PACKAGE_ID NUMBER
, CONSTRAINT TOUR_PK PRIMARY KEY
(
    TOUR_ID
)
);

CREATE TABLE HOTEL
(
HOTEL_ID NUMBER NOT NULL
, NAME VARCHAR2(50)
, ROM VARCHAR2(20)
, START_DATE DATE
, END_DATE DATE
, PACKAGE_ID NUMBER
, CONSTRAINT HOTEL_PK PRIMARY KEY
(
    HOTEL_ID
)   
);

CREATE TABLE BOOKING_DET
(
BOOKING_DET_ID NUMBER NOT NULL
, BOOKING_ID NUMBER
, PACKAGE_ID NUMBER
, CONSTRAINT BOOKING_DET_PK PRIMARY KEY
(
    BOOKING_DET_ID
)
);




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

SELECT DISTINCT OBJECT_NAME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE';

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

ALTER TABLE BOOKING
ADD CONSTRAINT BOOKING_FK1 FOREIGN KEY
(
EMAIL
)
REFERENCES CUSTOMER
(
EMAIL
);

ALTER TABLE PACKAGE
ADD CONSTRAINT PACKAGE_FK1 FOREIGN KEY
(
TRANSPORTATION_ID
)
REFERENCES TRANSPORTATION
(
TRANSPORTATION_ID
);


ALTER TABLE TOUR
ADD CONSTRAINT TOUR_FK1 FOREIGN KEY
(
PACKAGE_ID
)
REFERENCES PACKAGE
(
PACKAGE_ID
);...
$68.00 for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats