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 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 s 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.
Also... a practical booking system will probably require an architecture wherein the professor makes an order for what he/she needs, presses a button to "submit" that order for evaluation, and receives a response that is either a confirmation or a list of what parts of the request could or could-not be handled. It may also be useful if the system listed alternatives.
The design of the user-machine interaction in this system needs to be carefully and thoroughly planned out, and I think it will have considerable impact on the database. Therefore, your initial schema ideas should be considered just that... ideas, to be finalized only after the process design is completed.
Attached is a 2nd draft at my ER diagram. Please can you have a look to see if i'm on the right path. I was advised i should have a table called purchases. However i still dont know where this fits into my diagram. Also i feel i need some sort of goods return form/table, again i have no idea. Can you help.