1

I am working on a basic blood bank database on Oracle. I would like to create a completely basic normalized table and then I will add more details to it. I have done it to the 2NF and I will further proceed on to the 3NF afterwards, but first can someone take a look at my normalization and help me improve it (if needed) or any advices regarding it.

my unnormalized and 1st nf normalized tables

2nd nf tables

Improve this question
3
  • Looks fine to me. What more can be normalized? Seems to me that any further normalization will only introduce unnecessary complications.
    – Itai Bar-Haim
    May 17, 2016 at 14:31
  • Please provide your table structures as CREATE TABLE blah (FIELD1 TYPE1, F2 T2,.... Fn Tn); and data as INSERT INTO BLAH VALUES (V1, V2,... Vn); Help us to help you.
    – Vérace
    May 17, 2016 at 15:24
  • To normalize to BCNF or lower forms one must know all functional dependencies. You didn't give them. Are they supposed to be communicated by your sample data? You didn't say. Otherwise, why do you think we can normalize to 2NF without them? Of course we can offer designs based on (hopefully enumerated) guesses.
    – philipxy
    May 22, 2016 at 21:25

2 Answers 2

Reset to default
2

Excellent points by @joishi-bodio.

Here's a simple diagram explaining the different entities, attributes, and their relationships in your model.

The first step in modeling any database is to identify the real-world entities; the attributes that define each entity; and then their relationships and cross references.

Here are different observations and conclusions that you may draw from the example -

Entities and Attributes -

  • The most obvious real-world entity looking at your example is PERSON.
  • The entity Person can be defined with attributes like Name, Age, Gender, DOB, Blood group type
  • A PERSON can be either a 'Blood Donor' or 'Blood Receiver' or both. 'Donor' and 'Receiver' sound like the different roles that a Person takes, but are not different entities by themselves.
  • Blood Group can be another entity and can be defined by the name of that blood group among other things.
  • In a hospital, blood is stored in blood bags, which could be another entity, defined by who the donor for that blood bag is.
  • Transactions is another table which is the store for all blood transactions. A transaction can be defined by a blood bag being assigned to a person (receiver) on a date.

Relationships:

  • Every person has exactly 1 blood group. You can have 0 or many people with that group.
  • Each bloodbag has blood from 1 person. A person may or may not have blood in many blood bags.
  • You can assign 0 or many blood bags to a receiver. A person may or may not be a donor to one or many blood bags.

We get something similar to -

Improve this answer
1

bloodgroup can be moved to it's own table.. You probably don't need bloodgroup at all in the blood_bag table, considering that it's always going to match the donor's blood type. Likewise, you shouldn't need the donor_id in the transaction table since it's always going to match the blood_bag's donor.

I'm not really sure why you have a receiver table at all ... you probably just want a person table that records their name and blood type .. Then both the transaction table donor_id and receiver_id would point to the person table. (although, again, that table doesn't need donor_id at all..)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.