DATABASE DESIGN EXERCISE

Database Design Exercise



Database Design Exercise

Problem Description

Software issue tracking is an integral part of any enterprise 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 2008)

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 2010)

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