[go: up one dir, main page]
Scribd
Upload
160 views3 pages

Project Step #4: Data Manipulation Language (DML)

The document outlines the requirements for Project Step 4 which involves creating a SQL script with data manipulation language (DML) statements to populate tables with sample data and 20 queries to run on the database. The consolidated technical report must include the DDL, DML, and query scripts as well as output. A literature review is also required. The DML script must populate tables with at least 10 rows each and automatically generate surrogate keys. The 20 queries must include basic and advanced queries such as joins, aggregation, and data modification.

Uploaded by

Derick cheruyot
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
160 views3 pages

Project Step #4: Data Manipulation Language (DML)

The document outlines the requirements for Project Step 4 which involves creating a SQL script with data manipulation language (DML) statements to populate tables with sample data and 20 queries to run on the database. The consolidated technical report must include the DDL, DML, and query scripts as well as output. A literature review is also required. The DML script must populate tables with at least 10 rows each and automatically generate surrogate keys. The 20 queries must include basic and advanced queries such as joins, aggregation, and data modification.

Uploaded by

Derick cheruyot
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

Project Step #4: Data Manipulation Language (DML)

20 Queries, and Consolidated Final Report

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:

2. Query 1: Select all columns and all rows from one table (5
points)
3. Query 2: Select five columns and all rows from one table (5 points)
4. Query 3: Select all columns from all rows from one view (5
points)
5. 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)
6. Query 5: Select and order data retrieved from one table (5
points)
7. 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)
8. Query 7: Select distinct rows using joins on 3 tables (5 points)

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

14. Introduction (5 points)


15. Overview (5 points)
16. Literature Review (10 points)
17. Assumptions (5 points)
18. Design Decisions (10 points)
19. 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.
20. 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.
21. Detailed Database Design (40 points)

a. Entity Relationship Diagram (ERD) (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.
b. DDL Source Code Embedded (10 points)
o Ensure that all source code embedded in your report is
formatted professionally and legibly.
c. DML and Query Source Code Embedded (10 points)
o 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.
d. DDL, DML, and Query Output (10 points)

a.
o Show output from the execution of all DDL, DML, and SQL
queries. Ensure that all output is formatted professionally and
legibly.

You might also like