Approach Taken To Normalize Mot Data

Published Date: 02 Nov 2017

MOT Assignment

AC52039

Gareth Agius

This is a brief document highlighting the approach taken to normalize MOT data, and construct the necessary ERD diagrams.

Table of Contents

Introduction

SCAN WITH VIPER PLAGERISM DETECTION

The document below presents two approaches to normalising the data; the differences between the two approaches taken will be explained in greater detail in the following sections. A different assumption with regards to the data in one approach leads to an entirely different structure. The second approach builds on the already normalised structure resulting in section 3 with the aim of achieving an architecture that is more scalable, and efficient. In both cases a purely bottom up methodology is taken and the two approaches follow the normalisation rules to the letter, without applying amendments to the already normalised structure, based on a logical understanding of the real world. In the final sections, and entity relationship diagram is presented which is not achievable by working from an un-normalised flat table representation of the data, but rather by identifying the entities in the real world and modelling them accordingly.

Un-normalisation

The first step should be to transform the report data into an un-normalised flat table. The process involves selecting the columns, identifying calculated values to be ignored, identifying a unique column/s (key) for the table and inserting sample data. (SQA, 2007)

Mod

Test

ID

Testing

Categ

Status

Test

Year

Vehicle

Class

Make

Model

YearOf

First

Use

Test

Passes

Test

Failures

Body

And

Structure

Brakes

…..

Tyres

Motor

Tricycles

Quads

Driving

Controls

Items

Not

Tested

1

Normal

Complete

2007

4

AC

ACE

1995

9

3

1

1

1

Normal

Complete

2007

4

AC

ACE

1996

4

2

1

1

Normal

Complete

2007

4

AC

ACE

1998

2

1

1

3

Normal

Complete

2007

4

AC

ME 3000

1980

1

1

2

3

Normal

Complete

2007

4

AC

ME 3000

1981

1

1

3

Normal

Complete

2007

4

AC

ME 3000

1982

1

2

3

2

3

Normal

Complete

2007

4

AC

ME 3000

1986

2

1

The flat table below is a representation of the report data:

Table : Un-normalised report data.

The table above is a sample of the entire flat table. Some of the columns where also left out due to space restrictions. From the above it is clear that all the report data is represented in the table, except for the calculated values which include the totals for the columns from "TestPasses" onwards as well as the "FailureRate" column and the corresponding "TotalFailureRate" column.

The key columns that were selected for the table are entitled "ModTestID" (model test id) and "YearOfFirstUse". The "ModTestId" column comes from the incrementing numerical value on the right hand side of the report. In the report a new value is used to represent the tests for a particular vehicle model with a variety of different year of first use values. The "YearOfFirstUse" represents the year that the vehicle was first used. Together these two columns uniquely identify a row in the un-normalised table.

Assumptions Taken

The following assumptions where taken to be able to build the flat table in the section above, as well as to facilitate certain decisions taken when normalising the data:

The Failure rate can be computed from the sum of the number of "Test Passes" and "Test Failures" divided by the number of "Test Failures".

The final 3 recorded values namely "Motor Tricycles and Quadricycles", "Driving Controls" and "Items not Tested" are all required for class 4 vehicles for the "Normal" testing category.

It is assumed to be known by the user that a vehicle with "Year of First Use" set to "1971" represents a kit car.

From the data that we have it is impossible to compute the values of "Test Passes" and "Test Failures" from any other column values.

Placing a value of "0" in the case that there is no value for "Test Failures" and "Test Passes" does not alter the business interpretation of the results in any way. However the same does not apply for the remainder of the test columns. Thus placing a value of "0" in the column for "Brakes" is not the same as having no value, as this may lead to the interpretation that a test was carried out when in reality it was not.

The numerical ID value used in the report (to represent the tests for a particular make and mode) does not get reset from one year to the next and thus continues increasing in value when the tests are repeated in subsequent years.

The tests can be repeated from year to year thus requiring the column "TestYear" column to represent the year when the particular tests were carried out.

The tests recorded in the report belong to a particular category of tests, the tests can be performed in a different manner in other types of test categories. Thus the "TestCategory" column is required to provide further information with regards to the tests performed.

