It 610 Colorado University, RELATIONAL DATABASE MANAGEMENT SYSTEMS IT610 DQ4 AND IP4
Structured Query Language Phase 4 - Task 1 Task Type: Discussion Board Deliverable Length: see assignment details Primary Discussion Response is due by Wednesday Primary Task Response: Your first task is to post your own Key Assignment Outline to the discussion area so that other students are able to review your plan. Attach your document to the main discussion post, and include any notes you feel are appropriate. The purpose of this assignment is to help improve the quality of the Key Assignment Draft you will complete next week. Phase 4 - Task 2 Task Type: Individual Project Deliverable Length: Paper of 4–5 pages (excluding references and cover page) + Database System Plan template Key Assignment Draft You have been asked to develop a database system plan for a local company (or a company of your choosing) that wants to track its customers/clients, appointments, billing, and employees. Structured query language (SQL) is used to create the database schema, maintain the database records, and perform administrative tasks. In addition to the Database System Plan template that includes section headings (project outline), submit a SQL Statements document of 4–5 pages based on the entity relationship (ER) model completed in Week 3. Complete the following: Include SQL statements that use data definition language (DDL) commands to create the database schema specified in the ER diagram created in Week 3. Include indexes, constraints, and relationships. Include SQL statements that use data manipulation language (DML) to do the following: INSERT 15 records into each table. UPDATE 5 sets of records in each table using one specific criteria per update statement (e.g., EXACT MATCH, LIKE, BETWEEN, IN, or HAVING). DELETE 2 records from 1 table based on specific criteria (e.g., EXACT MATCH or LIKE). Create stored procedures to support retrieval of the data from the database, using SELECT statements to accomplish the following: Aggregate (group) functions (count, avg, min, max, sum) Write 4 queries using group functions. Group By Write 2 queries using the Group By, Having, and Order By syntax. Simple queries Write 4 queries that manipulate data in 1 table. Join queries Write 4 queries that join 2 or more tables. Add this section to the SQL Statements section of your database system plan. Name the document as follows: yourname_IT600__IP4.doc All sources should be cited both in-text and in the References section by using APA format. Submit the document for grading. Please submit your assignment.