Question

Consider the following database schema used to track blood donations. The database has
been in use for several years and contains thousands of entries. Primary keys are Bolded
and foreign keys carry the same names as the primary keys they reference

Receptionist(RID, Name, Address, Phone#)

Donor (Name, Address, Phone#, SSN, Gender, DOB)

Blood(BID, Blood_type, Cost, Name, Phone#, NID)

Nurses(NID, Name, Address, Phone#)

Transfusions(PatientID, Date, Trans#, BID)

Question: Since donors may donate every 60 days, a collection date needs to be added to the
blood relation. How do we achieve this?

Answer 1

All we need to do is to add a new column to the table blood using a simple alter table query.

The name of the column is collection_date. Since it defines the day on which the blood was collected, we need to have a datatype of date.Blood table already has a foreign key relation with the Donor table.

So, to achieve this, we need to fire a query like:

ALTER TABLE Blood ADD Collection_date date NOT NULL

and this will do the job.

Some check constraints might be needed to apply on this collection_date so as to check the donors can donate only once every 60 days.

To add the data for the collection-date for the past donations(if available) one can fire the update query to set the date or can have a default date for the past data.