The Status of the tests is also required, however it was assumed that the tests for a given test year for a model is treated as a whole unit. Thus in the test done in 2008 one cannot have say model ‘X’ with 1996 as year of first use as status "Complete" and the same model with 1999 as year of first use with status "InProgress".

The statement "Tests with one or more Failure, PRS, or Advisory type Defects in Category" can be ignored, as the appropriate/neccessary tests (results) can be queried from the stored data.

The test results for a particular vehicle make and model for a vehicle with the same "YearOfFirstUse" are combined with one another, resulting in one set of data for a make with the same year of first use value, eliminating the possibility of having two rows with the same "ModTestID" value and the same "YearOfFirstUse" value for a given test year. This assumption allowed us to guarantee that the primary key declared in the section above uniquely represents a row.

Anomalies found within the data.

When going through the data, a number of irregularities where observed:

The following is not an anomaly however it is a characteristic worth mentioning now, in the data there exist models with the same name but with a different vehicle make. An example of this is the "Aeon Cobra" and the "Ace Cobra"

An anomaly that was observed is the fact that for the model "Audi 80" one of the test entries has a year of first use value set at 1883, when according to (Audi, n.d.) the car started to be produced as early as 1965. The same holds for the "Ford Fiesta" and "Vauxhall Astra" model, both having year of first use entries at 1900 and 1901 respectively when both cars where manufactured much later. The Ford Fiesta was first manufacture in 1976 (Ford, n.d.) and 1979 was the first year the Vauxhall Astra was made, the first car every being made by Vauxhall being 1903 (Vauxhall, n.d.).

For test entry with id 304 for model "General Motors, Chevrolet", the totals for the tests do not appear directly under the individual tests but rather they occupy a separate entry with a different id altogether (305), treated as a model alone.

For the purpose of this exercise this exercise the last two anomalies listed above will be ignored,.

Normalisation Approach 1

As was stated in the introduction section, this approach bases the normalisation on the data available in the report, without taking in to consideration the presence of null values as well as the possible requirement of scalability in the tests performed. As a result another assumption should be taken to ensure that the resulting design is still logically sound with regards to the business requirements:

The assumption to be taken is that the tests displayed in the report are the only tests that are available, implying that for other vehicle classes or test categories no other test (other than the observed) can be carried out on a particular vehicle. If this assumption fails in practice, then the schema would need to be modified, either via the insertion of additional columns, or possibly entire tables for specific test categories would need to be added.

First Normal Form

For a table to be considered as being in the first normal form, it is required to satisfy a number of conditions. The first is that it has a primary key to uniquely identify each row (Whitehorn & Marklyn, 2007); the composite primary key was already identified in section 2 and so this requirement is satisfied. The second condition is that it contains no repeating groups or in other words it "doesn’t have multiple columns holding the same kind of data" (Whitehorn & Marklyn, 2007). In this approach this condition is deemed to be satisfied, some knowledge of what the columns are displaying is being used in this case, although each column is displaying the results of a test, the tests are entirely different and in turn so is the data. In this approach the test columns are considered to be attributes describing the particular model test for a given year. For us to be able to make such a decision the assumption taken in the section above is required, as will be seen by the result. The contents of each column must also not hold multiple values, thus each cell must hold atomic data (Whitehorn & Marklyn, 2007). It is clear that in the table this holds for every column.

Thus in this case the table defined in section 2 already satisfies the first normal form requirements.

Second Normal Form

For a table to be in the second normal form, it is required that every non-key column is fully dependent on the entire primary key. So in the event of a composite key, another column (non-key) cannot be dependent on only part of the composite key (Whitehorn & Marklyn, 2007).

Analysing the table in section 2 it is clear that the columns: "TestCategory", "Status", "VehilceClass" , "TestYear", "Make", "Model" are all entirely dependent on the key "ModTestID". All the other columns however are clearly dependent on the entire primary key. Thus these columns are moved to another table "ModelTest" along with the key "ModTestID" as shown below:

ModTestID

Testing

Category

Status

TestYear

VehicleClass

Make

Model

1

Normal

Complete

2007

4

AC

ACE

2

Normal

Complete

2007

4

AC

ME 3000

3

Normal

Complete

2007

4

ALFA

147

4

Normal

Complete

2007

4

ALFA

75

5

Basic

