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 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.

Thanks

Jason

So the diagrams will be in word. Okay let's take a shot at it.

Hi Certus,

Thanks for stopping and having a look at my problem. Yes the diagram is in word, as our university doesnt seem to be working.

Thanks again

Jason

First look at who is making the booking, Faculty. What are all the entities associated with Faculty?

Next look at stock. What is stock composed of?

Does a booking really have booking lines?

Answer that and we'll take it further. Forget about attributes for now. They just confuse the issue.

First look at who is making the booking, Faculty. What are all the entities associated with Faculty?

Lecturers are the main entities, Including other Staff members

Next look at stock. What is stock composed of?

Stock is composed of equipment, cameras etc. And consumables, i.e. food.

Does a booking really have booking lines?

I’m not to sure about this, I was thinking down the line of orders at the time. How about booking items.

Answer that and we'll take it further. Forget about attributes for now. They just confuse the issue.

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.

Don't "rush to code."

Okay, let's put our ideas together.

You still there StJason?

Hi Certus,

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.

Thanks

Jason

A good effort, Jason, but there is work to be done.

First Bookings and Purchases should be treated separately.

Second you Book Equipment and Purchase Consumables

Billing is a rollup of purchases into staff into faculty.
Billing is also a rollup of bookings into staff into faculty.

You don't need booking lines or purchase lines.

A booking is on a time line.

It has an estimated start date and end date.

It has an actual start date and end date.

A repair is like a booking. It has a start date and an end date.

A comsumables stock report is a GROUP BY query. No need for stock tables.

Keep your attributes to a minimum. You are still attempting to put together an Entity Relationship Diagram. Entities and Relationships. Attributes can come later.