This case study is about a computer systems development company which requires a mechanism for keeping track of the allocation of staff to projects. Study the case study carefully and then submit the following:
1. Draw an initial Entity-Relationship model, stating any assumptions you make.
You will have to make judgements as to what level of detail is appropriate, by taking into consideration the requirements of part (3) below.
2. Normalise the Project Allocation form (Fig 1) to arrive at third normal form entities. Show all the steps of normalisation clearly.
3. Implement and submit adequate documentation for the following:
(a) Create all the database tables needed for implementing the staff-project- allocation system, and populate them with appropriate data of your choice.
Submit Oracle scripts for each table, showing its description and its contents.
(b) Produce the following reports:
• (R1) A list of employees assigned to a particular project.
• (R2) A list of projects which an individual employee is assigned to.
• (R3) A list of projects which complete before a given date.
• (R4) The Project Allocation form (Fig. 1) which appears in the case study.
(c) Now produce two additional reports (R5 and R6) which you believe will be useful in this environment.
Consider extending your schema with either extra data columns or tables for this purpose. Give a clear textual description of what each of your two reports is supposed to show. Use your judgement as to what information the reports should contain and how they should be formatted.