7.1 Not all the tables in RMH Homebase satisfy all six database normalization criteria. For example, dbDates doesn't satisfy either criterion 5 or criterion 6. Give another example of how a RMH Homebase table violations normalization criteria 5. Give another example of how a table violates criterion 6. Explain why either of these anomalies can cause a problem - create more programming work or introduce an error - when updating one of these tables.
What are the six database normalization criteria?
- The rows can be rearranged without changing the meaning of the table.
- The columns can be rearranged without changing the meaning of the table.
- No two rows of a table are identical. This is often accomplished by defining one column whose values are mutually unique. This column is known as the table's primary key.
- No row has any hidden components, such as an object id or a timestamp.
- Every entry in the table has exactly one value of the appropriate type.
- No attribute in the table is redundant with the primary key.
The persons field of dbSchedule violates criterion 5 by allowing multiple values to be inserted. When you try to query an entry with multiple values, this creates more complexity than is needed and can have unwanted side effects. It is better to keep things as straightforward, organized, and simple as possible.
The id field of dbPersons violates criterion 6. This field combines the first_name and phone1 fields creating redundancy and causing the primary key to be non-unique. If, for example, a father and son (John-senior and John-junior) who not only share the same first name, but also the same phone number, are entered into this database and a query is run- it would result in two records that would try and use the same id value- what a mess!
7.2 Develop and unit test the functions get_shift_month, get_shift_day, get_shift_year, get_shift_start, and get_shift_end for the dbShifts.php module that are called by the new get_shift_name_from_id function shown in Figure 7.14.
For this exercise, I consulted with my Team FOSSils.
The first step to this exercise was to modify the get_shift_name_from_id function to the following:
function get_shift_name_from_id($id) {
$shift_name=date("l, F jS, Y",
mktime(0,0,0,get_shift_month($id),get_shift_day($id),
get_shift_year($id)));
$special_cases = array (
"night" => " night shift", "chef" => " guest chef");
if (array_key_exists(substr($id, 9), $special_cases))
$shift_name = $shift_name . $special_cases[substr($id, 9)];
else{
if (strpos($id, "Fam") !== false)
$shift_name = $shift_name . " Family Room ";
$shift_name = $shift_name . " from ";
$st = get_shift_start($id);
$et = get_shift_end($id);
$st = $st <12 ? $st . "am" : $st - 12 . "pm";
if ($st == "0pm")
$st = "12pm";
$et = $et <12 ? $et . "am" : $et - 12 . "pm";
if ($et == "0pm") $et = "12pm";
$shift_name = $shift_name . $st . " to " . $et;
}
return $shift_name;
}
To implement the rest of this exercise, I decided to create some simple getters for the functions to be tested such as:
function get_shift_month($id) {
return substr($id, 0, 2);
}
We then made up some simple assertion unit tests like the following:
$this->assertTrue(get_shift_month() == "01");
$this->assertTrue(get_shift_day() == "01");
$this->assertTrue(get_shift_year() == "01");
Exercise 7.3 – Design, implement, and unit test the changes to the database modules required by the new feature Item 4: Calendar month views in the "wish list" described in Appendix B.
After having read this question multiple times, consulting Appendix B, AND attempting to modify dbWeeks implementation; I have come to the conclusion that this is an impossible exercise. I am planning on consulting with my fellow students in class.
