0

Consider a relation that depicts a tutorial room booking in a university. Each faculty assigns a person to handle the booking for all tutorial classes for that faculty. The person’s email address is given to the university’s booking system as a contact person. BOOKING(b_date, b_starttime, b_endtime, unit_code, contact_person, room_no, tutor_id).

Exercise: Identify candidate key(s) and primary key for the relation if the following business rules are applicable:

1) Tutorial classes can be either 1 hour or 2 hours long.

2) A tutor can only teach one tutorial class in a given unit.

3) There are no parallel sessions of tutorial classes.

My answer: 1 of candidate key is (tutor_id, d_date). My suggestion key can make each tuple unique BUT there is no constraint that prevents users input incorrectly as below:

tutor_id | d_date | b_starttime | b_endtime | unit_code |

AAAA | Mon | 6 PM | 8PM | FIT1111 |

BBBB | Mon | 6 PM | 7PM | FIT1111 |

OR

tutor_id | d_date | b_starttime | b_endtime | unit_code |

AAAA | Mon | 6 PM | 8PM | FIT1111 |

BBBB | Mon | 7 PM | 8PM | FIT1111 |

Consequently, my key does not meet the business rule no.3. Then, I add 2 more attributes to candidate key (tutor_id, d_date, b_starttime, b_endtime).

My questions is when we choose candidate key, apart from guaranteeing the uniqueness of each tuple, do we need to prevent users from possibly wrong input which may break the business rules? If yes, when we set, for example, 4 attributes (A, B, C, D) as the primary key, whether the DBMS blocks users from wrong input action as in the table above?

Thanks.

Gemi Kong
  • 3
  • 1
  • Hi. See my answer re your main question. But it is hard to discuss this example because you are not clear enough. Read & act on hits googling 'stackexchange homework'. CKs are determined by FDs. What are your FDs & all CKs & why & *then* what are further constraints & why? Show your work following your textbook definitions & algorithms. PS Format tables in code blocks. See the edit help & always look at the formatted version of your post before posting. Please do not edit a question in a way that invalidates reasonalble answers already posted. Please only ask one question per post. – philipxy Aug 11 '18 at 06:21

1 Answers1

1

My questions is when we choose candidate key, apart from guaranteeing the uniqueness of each tuple, do we need to prevent users from possibly wrong input which may break the business rules?

Yes, we should declare enough constraints to disallow all invalid states. And yes, declaring one or even all CKs (candidate keys) is not necessarily enough. (Why would it be? Do you think you have a reason why it should?)

A CK doesn't just state the uniqueness of a set of attributes. It also states the non-uniqueness of any proper subset of that set.

CKs are used in normalization to higher NFs (normal forms), which gets rid of certain problematic constraints that a table must always be the join of other tables that you could have instead. It drops your current table & introduces those tables & their CK constraints & sometimes FK (foreign key) constraints, that subrows must appear elsewhere as CKs. But still other constraints can hold that should be declared.

If yes, when we set, for example, 4 attributes (A, B, C, D) as the primary key, whether the DBMS blocks users from wrong input action as in the table above?

You mention that a particular CK constraint wouldn't prevent certain invalid states. But since you should always declare all CKs, you should find them all before you start looking for invalid states that aren't prevented. Also "Then, I add 2 more attributes to candidate key" makes no sense because 1. that is not how we find CKs and 2. a CK plus other attributes cannot be a CK.

(Read & act on hits googling 'stackexchange homework' & post a new question showing your work following your textbook finding the CKs.)

philipxy
  • 14,867
  • 6
  • 39
  • 83