Complete

2007

4

AC

ACE

6

Basic

Complete

2007

4

AC

ME 3000

8

Basic

In-Progress

2007

4

ALFA

147

9

Basic

Complete

2007

4

ALFA

75

10

Normal

Complete

2008

4

AC

ACE

11

Normal

Complete

2008

4

AC

ME 3000

Table : "ModelTest" table, 1NF (V1).

Note that although in the data provided only the testing category of "Normal" was provided, in the above table a second category of "Basic" was added based upon the assumptions taken in section 2.

After extracting the table above the original table named "ModelTestResult" is as follows:

Mod

Test

ID

Year

OfFirst

Use

Test

Passes

Test

Failures

Body

And

Structure

Brakes

Drivers

View

OfRoad

…

Seat

Belts

Steering

Suspension

Tyres

Motor

Tricycles

Quads

Driving

Controls

Items

Not

Tested

1

1995

9

3

1

1

1

1

1996

4

2

1

1

1998

2

1

1

1

1

2

1980

1

1

2

2

1981

1

1

1

1

2

1982

1

2

3

1

1

2

2

1986

2

1

1

3

1999

3

2

2

1

1

3

2000

25

10

3

2

6

Table : "ModelTestResult" table, 1NF.In the above table some central columns where left out due to space restrictions.

Examining the two new tables, it is clear that both are already in the first normal form. Table "ModelTest" is guaranteed to be in the second normal form as it only has one column making up the primary key, also table "ModelTestResult" has no non-key column dependent on only part of the primary key and so is also in the second normal form. This can be done by confirming that no non-key column is fully dependent on either "ModTestID" alone or "YearOfFirstUse" alone.

By stating that the columns are not fully dependent on the column "ModTestID" this is implying that given only the value of "ModTestID" you would not be able to guarantee the value of any of the values of the other non-key columns.

Third Normal Form

To guarantee that a table is in the normal form, there must be no non-key attribute that is dependent on another non-key attribute. Thus if a table only has one non-key attribute such as then it can be ignored (Whitehorn & Marklyn, 2007).

In the table "ModelTestResult" there is no non-key column value that is fully dependent on any other non-key attribute. In the table "ModelTest" at first glance it appears that the column "Model" is dependent on the column "Make"; however there exist cases in the data where different makes have vehicles with the same models, as is the case with "AC" and "COBRA" and "AEON" and "COBRA". Thus the column model cannot be dependent on the make.

Similarly it also seems that the Vehicle class is also fully dependent on the "Model" however since there exists the possibility that there are multiple makes having the same model name, there is also the possibility that there are models with the same name however having a different vehicle class, as they belong to a different make. It is clear however that vehicle class is dependent on the combination of both Make and Model, and so can be extracted into a table as shown below. In this case the columns "Make" and "Model" together are not a candidate key, as they require all the other non-key columns to uniquely identify the row and so a dependency that violates the third normal form exists (Mahajan, 2006). The remainder of the columns are all attributes of the model test and so are valid non-key columns. In section 5 the normalisation rules will be put on the side and these columns will be treated differently.

ModelID

Make

Model

VehicleClass

1

AC

ACE

4

2

AC

ME 3000

4

3

AC

COBRA

4

4

AEON

COBRA

4

5

ALFA

147

4

6

ALFA

75

4

Table : "Model" table, 3NF.

As a result the table "ModelTests" can be updated as follows:

ModTestID

Testing

Category

Status

TestYear

ModelID

1

Normal

Complete

2007

1

2

Normal

Complete

2007

2

3

Normal

Complete

2007

5

4

Normal

Complete

2007

6

5

Basic

Complete

2007

1

6

Basic

Complete

2007

2

7

Basic

In-Progress

2007

5

8

Basic

Complete

2007

7

9

Normal

Complete

2008

1

10

Normal

Complete

2008

2

Table : "ModelTestResults" table, 3NF.

All the resulting tables are now confirmed to be in the third normal form and an entity relationship diagram can be built based upon the above tables.

Resulting Entity Relationship Diagram

Figure 3.: ERD Approach 1.

From the above ERD it is clear that there exist a number of relationships:

