Year 12s: what will be the first way you research your future options? >>

Basic database design help!

I'm looking for some one to explain to me how I would add foreign keys to the relevant entities to enable a relationship to be established between the following tables:

http://i.imgur.com/dqwJVnC.png

I would also appreciate if someone could explain to me how I would determine referential integrity for each entity, and within the selection at least one occurance of cascade update and one occurance of cascade delete. (These may be selected seperately, or together.)

Not to do it for me btw, just to explain how I would do it lol.

Reply 1

djphillips1408

First thing is that you have fundamental flaws in your table for animal. By having a vet field in there you are limiting yourself to the pet only ever being treated by one vet. That field needs to come out, and I would suggest that it is replaced by customer id as a foreign key. This does of course assume that a pet will only ever have one owner in its life but I guess we can assume that the sort of people who use vets are likely to keep a pet for life.

I uses you are trying to record visits of animals to a vets and for that you need to create tables to join everything up. At the moment your vet table is an entity that has no connection to your other tables in its current form. The last vaccine dates and illness also need to come out, unless you intend to delete the old data every time a vaccine is administered or a new illness is diagnosed!

Actually another issue could be that by having an address field in your vet table you are assuming that a vet can only operate out of one practice. I don't know the ins and outs of how vets work but if a vet works out of 2 offices of a practice then you have a problem. Certainly the vet I use does some mornings in one location and some at another

(edited 11 years ago)

Reply 2

hypercaine.

Do you have a scenario on which this is based? The way I usually go about doing it is just writing down the tables and their attributes (as youve done) and then eliminating ones which arent necessary, e.g. a pet will be treated by a vet (lets assume only one vet treats a pet) therefore a relationship between the pet and vet table. By creating a surgery table you can include the address of the surgery and include the surgery ID in the vet table to see which vet is at which surgery (again, assuming theres more than one surgery) Then add in all the attributes for each, you then need to understand that there will be relationships between all of them, e.g. the pet table should have customer ID and possibly vet ID. Its OK to assume certain things from the scenario.

By starting with just a list of tables you can see what relationships are formed, some might be harder to see than others but just use common sense and form an entity relationship diagram and im guessing it will need to comply with third-normal form? Once you have a ER diagram youre pretty much done and can begin creating the tables.

Quick Reply

Related discussions

Latest

Articles for you

Terms and conditions

What is an LLM and how could it benefit your career?

Powerful questions business students forget to ask their career advisers

Why I chose a law degree and what studying law was like