CSCI 5333.4 DBMS
Homework #2

Due date: 9/28

Based on the solution of HW#1, provide a reaonable set of relation schemas for implementing the class diagram. Use the suggested solution of HW #1, not your own solution. Underline the primary key of each relation and identify all candidate keys and foreign keys.

Bonus (10%): identify the highest normal form of each relation.

Recap of the original problem:

A Toy Web-Based Work Request System

An university would like to build a web-based work request Intranet system so that users can request works to be performed on some computer systems and technicians can be assigned requests.

There are three kind of users of the systems: requesters, technicians and administrators. A requester (regular user) makes work requests on some computers. A technician accepts a work order and reports the result of processing the work order. Within the university, every computer has an unique id. A computer may belong to a computer group, which may belong to a parent group in turn. For example, a lab may contain three groups of computers, each group may contain some number of computers. Indeed, a computer group may be composed of several subgroups and several individual computers. A computer group may be homogeneous or heterogeneous. A homogeneous computer group contains only computers of the same configuration (i.e. same RAM Size, HD Size, Operating Systems, installed software, etc). A computer may be considered as a homogeneous computer group with one computer. A heterogeneous computer may contain computers of different configurations.

For every computer, various information about its configuration should be stored, including RAM Size, HD size, MAC addresses, operating systems and software installed. The software installed must be from a list of supported software, which is operating systems dependent. For example, Firefox may be supported in Linux, but not Solaris. Currently, supported operating systems include Windows Vista, Windows 7 (Client and Server), Ubuntu Linux 10.10 and 11.1, and Solaris 10, Solaris 11.

When a requester requests a work through the request page, he may specify the targeted computer or homogeneous computer group, and the problem. A problem may contain several problem items. If a problem item is related to a software, then the software should be named. Again, only supported software will be supported. For each problem item, the requester may specify a problem description and a comment. A submission time is automatically inserted when the request is submitted so works can be prioritized in submission time.

A technician may log onto the support page of the Intranet to find out what works are pending. A regular user cannot log on the support page. The technician can then select a request to work on. Subsequently, he can log on to the site to report progresses. His comments together with the report time should be stored in every progress report. Only one technician works on one request. If a technician has claimed a request, it will be shown in the support page.

An administrator can create new regular user and technician accounts through the administration page. An administrator may also assign works to technicians. To help the administrator assigning work, the expertise (on software) of every technician is stored. For all users, their names, SS# (if possible), office phone numbers and business units should be stored. A business unit may be within a parent business unit. For example, faculty member A may work for CSCI, which is a subunit of SCE. Each business unit may have a head, a secretary and a phone number. Both the head and secretary are regular users. Both the technicians and administrator can check on the history of a request, listing all the comments and progresses related to any request. A regular user can only check on the history of his own requests, but not others.