UQ Students should read the Disclaimer & Warning
Note: This page dates from 2005, and is kept for historical purposes.
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>INFS1200 - Assignment One part one</title>
<style type="text/css">
<!--
body {
background: url(_img/DSC04989.jpg) fixed center center;
}
.underline {
text-decoration: underline;
}
th, td, textarea {
border: 1px solid #000000;
padding: 0 1ex;
background: transparent;
overflow: hidden;
}
table {
border: none;
}
-->
</style>
</head>
<body>
<h1>INFS1200 – Assignment 1, part 1</h1>
<p><a href="#results">Results</a> | <a href="#sample">Sample Solution</a> | <a href="#submission">My
Submission </a></p>
<h2 id="results">Results</h2>
<p><img src="_img/INFS1200-ass1p1-marks-dist.png" alt="Marks distribution" width="533" height="296" /></p>
<p><img src="_img/INFS1200-ass1p1-marks-hist.png" alt="Marks histogram" width="509" height="322" />
</p>
<table border="1" title="Statistics">
<tr>
<td>My Result</td>
<td>6.35 out of 8</td>
</tr>
<tr>
<td>Mean</td>
<td>5.654198473</td>
</tr>
<tr>
<td>Standard Error</td>
<td>0.083451297</td>
</tr>
<tr>
<td>Median</td>
<td>5.6</td>
</tr>
<tr>
<td>Mode</td>
<td>6.85</td>
</tr>
<tr>
<td>Standard Deviation</td>
<td>1.350777244</td>
</tr>
<tr>
<td>Sample Variance</td>
<td>1.824599164</td>
</tr>
<tr>
<td>Kurtosis</td>
<td>0.575120112</td>
</tr>
<tr>
<td>Skewness</td>
<td>-0.625109979</td>
</tr>
<tr>
<td>Range</td>
<td>7</td>
</tr>
<tr>
<td>Minimum</td>
<td>1</td>
</tr>
<tr>
<td>Maximum</td>
<td>8</td>
</tr>
<tr>
<td>Count</td>
<td>262</td>
</tr>
<tr>
<td>Confidence Level (95.0%)</td>
<td>0.164323315</td>
</tr>
</table>
<hr />
<h2 id="sample">Sample Solution</h2>
<p>Note that the solution below is “a” possible design of the given universe of
discourse. You may have made different design choices in your assignment which
may be correct too. </p>
<p><img src="_img/INFS1200-ass1p1-sol-ER.png" alt="Possible solution ER diagram" width="718" height="741" /></p>
<p>Steps:<br />
<a href="#one">1 – Strong Entities</a> | <a href="#two">2 – Weak Entities</a>
| <a href="#three">3 2- 1:1 Relationships</a> | <a href="#four">4 –1:N Relationships</a>
| <a href="#five">5 – M:N Relationships</a> | <a href="#six">6 – Multivalued
Attributes</a> | <a href="#seven">7 – n-ary Relationships</a> | <a href="#eight">8
– Specialisation</a></p>
<h3 id="one">STEP 1 – Strong Entities</h3>
<p><em>The following tables are created:</em></p>
<p>Company (<span class="underline">ABN</span>, Email, URL, Name, Address)</p>
<p>Bus (<span class="underline">Reg#</span>, Weight, Type, Model, Make, Seats,
Date)</p>
<p>Route (<span class="underline">Route#</span>)</p>
<p>City (<span class="underline">Postcode</span>, Name, State)</p>
<h3 id="two">STEP 2 – Weak Entities</h3>
<p><em>The following tables are created:</em></p>
<p>Branch (<span class="underline">Int. Number, ABN</span>, Mgr. Name, Address,
Phone, Fax, Email)</p>
<p>Stop (<span class="underline">St.</span><span class="underline"> no, Postcode</span>)</p>
<p>Trip (<span class="underline">Route#, Reg#, Date, </span>Driver, Assistant)</p>
<h3 id="three">STEP 3 – 1:1 Relationships</h3>
<p><em>none</em></p>
<h3 id="four">STEP 4 – 1:N Relationships</h3>
<p><em>Foreign keys are added to existing tables:</em></p>
<p>Company (<span class="underline">ABN</span>, Email, URL, Name, Address, Postcode)</p>
<p>Bus (<span class="underline">Reg#</span>, Weight, Type, Model, Make, Seats,
Date, ABN)</p>
<p>Trip (<span class="underline">Route#, Reg#, Date, </span>Driver, Assistant)</p>
<p>Route (<span class="underline">Route#</span>, Start_stop_no., Start_postcode,
End_stop_no, End_postcode)</p>
<p>Branch (<span class="underline">Int. Number, ABN</span>, Mgr. Name, Address,
Phone, Fax, Email, Postcode)</p>
<h3 id="five">STEP 5 – M:N Relationships</h3>
<p><em>The following tables are created:</em></p>
<p>Approved_For (<span class="underline">ABN, Route#</span>)</p>
<p>Intermediate_stops (<span class="underline">Route#, </span><span class="underline">St.</span><span class="underline">
no, Postcode</span>)</p>
<h3 id="six">STEP 6 – Multivalued Attributes</h3>
<p><em>The following tables are created:</em></p>
<p>Company_phone (<span class="underline">ABN, Phone</span>)</p>
<p>Company_fax (<span class="underline">ABN, Fax</span>)</p>
<h3 id="seven">STEP 7 – n-ary Relationships</h3>
<p><em>none</em></p>
<h3 id="eight">STEP 8 – Specialisation</h3>
<p><em>none</em></p>
<h3>Final Schema</h3>
<p><img src="_img/INFS1200-ass1p1-sol-schema.png" alt="Possible solution mapped schema" width="677" height="707" /><br />
Sample solution may be Copyright © 2003 The University of Queensland, Australia<br />
Sourced from http://www.itee.uq.edu.au/~infs1200//Assignments/Assignment1-Part1-Solution.doc</p>
<hr />
<h2 id="submission">My Submission</h2>
<p><em>Give specification assumptions (if any), or any special notes/comments
that you feel would help us better assess your work</em></p>
<table border="1">
<tr>
<td> <p>ER diagram and final relational schema are presented as images.
It was found to be too unreliable to use Word drawing tools, as
changing font size and various other things would destroy the
alignment of elements, so an image was used instead. It was not
feasible to use a separate line for each foreign key in the relational
mapping, so lines have been combined where possible.</p>
<p>A few assumptions were made:</p>
<ul>
<li>Buses are operated only by one company.</li>
<li>Drivers and assistants work for bus companies.</li>
<li>Phone and fax numbers need to be identified as belonging to
a department, person, etc. Dept attribute was added to phone
and fax, which represents who or what the phone or fax will
connect to. This prevents a random list of phone numbers,
which would not be much use to anyone – having to ring each
one until they connected to sales department, for example.</li>
<li>Only one driver and assistant are assigned to one bus per
date. Date could be changed to a begin date/time and end date/time
if more dynamic assignment was needed.</li>
<li>Route numbers may not be unique across different companies.</li>
<li>State can be inferred from the postcode.</li>
<li>Route beginning and ending is a single attribute. If necessary,
this could be divided into postcode, city etc.</li>
<li>Only one email and website exists per company and per branch.
This is because multiple emails and websites were not specified
in the assignment specification, but multiple phone and fax
numbers were.</li>
<li>Assistant may not be a bus driver, and bus drive may not be
an assistant. If an assistant is always capable of being a
bus driver, and vice versa, assistant could be merged with
driver.</li>
<li>Street is an atomic value that may contain street numbers
and various address information.</li>
<li>Driver and assistant both have unique ID’s that are unique
outside their company, possibly a social security ID or similar.</li>
<li>Phones and faxes may not be unique across companies (in the
case of internal phones etc). Also, that retrieval of a phone
or fax number would always be joined with the company that
uses the number; this is why the unique company ABN has been
included with each phone and fax number – which should improve
efficiency.</li>
</ul>
<p>I am not a great admirer of inserting large images into Word documents,
as they sometimes do not appear as they should. Should the images
in this document are unreadable or otherwise do not work, I have
provided the copies in the zip file.</p>
<p>Names for entities, relationships and attributes are not necessarily
the same on the ER diagram and the relational schema. The min
max notation has been used, with double lines to indicate weak
entities and their identifying relationship. Total participation
is shown by the min max notation and not by double lines, although
the lines between a weak entity and its identifying relationship
have been shown as double lines. Multi-valued attributes “phone”
and “fax” also have double lines. Primary keys have been marked
with an underline, and partial keys with a dashed underline. These
are not necessarily all possible keys, but only those chosen to
identify their entities. Attribute “city” was not included as
a key for entity “Stops” in the ER diagram, but while designing
the relational schema, it was found to be necessary to use it
as part of the key. The ER diagram has not been changed to show
“city” as a key, as I did not feel it necessary to use “city”
as a key when designing the ER diagram.</p></td>
</tr>
</table>
<p> </p>
<table border="1">
<tr>
<td colspan="3"> Official Use Only</td>
</tr>
<tr>
<td> ER (/5) </td>
<td> </td>
<td rowspan="3">
</td>
</tr>
<tr>
<td> Mapping (/3) </td>
<td> </td>
</tr>
<tr>
<td> TOTAL (/8)</td>
<td>6.35 </td>
</tr>
</table>
<p> </p>
<h1>ER Diagram</h1>
<p><img src="_img/INFS1200-ass1p1-ER-diagram.png" alt="Entity relationionship diagram" width="1071" height="779" /></p>
<p> </p>
<h1>Mapping</h1>
<p>Steps taken:</p>
<p>Initial construction and subsequent working was done in pencil and paper, and
as I don’t have a scanner, I cannot submit it.</p>
<ul>
<li>All regular entities were mapped to relations. All their attributes were
added. In the case of composite attributes such as “address” in “stops”,
only the simple attributes of the complex attributes were included.</li>
<li>Weak entities were then added, including as foreign key(s) the primary
key(s) from the owner entities. All their attributes were added in the
same way as for regular entities.</li>
<li>Binary relations were then mapped. Primary keys from one relation were
included as foreign keys in the other relation – in cases where one relation
had total participation, the primary key of the other relation was included
in that relation as a foreign key. The attributes on the relations, “date”
on relations “drives” and “assists” were then included into the same relation
that has the foreign key – in this case “driver” and “assistant”.</li>
<li>Primary keys were then checked to ensure they were valid and foreign keys
incorporated into the primary key in some cases, especially on relation
“stops”, where it was necessary to identify stops for specific companies,
by city and route number, then by stop number. Different routes could
have different numbers for the same stops, and different companies could
have the same numbers for different stops. This was not always clear from
the ER diagram, and city was not included as a key on the ER diagram.</li>
<li>Arrows were then added showing foreign keys. This was perhaps the hardest
part of the assignment. I have since realised that it is not necessary
to add each arrow on its own canvas, and then send that to the back...
which would have made it feasible to use Word. However, before I realised
that, I drew this, with arrows joining onto other lines to keep the image
from becoming too cluttered. An arch is shown when a line passes over
another without joining, and a dot is shown when the line joins another.
Arrows are placed at the end of the line pointing to the primary key that
was used as a foreign key.</li>
</ul>
<p><img src="_img/INFS1200-ass1p1-mapping.png" alt="Relational schema" width="805" height="593" /></p>
<p>End.</p>
<hr />
<p>The following relational diagram was also used to assist the design of the
above, but was not submitted.</p>
<p><img src="_img/INFS1200-ass1p1-premap.png" alt="Entity relational diagram" width="1070" height="609" /><br />
All work © Copyright 2003 Ned Martin</p>
<p>07-Sep-2003<br />
18-Sep-2003 - Updated with results</p>
</body>
</html>