ModelTestResults_ModelTest: The "ModelTest" table is the parent table and their can exist one or many entries in the "ModelTestResults" table with the same value of "ModelTestID". Therefore there cannot exist a primary key value "ModelTestID" in the table "ModelTest" without there existing at least one entry in the table "ModelTestResult" with an equivalent value of "ModelTestID".

ModelTest_Model: "Model" is the parent table and there can exist zero or many entries in the "ModelTest" table with a value of "ModelID" equal to a primary key value in the "Model" table. This implies that there can be an entry in the "Model" table however it is not necessary for there to exist a corresponding entry in the "ModelTest" table with the same "ModelID" value.

The Access Database

Apart from this document there should also be another file named "MOT_DB_Approach1_Final.accdb" in this file one can see the database representation of the above ERD. Apart from the tables and the relationships there are also 2 queries, that are used to show how the computed values can be obtained from the base tables:

"TestTotals": This query computes the total values for all the tests carried out on a particular vehicle model, for a particular test year and test category, by summing the values of the individual tests for the models with different "YearOfFirstUse" values. The value of "AverageFailureRate" is computed by making use of the resulting value of "TotalTestFailures" and "TotalTestPasses".

"FailureRate": This simple query is used to show how the failure rate value can be computed for each model’s test for a particular value of "YearOfFirstUse" by making use of the values of "TestFailures" and "TestPasses".

With regards to the tables themselves, note that every column in the "ModelTestResult" table apart from the key columns, are nullable columns. The cells can’t be populated with zero values due to the assumption taken in section 2, all other columns in the tables do not allow null values.

In the table "ModelTest" the following columns have a uniqueness constrain applied to them: "TestingCategory", "Status", "TestYear", "ModelID", thus disallowing tests of the same category to be carried out on the same vehicle model on the same year. This is an assumption about the business process based on the data presented, and can be removed if necessary.

Similarly to the above there are uniqueness constraints on the combined columns "Make", "model" and "VehicleClass", thereby disallowing any two vehicles having the same make model and vehicle class. It is still possible for example to have a vehicle with the same make and model but a different vehicle class, or a vehicle with the same model different makes and the same vehicle class.

In the "ModelTestResults" table no such uniqueness constraints on the "ModelTestID" and "YearOfFirstUse" are required since both these columns are set as primary keys.

In the ERD a relationship is described whereby if a entity exists in table "ModelTest" then a corresponding entry in the "ModelTestResult" table needs to exist, this was not implemented in the access database, due to the fact that in order to input the data, transactions would need to be used.

Normalisation Approach 2

In this approach the normalised tables obtained in section 3 will be used as the starting point.

In the first approach we took the assumption that there are no further tests that can be performed on the vehicle, which doesn’t really provide much flexibility in the way of adding or removing tests. To remove a test, the entire column would have to be removed (deleting historical data), and to add a test a new column would need to be added to the "ModelTestResults" table, which may not necessarily be applicable to all the test categories or vehicle classes.

Also note that the resulting "ModelTestResults" table is a very sparse table, containing a lot of empty (Null) cells, according to the assumption in section 2.1 these null values cannot be replaced by zeros. In the interests of good design such tables should be avoided (Whitehorn & Marklyn, 2007).

Note that in the MOT data provided the columns of "TestFailures" and "TestPasses" also had empty cells, however the assumption taken in section 2 which states that zero values will not affect the business interpretation of the results still holds in this section also. Thus these columns can be set not to allow nulls.

In order to satisfy the above the "ModelTestResults" table should be subdivided into a number of tables as follows.

First Normal Form Changes

In the first approach, it was stated that the individual test columns from "Breaks" till "ItemsNotTested" do not represent repeated data. I do not believe one should be given fault for taking such a decision due to the fact that having a basic understanding of the data, these columns are not in fact divulging the same data, and one column represents the results for one test type whereas the other represents the results for another type of test entirely. However if one where to blindly follow the rules of normalisation without overthinking the meaning of this data, in order to bring the table "ModelTestResults" into the first normal form then the columns for each test in need to be considered as repeating groups and are extracted from the table as shown below. In doing so, the requirements described in section 5 are being met.

ModTestID

YearOfFirstUse

TestTypeID

TestPasses

TestFailures

TestCount

1

1995

3

2

2

1

1

1995

6

2

2

1

