Oracle – Advice on Database Normalization

database-designdenormalizationoracle

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

Best Answer

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 -

Related Question