Reference no: EM133297830

Project Step - Data Manipulation Language (DML)

Instructions

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
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
All of your project sequences and triggers must be used to automatically populate your surrogate keys.

3. Separate DML for Different Tables with Comments
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
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
2. Query 2: Select five columns and all rows from one table
3. Query 3: Select all columns from all rows from one view
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. Query 5: Select and order data retrieved from one table
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
7. Query 7: Select distinct rows using joins on 3 tables
8. Query 8: Use GROUP BY and HAVING in a select statement using one or more tables
9. Query 9: Use IN clause to select data from one or more tables
10. Query 10: Select length of one column from one table (use LENGTH function)
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
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
13. Perform 8 Additional Advanced Queries
NOTE: each query should have comment to describe the purpose of that query

These queries are open for you to design but must be complex in nature such as including multiple (3 or more) 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
2. Overview
3. Literature Review
4. Assumptions
5. Design Decisions
6. Statement of Work (SOW) --- I attached the existing copy of this, it should be updated based on the rubicatteched
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 ---- This document is attached, but it need to be updated based on the rubic attached for it
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
1. Entity Relationship Diagram (ERD) --- This is also attach but need to be updated based the rubic attach for it
• 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
• Ensure that all source code embedded in your report is formatted professionally and legibly.
3. DML and Query Source Code Embedded
• 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
• Show output from the execution of all DDL, DML, and SQL queries. Ensure that all output is formatted professionally and legibly.
Database Administration and Monitoring
Project Step #4 Deliverables
1. Technical Report in Word or PDF: LastName_FirstName_final_project.[doc|pdf]
2. Updated DDL Script in SQL or TXT: LastName_FirstName_DDL.[sql|txt]
3. DML Script in SQL or TXT: LastName_FirstName_DML.sql
To clarify, you are required to submit your final scripts (DDL and DML to include your queries) as embedded into your consolidated lab report along with their output as well as separately in SQL or TXT format to be executed and verified as error-free.

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:
1. Drop Statements for All Objects as needed (5 points)
Syntax:
/* drop table name*/
Drop table
[Schema.] Cases
[cascade contraints] [purge];
At the beginning of your script, ensure that all objects that are required to be dropped are properly dropped.
2. Create/Alter Statements for All Tables and Constraints (30 points)

1. Table Cases
create table cases (
case_id number(15) primary key,
opened date NOT NULL,
next_evid int NOT NULL,
message varchar (250) NULL,
closeby char (14) NULL
);
2 Table request_ext
create table request_ext (
req_id number (14) primary key,
req_fld1 VARCHAR(30) NOT NULL,
req_fld3 VARCHAR(30) NOT NULL,
req_fld4 VARCHAR(30) NOT NULL,
req_fld5 VARCHAR(30) NOT NULL );

Table 3-- lab_dept
create table lab_dept (
dept_id char (14) primary key NOT NULL,
upd_rep_idCHAR(8) NOT NULL,
descrptCHAR(60) NOT NULL,
email CHAR(100) NOT NULL,
contact char (50) not null
);
4 Table 4 Request
create table requests (
req_id char (14) primary key NOT NULL,
rvw_asn_idCHAR(14) NULL,
req_numvarCHAR(30) NOT NULL,
dept_idCHAR(14) NOT NULL,
serv_idCHAR(14) NOT NULL
);

Table 5 Lab
create table lab (
agc_id char (14) primary key NOT NULL,
agc_nameCHAR(50) NOT NULL,
descrptCHAR(60) NOT NULL,
agc_typeCHAR(30) NOT NULL,
contact char (50) NOT null,
addr_1 char (50) NOT NULL
);
In an order that is appropriate for repeated executions, ensure that all tables and constraints are properly created and/or altered.
3. 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 index index_name on frequently used columns)*/

create index cases_index on cases(case_id, opened);

4. 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 view view_name [(column aliases)] as */

CREATE VIEW cases_view as

select

case_id,

opened

from cases
where opened between '2017-02-01' and '2017-09-01';

view 2
/*Create view view_name [(column aliases)] as */

CREATE VIEW Closeby_view as

select

case_id,

opened

from cases

where closeby = 'Vinroy';

5. 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.
Select cases_seq.NEXTVAL from dual;
Create sequence cases_seq
Minvalue 1
Maxvalue 9999999999999999999999999
Start with 1
Increment by 1
Cache 20;
/* request sequence increment by 4*/
Create sequence requests_seq
Minvalue 1
Maxvalue 9999999999999999999999999
Start with 1
Increment by 4
Cache 20;

6. 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).
Create table for trigger
CREATE TABLE lab_audit_delete (
audit_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
table_name VARCHAR2(255),
username VARCHAR2(10),
col_name VARCHAR2(30),
mpod_date DATE
);
CREATE TABLE lab_audit_delete (
audit_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
table_name VARCHAR2(255),
username VARCHAR2(10),
col_name VARCHAR2(30),
mpod_date DATE
);
CREATE OR REPLACE TRIGGER lab_audit_trg
AFTER
UPDATE OR DELETE
ON lab
FOR EACH ROW
DECLARE
all_transaction VARCHAR2(10);
BEGIN
-- determine the transaction type
all_transaction := CASE
WHEN UPDATING THEN 'UPDATE'
WHEN DELETING THEN 'DELETE'
END;

-- insert a row into the audit table
INSERT INTO lab_audit_delete(table_name, user_name, col_name, mpod_date)
VALUES('lab', all_transaction, col_name, mpod_date);
END;
7. 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.
8. 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.
9. 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.
10. Executable, Error-Free Script (10 points)
The script you submit must fully execute and be error-free.
Project Step #3 Deliverables