1

1995

8

2

2

1

1

1995

10

2

2

1

1

1995

13

2

2

2

1

1996

3

3

4

2

1

1996

7

3

4

4

1

1996

13

3

4

2

2

1980

1

3

3

1

2

1980

3

3

3

2

2

1980

7

3

3

3

2

1980

11

3

3

4

2

1980

13

3

3

5

2

1980

14

3

3

2

Table : "ModelTestResults" table, Approach 2.

TestTypeID

TestDescription

1

BodyAndStructure

2

Brakes

3

DriversViewOfRoad

4

FuelAndExhaust

5

LightingAndSignalling

6

RegPlatesAndVIN

7

RoadWheels

8

SeatBelts

9

Steering

10

Suspension

11

Tyres

12

MotorTricyclesQuads

13

DrivingControls

14

ItemsNotTested

Table : "TestType" table, Approach 2.

Table "ModelTestResults" has been modified to include 2 columns in replacement of the previous 14 individual test columns. Now only if there is a value for a particular test, will a new row be inserted into the table, where the value of "TestTypeID" refers to the particular test that was carried out, and "TestValue" refers to the result of that test. In this manner empty cells have been avoided, and also to add a new test type, an entry only needs to be added to the "TestType" table, and used accordingly in the "ModelTestResults" table.

Also note that the column "TestTypeID" was set as a part of the composite primary key in the "ModelTestResult" table.

Second Normal Form

At this stage we have the tables "TestType", "TestingCategory" and "ModelTest", which all have a single key column, and thus are already in the second normal form.

However in the table "ModelTestResults" it is clear that the values of "TestFailures" and TestPasses" are dependent on the values of "ModTestID" and "YearOfFirstUse". As a result these values can be extracted from the table and placed into a separate table as follows:

ModYearTestID

ModTestID

YearOfFirstUse

TestPasses

TestFailures

1

1

1995

2

2

2

1

1996

3

4

3

2

1980

0

5

4

2

1981

3

3

Table : "ModelYearTest" table, Approach 2, with surrogate.

ModYearTestID

TestTypeID

TestCount

1

3

1

1

6

1

1

8

1

1

10

1

1

13

2

2

3

2

2

7

4

2

13

2

3

1

1

3

3

2

3

7

3

3

11

4

3

13

5

3

14

2

Table : "ModelYearTestResults" table, Approach 2, with surrogate.

Thirds Normal Form

The tables "TestType", "TestingCategory" and "ModelYearTestResults" all have only one non-key attribute and thus can be ignored.

The tables "ModelYearTest" and "ModelTest" have no columns, that are dependent on any other non-key columns and so are both in the third normal form.

Entity Relationship Diagram for Approach 2

Figure 4.: ERD for Approach 2.

From the above ERD the relationships between the tables are clear:

TestType_ModelYearTestResult: "TestType" is the parent table and there can be zero or more entries in the "ModelYearTestResult" table with a "TestTypeID" equal to a primary key in the "TestType" table. This implies that new test types can be inserted into the "TestType" table without them having to be used.

ModelTest_Model: "Model" is the parent table and there can exist zero or many entries in the "ModelTest" table with a value of "ModelID" equal to a primary key value in the "Model" table. This implies that there can be an entry in the "Model" table however it is not necessary for there to exist a corresponding entry in the "ModelTest" table with the same "ModelID" value.

ModelYearTestResults_ModelYearTest: The "ModelYearTest" table is the parent table and their can exist one or many entries in the "ModelYearTestResults" table with the same value of "ModelYearTestID". Therefore there cannot exist a primary key value "ModelYearTestID" in the table "ModelYearTest" without there existing at least one entry in the table "ModelYearTestResult" with an equivalent value of "ModelYearTestID".

ModelYearTest_ModelTest: The "ModelTest" table is the parent table and their can exist one or many entries in the "ModelYearTest" table with the same value of "ModelTestID". Therefore there cannot exist a primary key value "ModelTestID" in the table "ModelTest" without there existing at least one entry in the table "ModelYearTest" with an equivalent value of "ModelTestID".

Database for Approach 2

