Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2004
    Location
    London
    Posts
    8

    Unhappy Student Database Design

    Hi Everyone,

    I'm a student attempting to do relational database design from a given scenario. But I have no experience in database design and I would really appreciate some help and advice from you guys. I am having difficulty with everything. Let me start by displaying the scenario and what needs to be designed.

    The Vale Universityˇ¦s (VU) media services department would like to set up an on-line booking system to allow lecturers to book equipment/services themselves from anywhere within VU. The system should produce a daily list of booking for media services staff and in the case of equipment booking should ensure that stock is available before a booking is accepted.

    At present, the system is a manual one. The department maintains a stock of equipment, ranging from cameras/TV monitors through to flip chart holders. The department also issues consumables to staff. Lecturers wishing to book equipment place their requirements in person or by phone/email. The department staffs ensures the equipment is available for the given date, then send an email/phone confirmation and fill out a booking form for the equipment. Lecturers will sign the booking form when the collect the item and take away a copy of the signed form. The equipment is booked for a minimum of one day. When they return the equipment the media staff checks it over to make sure that is not damaged, and return it back into stock. On occasions the piece of equipment has been found damage and has had to be taken of stock to be repaired.

    The vice chancellor now wants the media services department to bill faculties/schools for use of the service. To this end, the media services manager needs to record the daily hire rate for each category of equipment and it will need to generate appropriate reports at the end of each month. The manger feels that a computerised database should make the whole process more efficient. He has drawn up an initial list of requirements.

    The system should generate the following reports:

    „h Daily/Weekly booking schedule
    „h Outstanding returns schedule
    „h Stock list
    „h Annual booking /consumables costs:
    Per lecture
    Per school/faculty
    Per equipment type
    „h Repair cost (these are to be billed to the school of the lecturer responsibly!)

    He would like lectures to be able to place booking directly, rather than having to liased with a member of the media services (although this service would still be available). He would also live the lecturers to be able to view their booking history. Ranges of summary statistics should be available on line for each faculty/school head.
    The Vale Universityˇ¦s (VU) media services department would like to set up an on-line booking system to allow lecturers to book equipment/services themselves from anywhere within VU. The system should produce a daily list of booking for media services staff and in the case of equipment booking should ensure that stock is available before a booking is accepted.

    At present, the system is a manual one. The department maintains a stock of equipment, ranging from cameras/TV monitors through to flip chart holders. The department also issues consumables to staff. Lecturers wishing to book equipment place their requirements in person or by phone/email. The department staffs ensures the equipment is available for the given date, then send an email/phone confirmation and fill out a booking form for the equipment. Lecturers will sign the booking form when the collect the item and take away a copy of the signed form. The equipment is booked for a minimum of one day. When they return the equipment the media staff checks it over to make sure that is not damaged, and return it back into stock. On occasions the piece of equipment has been found damage and has had to be taken of stock to be repaired.

    The vice chancellor now wants the media services department to bill faculties/schools for use of the service. To this end, the media services manager needs to record the daily hire rate for each category of equipment and it will need to generate appropriate reports at the end of each month. The manger feels that a computerised database should make the whole process more efficient. He has drawn up an initial list of requirements.

    The system should generate the following reports:

    „h Daily/Weekly booking schedule
    „h Outstanding returns schedule
    „h Stock list
    „h Annual booking /consumables costs:
    Per lecture
    Per school/faculty
    Per equipment type
    „h Repair cost (these are to be billed to the school of the lecturer responsibly!)

    He would like lectures to be able to place booking directly, rather than having to liased with a member of the media services (although this service would still be available). He would also live the lecturers to be able to view their booking history. Ranges of summary statistics should be available on line for each faculty/school head.

    My initial attempt I found the following tables. Staff member, Faculty, stock, booking, booking line and category. I donˇ¦t know if this is right. Designing is definitely not my strong point. I would really appreciate some help in finding the right answers. I know I need more tables and attributes, but I donˇ¦t know what. Please can you help.

    Thanks

    Jason
    Last edited by StJason; 01-15-04 at 18:41.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •