COMP 332 Principles of Database Design by PhilCantillon


									                     COMP 332: Principles of Database Design
                             Tutorial: Problem Set 1—Supplemental

The Student Helper Time Sheet form shown on the next page is used by the HKUST Finance Office
to determine the monthly pay for student helpers. In addition to the information provided on the
form, we also know the following additional real world knowledge regarding this application.
   A student helper can work for one or more departments (in a given period or in different
   periods). For each time sheet, there is a requisition, identified by a BR No. (Budget Requisition
   Number) that authorizes payment to the student. A requisition can be for many time sheets, but
   each time sheet has exactly one requisition that authorizes payment. A requisition and its
   related time sheet(s) are submitted together to the Finance Office. Only one TA endorses and
   one approving officer approves each time sheet. The endorsing TA and approving officer are
   both from the same department as the one that pays the student for a given time sheet. For
   auditing purposes, it is necessary for the Finance Office to know who endorsed and who
   approved a studentʼs time sheet, the date of the approval and the date the time sheet was
   submitted for payment. Finally, all student helpers are paid through autopay and so must supply
   their bank information.

(a) Identify all the entities represented by the form and the above problem description and show
    their attributes. Assign reasonable names to the entities and attributes identified. It is not
    necessary to specify keys for the entities.
(b) Prepare an ER diagram showing how the entities identified in (a) are related. Show as
    necessary, generalizations and connectivities that can be clearly inferred either from the given
    form, from the problem description or from real world knowledge. Connectivities that cannot be
    inferred should be left unstated. It is not necessary to show weak entities.
The overall goal of your conceptual schema design should be to minimize redundancy in both the
conceptual schema and the database.
                            The Hong Kong University of Science & Technology
                                        Student Helper Time Sheet
                                                 (for Daily/Hourly paid)
                                               (Ref.: Financial Circular No. 1/96)

                                                                                     Ref : BR No. : _______________

                                                                                     Department            : _______________

Student Name                : ________________________________________________________________
(as used in Bank Account)

Student I.D. No. : _____________________________                              Staff No. : _____________________
(for HKUST student only)                                                      (if applicable)

Period worked from _____________________________                              to ___________________________

e-mail : ____________________________________
Date                                                         Total                                                            Total
Day        Sun     Mon      Tue   Wed   Thur    Fri   Sat              Sun   Mon     Tue        Wed     Thur   Fri   Sat

Date                                                         Total                                                            Total
Day        Sun     Mon      Tue   Wed   Thur    Fri   Sat              Sun   Mon     Tue        Wed     Thur   Fri   Sat

Date                                                         Total
Day        Sun     Mon      Tue   Wed   Thur    Fri   Sat

Endorsed by TA:                                        Total number of *day(s)/hour(s) for the period:


Claimed by : ________________________                                Approved by : ________________________
                                   Student                                                            Approving Officer

Date              : ________________________                         Date             : ________________________

* delete as appropriate

Autopay Information
To be completed only if (i) this is your FIRST submission of time sheet or
                        (ii) you want to change the bank account for autopay

Bank Name            : ___________________________________________________________________

Account No. : ___________________________________________________________________

FOF 72/91/03                                                                                                              March 2002

To top