Apart from this document there should also be another file named "MOT_DB_Approach2_Final.accdb" in this file one can see the database representation of the above ERD. Apart from the tables and the relationships there are also the same two queries as described in section 3.5. To show that even though the structure of the database differs, the same type of reports can still be generated. In this database the "TestTotals" query takes a while to be generated due to the fact that over 14 joins are needed to build a query table that is identical to that obtainable in the previous database, with individual columns for each total test value. In practice this would be avoided and the results would not be displayed in such a manner.

With regards to the tables themselves, in this database unlike in the previous design, there are no nullable columns, resulting in the maximum space saving.

In this database, the same uniqueness constraints where placed on the tables as was done in the first approach.

Changes to the Final Results

Section 6, the concluding remarks discussing which of the two normalisation approaches is best suited to this situation, however this section, discusses how changes can be made to the second normalisation approach, that go above and beyond the normalisation steps.

If one had to examine the tables resulting from the second normalisation approach it is clear that all tables are in the third normal form, and so in reality should not be broken down any further. However if one had to start modelling the system from a top down approach, there are a number of real world entities that are not represented in our final database. Having separate tables for the entity "Model", "Make" is one of the changes that can be made.

The tables below show how the "Model" and "Make" entities can be represented as seperate tables in our example, as well as how the original "Model" table would be transformed into the "ModelDetails" table:

ModelDetailsID

MakeID

ModelID

VehicleClass

1

1

1

4

2

1

2

4

3

1

3

4

4

2

3

4

5

3

4

4

6

3

5

4

Table : "ModelDetails" table.

MakeID

Make

1

AC

2

AEON

3

ALFA

Table : "Make" table.

ModelID

Model

1

ACE

2

ME 3000

3

COBRA

4

147

5

75

Table : "Model" table.

Also note that in the table "ModelTest" the columns "TestingCategory", "Status" and "TestYear" although all are attributes of a particular test, are still repeating needlessly and taking up extra space. These columns can be moved into a separate table alone, containing all possible or some of the permutations of the "TestingCategory", "Status" and "TestYear" values and assigning to the test an id representing the appropriate combination of values.

The table below represents the table "ModelTest", if the above change applied:

ModTestID

TestingDetailsID

ModelDetailsID

1

1

1

2

1

2

3

1

5

4

1

6

5

2

1

6

2

2

7

3

5

8

2

7

9

3

1

10

3

2

Table : "ModelTest" table, Dissected further.

TestingDetailsID

Testing

Category

Status

TestYear

1

Normal

Complete

2007

2

Basic

Complete

2007

3

Basic

In-Progress

2007

4

Normal

Complete

2008

Table : "TestingDetails" table.

One can still go a step further and treat the entities in the above "TestingCategory", "Status" and "Year" as separate entities (tables) all together, resulting in even more tables.

By adding this separation of values, you do in reality gain storage space savings, since rather than strings in a table being repeated you are having integers being repeated. Another advantage is the fact that if the testing category name "Normal" now needs to be changed to "Standard" rather than updating all the values in the table, it only needs to be changed in one place, the same applies if a vehicle "Make" name changes. Splitting off "Year" into a separate table has benefits with regards to updating, and also no savings with regards to space, and so can be avoided entirely.

Thus taking the decision to further subdivide the normalised tables and treat attributes as entities on their own is entirely dependent on the business requirements, and the way that data is updated. In some cases may be futile, or pose a disadvantage due to the fact that by adding these tables, joins are also being added for queries which may slow down performance greatly and for no reason.

For the purposes of clarifying the above changes an ERD is shown below representing all possible subdivisions (including the "year" division) of the normalised tables. Every table represented is still in the 3rd normal form and so is still essentially a valid table.

Table : ERD for fully dissected model.

Conclusion

It is clear that the above two approaches, both result in working database architectures, capable of computing the necessary calculated values and meeting the assumed business requirements. However solely based on the second approach’s avoidance of null values it is clear that this is the preferred approach. Besides the avoidance of nulls, approach 2 makes it far easier to add new tests, and make the tests customizable depending on the vehicle class, testing category and testing year, without any modifications to the schema itself. With regards to the further subdivisions described in section Error: Reference source not found, adopting these changes goes above and beyond the normalisation guidelines, however due to the further avoidance of update anomalies and in some cases the saving of space, adopting some of the changes may be a beneficial tactic.