Home Homework Library Computer Science Database Development Instructions for Project, part 4 Create and submit your final cons...

Instructions for Project, part 4 Create and submit your final cons...

Question

Instructions for Project, part 4
Create and submit your final consolidated technical report which will include the new requirements outlined below that are from project step 4 as well as all of the previous project steps (1-3). From project step 4, you must create and submit a SQL script containing your data manipulation language (DML) statements to insert your sample data into your database and your twenty queries (12 basic, 8 advanced). Within the consolidated technical report, you must include the complete textual output from running your DDL, DML, and queries successfully. You will also include a literature review of similar systems in your report. Your final score will include the evaluation of the collection of output as well as a live, error-free expected run of your script in the environment.

Within your DML and query scripts, the following minimum requirements must be met:

Data Manipulation Language (DML) SQL Script Minimum Requirements:
1. All Tables Populated with Minimum of 10 Rows (40 points)

Unless a valid and approved exception exists within your requirements definition document, all tables must have at least 10 rows of sample data.

2. All Surrogate Keys Populated Automatically (40 points)
All of your project sequences and triggers must be used to automatically populate your surrogate keys.
3. Separate DML for Different Tables with Comments (10 points)

For readability, each block or grouping of DML statements for each table must be separated with an appropriate comment header with a blank line after the last statement in the group. Note: the last group does not require a blank line afterwards.

4. Executable, Error-Free Script (10 points)

The script you submit must fully execute and be error-free.

20 SQL Queries (12 Basic, 8 Advanced) Minimum Requirements:
1. Query 1: Select all columns and all rows from one table (5 points)
2. Query 2: Select five columns and all rows from one table (5 points)
3. Query 3: Select all columns from all rows from one view (5 points)
4. Query 4: Using a join on 2 tables, select all columns and all rows from the tables without the use of a Cartesian product (5 points)
5. Query 5: Select and order data retrieved from one table (5 points)
6. Query 6: Using a join on 3 tables, select 5 columns from the 3 tables. Use syntax that would limit the output to 10 rows (5 points)
7. Query 7: Select distinct rows using joins on 3 tables (5 points)
8. Query 8: Use GROUP BY and HAVING in a select statement using one or more tables (5 points)
9. Query 9: Use IN clause to select data from one or more tables (5 points)
10. Query 10: Select length of one column from one table (use LENGTH function) (5 points)
11. Query 11: Delete one record from one table. Use select statements to demonstrate the table contents before and after the DELETE statement. Make sure you use ROLLBACK afterwards so that the data will not be physically removed (5 points)
12. Query 12: Update one record from one table. Use select statements to demonstrate the table contents before and after the UPDATE statement. Make sure you use ROLLBACK afterwards so that the data will not be physically removed (5 points)
13. Perform 8 Additional Advanced Queries (40 points)

These queries are open for you to design but must be complex in nature such as including multiple table joins, sub-queries, aggregate functions, etc. These queries will not only be evaluated on their design and execution but also on their depth of complexity.

Challenge yourself and leverage the techniques learned throughout the course to design your advanced queries.

Technical Report
1. Introduction (5 points)
2. Overview (5 points)
3. Literature Review (10 points)
4. Assumptions (5 points)
5. Design Decisions (10 points)
6. Statement of Work (SOW) (5 points)
o Revised, updated, and expanded as needed to include incorporation of all feedback given as part of the project part 1 evaluation.
7. Requirements Definition Document (10 points)
o Revised, updated, and expanded as needed to include the incorporation of all feedback given as part of the project part 2 evaluation.
8. Detailed Database Design (40 points)
1. Entity Relationship Diagram (ERD) (10 points)
 Revised, updated, and expanded as needed to include the incorporation of all feedback given as part of the project part 2 evaluation.
2. DDL Source Code Embedded (10 points)
 Ensure that all source code embedded in your report is formatted professionally and legibly.
3. DML and Query Source Code Embedded (10 points)
 Project Part 4 DML script + 20 SQL queries assignment; see above. Ensure that all source code embedded in your report is formatted professionally and legibly.
4. DDL, DML, and Query Output (10 points)
 Show output from the execution of all DDL, DML, and SQL queries. Ensure that all output is formatted professionally and legibly.
9. Database Administration and Monitoring (10 points)

Solution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references.
Students may use these solutions for personal skill-building and practice.
Unethical use 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
, TRIP_TO VARCHAR2(50)
, TRIP_FROM VARCHAR2(50)
, START_DATE VARCHAR2(50)
, END_DATE VARCHAR2(50)
, 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
);

ALTER TABLE HOTEL
ADD CONSTRAINT HOTEL_FK1 FOREIGN KEY
(
PACKAGE_ID
)
REFERENCES PACKAGE
(
PACKAGE_ID
);

ALTER TABLE BOOKING_DET
ADD CONSTRAINT BOOKING_DET_FK1 FOREIGN KEY
(
BOOKING_ID
)
REFERENCES BOOKING
(
BOOKING_ID
);

ALTER TABLE BOOKING_DET
ADD CONSTRAINT BOOKING_DET_FK2 FOREIGN KEY
(
PACKAGE_ID
)
REFERENCES PACKAGE
(
PACKAGE_ID
);

This is only a preview of the solution.
Please use the purchase button to see the entire solution.

By purchasing this solution you'll be able to access the following files:

Solution1.docx

Solution2.zip

Purchase Solution

$66.00