Good morning, I wonder how I can make sure this schema is in BCNF.
petition(ID, title, contents, budget, organizationID, official, resultID, applicantID)
applicant(ID, name)
official(ID, name, department)
organization(ID, name, phoneNumber)
*Each petition has an official.
*Each petition should have more than one organization.
This is the hardest option that I cannot handle. With the same petitionID, there can be several organizations. Also, even though I insert several tuples whose elements are the same except organizationID, if there is any change in budget or result, those tuples should be changed together without causing any anomaly.
*budget can be NULL.
*The result would be filled after they deal with the problem.
Advertisement
Answer
From what you write, I understand there’s a many-to-many relationship between “petition” and “organization”. This means you need an additional table representing this relationship. E.g.:
petition(ID, title, contents, budget, official, resultID, applicantID)
applicant(ID, name)
official(ID, name, department)
organization(ID, name, phoneNumber)
and
organization_petition(organizationID, petitionID)