Database system
Key Assignment
Part 1 Tasks (New Content for Week 5)
Database administration is an important element of database planning. Security and performance issues need to be considered prior to the actual implementation of the database in the production environment. Write a Database Administration Plan document of 4–5 pages for the case study organization. Complete the following:
Provide a screenshot of the execution plan for each query.
In your analysis, discuss which queries you would change, including how and why you would change the queries.
Name the document as follows:
yourname_IT610_IP5
Part 2 Tasks (objectives from Weeks 1–4)
- System Requirements Specification (Week 1): Include a feasibility analysis that details what specific steps need to be performed at this phase of the project, clarifies the scope of the project, provides an estimate for cost and time to develop the database, and justifies the need for the proposed database.
- Conceptual Data Model (Week 2): Identify the entities and highest-level relationships using an embedded diagram. Describe the problem and data requirements in the narrative, as well as your rationale for the conceptual model. Justify your design by discussing how the design will solve the problem presented in the case scenario.
- Entity Relationship Diagram (Week 3): Create an entity relationship (ER) diagram that includes the primary keys for all entities, the relationships between different entities, the attributes (and data types) for each entity, and junction tables to support any many-to-many relationships. Be sure that the design meets a minimum of third normal form (3NF).
Describe the steps that you took to ensure that your design was normalized to the desired level and any exceptions you had to make to prevent anomalies and a loss in data integrity.
- SQL Statements (Week 4): Create the data definition language (DDL) and data manipulation language (DML) SQL statements that will be used to create the database schema. Be sure to 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 1 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 the retrieval of the data from the database, using SELECT statements to accomplish the following:
Aggregate (group) functions (e.g., count, avg, min, max, and 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.