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:

• Use data control language (DCL) to control access to the data by writing structured query language (SQL) statements to add a group of 5 users who have been granted permission to perform specific system privileges (CREATE object) and object privileges (INSERT, SELECT, UPDATE, and EXECUTE).


• Write the SQL-stored procedures that will support the efficient execution (using COMMIT and ROLLBACK) of 3 transactions (INSERT, UPDATE, and DELETE) in the database.


• Generate an execution plan for each query created in the Week 4 IP and provide a performance analysis of the results of each plan.
  
  
  
  • 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.
  
  
  
  


• Create a backup plan under the full recovery model and provide a rationale for your model.


• Be sure to cite your sources in-text and in the References section by using APA style.


• Add the Week 5 IP to the database system plan and complete the plan (incorporating feedback as needed).


• Name the document as follows:
  
  
  
  • yourname_IT610_IP5.doc
  
  
  
  


• Submit the document for grading

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.