Database Design Proposal

Problem Description

Software issue tracking is an integral part of any college software development lifecycle. The issue tracking toolkit is responsible to create, store, trace, manage issues (e.g., software bugs or requests for new features). Each issue is represented by a ticket that must capture the following information

the actual issue

the components or projects effected by the issue

developer/customers who first identified the issue

the developers/managers who are responsible to address the issue

the state of the issue

other related issues (tickets) (Benton 102)

Deliverables

The goal of this exercise is to provide a practical experience, as a database designer and administrator. Your tasks are

gathering and capturing the necessary requirements (which are partially given)

modeling the requirement using the E/R conceptual model

producing a detailed database schema from the E/R model and creating the DDL to generate your schema

using your compiled requirement derive a set of data dependencies (FD) and data constraints (e.g., the primary and foreign keys)

using the FDs normalize your original database schema into BCNF

proposing a set of indexes, based on your final schema, that you expect to improve query running time; you must justify each proposed index by identifying which queries you expect to benefit from these indexes. (Handfield 45-48)

improving the query performance by altering the database schema, e.g., de-normalization (reversing BCNF to avoid joins), or further decomposition (to reduce the amount of useless data a query finds in each tuple). you must discuss the pros and cons of each proposal.

evaluating the query speed up for selected queries which highlight the impact of your proposed optimizations. You also need to generate some dummy data for this portion of the assignment.

This assignment was purposefully designed to be open-ended, and a portion of the grade is reserved for going that extra mile in exploring other potential requirements for an college change management toolkit, extracting interesting and non-trivial FDs from your requirements, and proposing non-trivial optimizations, e.g., indexes, schema alteration, and views. NOTE: We will build an app around this database design in A3, so it's in your best interest to make it maximally usable.

It is very obvious that the data backups need to be monitored more closely and tested for accuracy on a more frequent basis. The IT department has implemented a new procedure that backs-up the data every hour and tests its integrity every night. New hardware was installed and our database ...