| Server IP : 134.29.175.74 / Your IP : 216.73.216.160 Web Server : nginx/1.10.2 System : Windows NT CST-WEBSERVER 10.0 build 19045 (Windows 10) i586 User : Administrator ( 0) PHP Version : 7.1.0 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : C:/nginx/html/MichaelMalz/CST1600/Resources/ |
Upload File : |
<?
// index.php
$TRACK = "<b>".basename(__FILE__).'</b> <span class="pv_fl">('.__FILE__.")</span>\n<ol>\n";
ini_set('include_path',ini_get('include_path').PATH_SEPARATOR.$_SERVER['DOCUMENT_ROOT']."/"); // Add the DOCUMENT_ROOT to the include_path.
$skipAuthentication = true;
include('application.phpinc');
include('Course/courseInfo.phpinc');
include('../classInfo.phpinc');
#include('Course/CourseControl.phpinc');
$title = "Jim's Normiization Rules" ;
$headTitle = '_CST_ - '.$courseNumber.' '.$courseTitle.' - '.$title;
if ( isset($class['section']) ) {
$pageTitle = $courseNumber.'-'.$class['section'].' '.$courseTitle.'<br>'.$title;
} else {
$pageTitle = $courseNumber.' '.$courseTitle.'<br>'.$title;
}
$pageMenu = 'Course/courseMenu.phpinc';
$js .= ',schedule.js';
include('common/pageHeader.phpinc');
$sectionTitle = 'Normiization Rules';
include('common/sectionHeader.phpinc');
?>
<ol>
<li class="bold nowrap">Identify the entities.</li>
<li class="bold nowrap">Identify the primary keys for each entity table.</li>
<li class="bold nowrap">Eliminate repeating groups in individual tables.</li>
<li class="bold nowrap">Remove the possibility of INSERT, UPDATE, or DELETE anomolies.</li>
<li class="bold nowrap">Relate the entities back together.</li>
<li class="bold nowrap">Make sure the tables do not allow bad data.</li>
</ol>
<p><b>Example:</b>
<ol>
<li>
<b class="nowrap">Identify the entities.</b>
<br>These are the who, what, where, or when items.
They are rarely directly related.
Performing this operation first can solve many of the INSERT, UPDATE, or DELETE anomolies thay may be in the initial tables(s).
<br>e.g. Given a table <b>client_interviews</b> with the fields: <b>ClientNo</b>, <b>ClientName</b>, <b>ClientPhoneNumber</b>, <b>InterviewDate</b>, <b>InterviewTime</b>, <b>StaffNo</b>, <b>StaffName</b>, <b>StaffWorkHomePhoneNumbers</b>, and <b>RoomNo</b>:
<ul>
<li><b>ClientNo</b> is data about a <b>Client</b>. This is a who.</li>
<li><b>ClientName</b> is data about a <b>Client</b>. This is a who.</li>
<li><b>ClientPhoneNumber</b> is data about a <b>Client</b>. This is a who.</li>
<li><b>InterviewDate</b> is data about an <b>Interview</b>. This a what.</li>
<li><b>InterviewTime</b> is data about an <b>Interview</b>. This a what.</li>
<li><b>StaffNo</b> is data about <b>Staff</b>. This is a who.</li>
<li><b>StaffName</b> is data about <b>Staff</b>. This is a who.</li>
<li><b>StaffWorkHomePhoneNumbers</b> is data about <b>Staff</b>. This is a who.</li>
<li><b>RoomNo</b> is data about a <b>Room</b>. This is a what.</li>
</ul>
So I break it up into four entity tables:
<table>
<tr><th>Table</th><th>Fields</th></tr>
<tr><th>client</th><td>ClientNo, ClientName, ClientPhoneNumber</td></tr>
<tr><th>staff</th><td>StaffNo, StaffName, StaffWorkHomePhoneNumbers</td></tr>
<tr><th>interview</th><td>InterviewDate, InterviewTime</td></tr>
<tr><th>room</th><td>RoomNo</td></tr>
</table>
Don't worry about relating the entities back together yet.
</li>
<li>
<b class="nowrap">Identify the primary keys for each entity table.</b>
<br>Entity tables should have one and only one field as the primary key.
<br>If no single field can be identified as a primary key, one can be added.
<br>Note that for the <b>interview</b> table I need to add a primary key.
If I only used InterviewDate then there could only be one InterviewDate entry of say '<?=valid_date(CurrentDate(),'USA')?>'.
There could be more than one interview per day.
The same is true of InterviewTime.
<br>I will denote that by putting an asterisk (<b>*</b>) in after of the primary key field(s) for each table:
<table>
<tr><th>Table</th><th>Fields</th></tr>
<tr><th>client</th><td>ClientNo*, ClientName, ClientPhoneNumber</td></tr>
<tr><th>staff</th><td>StaffNo*, StaffName, StaffWorkHomePhoneNumbers</td></tr>
<tr><th>interview</th><td>InterviewId*, InterviewDate, InterviewTime</td></tr>
<tr><th>room</th><td>RoomNo*</td></tr>
</table>
</li>
<li>
<b class="nowrap">Eliminate repeating groups of data.</b> This is the <a href="1NF.txt">1NF</a> rule.
There can be no:
<ul>
<li>Lists of data in a field (e.g. The field <b>Phone</b> with data like 123-456-7890, 234-567-8901).</li>
<li>Repeating fields in a table (e.g. Fields named: <b>Phone1</b>, <b>Phone2</b>, <b>Phone3</b>).</li>
<li>Repeating tables (e.g. Tables named: <b>HomePhone</b>, <b>MobilePhone</b>).</li>
</ul>
So the phone numbers in either case must be placed in a new table.
There StaffWorkHomePhoneNumbers contains a list of data so this must be split up.
This is done by modifying the <b>staff</b> table and adding a <b>staffphone</b> table.
<table>
<tr><th>client</th><td>ClientNo*, ClientName, ClientPhoneNumber</td></tr>
<tr><th>staff</th><td>StaffNo*, StaffName</td></tr>
<tr><th>staffphone</th><td>StaffNo*@, PhoneNumber*, PhoneType</td></tr>
<tr><th>interview</th><td>InterviewId*, InterviewDate, InterviewTime</td></tr>
<tr><th>room</th><td>RoomNo*</td></tr>
</table>
Note that the <b>StaffNo</b> field in the <b>staffphone</b> table is a foreign key relationship to the <b>StaffNo</b> field in the <b>staff</b> table.
This is denoted by putting an at sign (<b>@</b>) after of the field. The primary key for the <b>staffphone</b> table is a composite key of the <b>StaffNo</b> and <b>PhoneNumber</b> fields.
This ensures that any staff phone number can only be entered once for each staff person.
</li>
<li>
<b class="nowrap">Remove the possibility of INSERT, UPDATE, or DELETE anomolies.</b>
<br>INSERT and UPDATE anomolies can cause data in the database to lose integrity because the data entered is incorrect.
<br>DELETE anomolies can cause data to be lost completly to the database that should logically be kept.
<br>These types of anomolies are usually fixed in the first step because data is put in multple tables with the data in each table only related to the entity.
That is the case here so the tables above need no modification.
</li>
<li>
<b class="nowrap">Relate the entities back together.</b>
<br>An interview consisted of a client meeting a staff person in a room at a specifid date and time.
<br>The linking table will have foreign keys to the other tables and I will denote that by putting an at sign (<b>@</b>) in front of the field name.
<br>I also need to identify the primary key and will denote that by putting an asterisk (<b>*</b>) in front of the primary key field(s) as before.
<br>So now I add a linking table <b>meeting</b>:
<table>
<tr><th>Table</th><th>Fields</th></tr>
<tr><th>client</th><td>ClientNo</td></tr>
<tr><th>staff</th><td>StaffNo*, StaffName</td></tr>
<tr><th>staffphone</th><td>StaffNo*@, PhoneNumber*, PhoneType</td></tr>
<tr><th>interview</th><td>InterviewId*, InterviewDate, InterviewTime</td></tr>
<tr><th>room</th><td>RoomNo</td></tr>
<tr><th>meeting</th><td>ClientNo*@, InterviewId*@, StaffNo*@, RoomNo*@</td></tr>
</table>
</li>
<li>
<b class="nowrap">Finally, make sure the tables do not allow bad data.</b> Everything follows 1NF, 2NF, and 3NF and business rules.
<br>The way it is now, I could double book a client to multiple interviews at the same time, just with a different staff person or a different room.
This should not be allowed. The same could be done for rooms or staff.
<br>So I need three more linking tables <b>client_meeting</b>, <b>room_meeting</b>, and <b>staff_meeting</b> with an added unique field and a changed <b>meeting</b> table.
<table>
<tr><th>Table</th><th>Fields</th></tr>
<tr><th>client</th><td>ClientNo*</td></tr>
<tr><th>staff</th><td>StaffNo*</td></tr>
<tr><th>interview</th><td>InterviewId*, InterviewDate*, InterviewTime*</td></tr>
<tr><th>room</th><td>RoomNo*</td></tr>
<tr><th>client_meeting</th><td>ClientNo*@, InterviewId*@, clientmeetingId^</td></tr>
<tr><th>room_meeting</th><td>RoomNo*@, InterviewId*@, roommeetingId^</td></tr>
<tr><th>staff_meeting</th><td>StaffNo*@, InterviewId*@, staffmeetingId^</td></tr>
<tr><th>meeting</th><td>clientmeetingId*@, roommeetingId*@, staffmeetingId*@</td></tr>
</table>
Note that the clientmeetingId, roommeetingId, and staffmeetingId fields are UNIQUE.
I will denote that by putting an caret (<b>^</b>) after any unique field(s) in each table.
<br>Now, the client can only be booked for one date/time, and the same is true of the rooms and staff.
</li>
</ol>
</p>
<?
include('common/sectionFooter.phpinc');
/** /
$sectionTitle = 'Normiization Examples';
include('common/sectionHeader.phpinc');
?>
<a href="../Examples/Normilization/Normilization example 1.txt">Normilization example 1</a>
<a href="../Examples/Normilization/Normilization example 2.txt">Normilization example 2</a>
<a href="../Examples/Normilization/Normilization example 3.txt">Normilization example 3</a>
<a href="../Examples/Normilization/Normilization example 4.txt">Normilization example 4</a>
<a href="../Examples/Normilization/Normilization example 5.txt">Normilization example 5</a>
<?
include('common/sectionFooter.phpinc');
/**/
include('common/pageFooter.phpinc');
?>