Un Normalised Report 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.

Introduction

SCAN WITH VIPER PLAGERISM DETECTION

The document below presents two approaches to normalising the data; the first approach follows the normalisation rules to the letter, without applying amendments to the design to improve on the already normalised structure. The second approach builds on the already normalised structure with the aim of achieving an architecture that is more scalable, fault tolerant and efficient.

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 (key) for the table and inserting sample data.

ADD A COLUMN CALLED STATUS that contains completed etc.

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, used in both normalisation approaches:

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 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 column that was selected for the table is entitled "ModTestID" (model test id) this 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 make and model for vehicles with a variety of different year of first use values.

The following assumptions where taken to be able to build the above flat table, to ensure that all business requirements have been ensured.

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 form 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 anyway. 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 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 where 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 statement "Tests with one or more Failure, PRS, or Advisory type Defects in Category" can be ignored, as the appropriate tests can be queried from the stored data. This grouping of results is a queryed resulting set of data.

Normalisation Approach 1

GO through this: http://db.grussell.org/section008.html and make sure yours follows it.

DO this case study: http://www.sqa.org.uk/e-learning/SoftDevRDS02CD/page_22.htm

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.

First Normal Form

To bring the flat table defined in the above section into the first normal form, the repeating data and the repeating attributes need to be identified. REFERENCE http://www.sqa.org.uk/e-learning/MDBS01CD/page_27.htm#Step1

Repeating Data

From the flat table it is clear that the data in the columns "ModTestID", "TestCategory", "VehilceClass" , "TestYear", "Make", "Model", are all repeating data columns, which implies that the data within the columns is constantly being repeated.

These columns should thus be extracted and placed in a table "ModelTest" on their own as follows:

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).

I THINK TESTING CATEGORY AND TEST YEAR ARE REPEATING DATA AND SHOULD GO OUT INTO THEIR OWN TABLE (NOT SUREEEEEEEEEEEEEEEE) Possibly mention it in the changes area, but it does not break any rules.

This isn’t really normalisation see article: http://stackoverflow.com/questions/14258445/database-normalisation-repeating-fields

TestingOverviewID, Category, TestingYear

Repeating attributes

Without making any changes to the remaining data in the original table, it is clear that for a given identical value of "ModTestID" there can be multiple values in the remaining columns. Thus for a value of "ModTestID" equal to "1", in multiple rows there can be different values in the remaining columns from one row to the next.

The above issue can be easily solved by declaring the column "YearOfFirstUse" as a key column. Now for any given values of "ModTestID" and "YearOfFirstUse" there is only one row of data with the same two values of "ModTestID" and "YearOfFirstUse" eliminating the repeating attributes. This can only be guaranteed if the following assumption is taken:

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.

The table below shows a second table in the first normal form "ModelTestResult":

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.

Second Normal Form

Reference Mark White horns book

To bring the tables into the second normal form, for every table with a composite key every non key attribute is required to be fully dependent on the entire primary key.

Due to the fact that the table "ModelTest" only has one column making up the primary key, these tables can be ignored during this step.

However the table "ModelTestResult" has a key composed of both "ModTestID" and also "YearOfFirstUse". If we had to analyse every other column, we can determine that no column is dependent fully on the value of "ModTestID" and also that no other column is fully dependent on "YearOfFirstUse" alone, which implies that the table is also already in the second normal form.

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, and similarly vice versa. Given a value of a non-key column you would not be able to ensure the value of the "ModTestID" column.

Third Normal Form

Reference Mark White horns book

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

In the table "ModelTestResult" there is no non-key column value that is fully dependent on any other non-key attribute. In the table "Model" at first glance it appears that the column "Model" and "Make" are fully dependent on one-another; however there exist cases where different makes have vehicles with the same models, as is the case with "AC" and "COBRA" and "AEON" and "COBRA". Thus the two columns are not fully dependent on one another.

At first glance 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 follows:

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.

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.

Resulting Entity Relationship Diagram

Figure 3.: ERD Approach 1.

From the above ERD it is clear that there exists 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 models test for a particular value of "YearOfFirstUse" by making use of the values of "TestFailures" and "TestPasses".

With regards to the tables themselves, please note that every column in the "ModelTestResult" table apart from the key columns, are nullable columns. The cells cant 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", "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. PUT ALSO ON OTHER APPROACH TABLE. (AND MENTION IT IN DOCUMENT BELOW)

Similarly to the above there are uniqueness constranints on the combined columns "Make", "model" and "VehicleClass" therby disallowing any tow vehciles 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.

PUT ALSO ON OTHER APPROACH TABLE.

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

ADD CONSTRAINT TO ENFORCE AN ENTRY IN THE CHILD TABLE WHEN A ENTRY IN THE PARENT TABLE EXISTS. DON’T THINK IT IS POSIBLE without transactions Talk about it here. Or say that in a final application transactions would have to be used IMP

Normalisation Approach 2

In this approach the normalised tables obtained in section 3 will be used as a basis, as well as the change described in section 5 applied to those normalised tables. REMOVE

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. In the interests of good design such tables should be avoided. (REFERENCE)

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

Thus 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, an unwritten assumption was taken 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, 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, the following changes would be made to the table "ModelTestResults" to bring it to the first normal form, which lead to the requirements described in section 5 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.

Second Normal Form

At this stage we have the table "TestType", "TestingCategory" and "ModelTest", which all have a single key value, 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:

ModelTest_TestCategory: "TestingCategory" is the parent table, and there can be zero or more entries in the "ModelTest" table with a "TestingCategoryID" equal to a primary key in "TestingCategory" table. This implies that a testing category can exist but no tests may have been stored yet using that category.

TestType_ModelYearTestResult: "TestType" is the parent table and there can be zero or more entires 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 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.

ENFORCE THE ZERO OR MANY CONSTRAINTS described in the relationships above IN THE DATABASE AND ANY OTHER RESTRICTIONS

Changes to the Final Results

http://stackoverflow.com/questions/13903174/how-far-does-one-go-to-eliminate-duplicate-data-in-a-database HERE IT SAYS THE CHANGES YOU WANT TO DO HAVE NOTHING TO DO WITH NORMALISATION

Possibly you can split off Model into a separate Table and Make into a separate table, to avoid Update anomalies, such as updating the make name etc.. Year of first use maybe can be split up into a separate table (but it poses no update anomalies), similarly Year of Test maybe can go into a separate table but it poses no update anomalies, you are just saving space, but adding a join. (In both ways you are adding a join). Technically even with the change below you are adding a join (Do you want to do it????)

I WOULDN’T DO THE BELOW .. OR give it as an example and mention the Model and Make also.

Maybe you can say that in the beklow the testing category was a form of repeating data , but nor really.

These steps don’t really have to do with normalisation: http://stackoverflow.com/questions/14258445/database-normalisation-repeating-fields

I THINK TESTING CATEGORY AND TEST YEAR ARE REPEATING DATA AND SHOULD GO OUT INTO THEIR OWN TABLE (NOT SUREEEEEEEEEEEEEEEE) Possibly mention it in the changes area, but it does not break any rules.

This isn’t really normalisation see article: http://stackoverflow.com/questions/14258445/database-normalisation-repeating-fields

Looking at the final results for both approaches, I see a number of possibilities how the design can be modified and improved in a number of ways. The changes described below do not get trapped by the normalisation rules, and yet can lead to a number of benefits if adopted. (The Principle of Orthogonal Design, or POOD. NOT REALLY THIS RELATES TO COLUMNS SHARED BY MULTIPLE TABLES)

Looking at table "ModelTest" it is clear that having a string value to represent the value of "TestingCategory" is an obvious waste of space. It would make more sense to move the testing category string to a separate table with a new ID as follows:

TestingCategoryID

Category

1

Normal

2

Basic

Table : "TestingCategory" table, Approach 2.

The "ModelTest" table would then be modified as follows:

ModTestID

Testing

CategoryID

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 : "ModelTest" table, Approach 2.

Note that although in the data provided the testing category of "Normal" was provided, in the above tables a second category of "Basic" was added based upon the assumptions taken in section 2. This change could have also been followed in ERD for approach 1. (SHOULD YOU EVEN HAVE IT)

Conclusion

It is clear that the above approaches, both result in working database architectures, capable of computing the necessary calculated values. However solely based on approaches 2 avoidance of null values it is clear that this is the preferred approach. Besides the avoidance of nulls, approach 2 makes it far more 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.

http://www.basenow.com/help/Normalization.asp

 

 

 

 

 

 

 

 

 

A table should avoid nullable columns.

 

 

 

 

 

 

 

 

 

 

All columns in the ModelTestResult table are nullable columns.

 

 

 

 

 

 

 

 

By avoiding these nullable columns, you can increase the number of tests depending on TestCategory or VehicleClass or decrease the number of tests.

You can also increase the tests on a particualr year, with out schema changes.

 

 

 

 

 

 

I.e good practice to avoid null values in columns (Even though it is not part of the normalisation rules)

 

Mark Whitehorns and bill.. Book: Inside Relational Databases with Examples in Access

 

"I would not for a moment

suggest that in a trite thousand or so words I have done anything except alert

you to the fact that there are problems associated with using nulls. Be careful out there."

Then continue and remove the nulls and show the ERD for Approach 2.

Assumption: The tests for vehciles with the same year of first use are combined together, which implies that there cant be vehicles with the same year of first use of the same make and model

Assumption: That there are a variety of other tests for different test categories/vehcicle classes that must be handled

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Assumption: Inorder to meet the assumption above by followign normalisation rules, all the test columns

can be considered as repeating columns (this could possibly

be a logical decision and not actually a normalisation rule, decide what you will put).

 

 

 

With this approach the sparse columns are also dealt with (not mentioned in normalisation rules)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Talk about having a table for each logical entity in the real world. In this case the Model is an entity in the real world.

Talk about the relationships between the tables. In the ERD

You should supply us with a Word document that includes your ER diagram and specifies any assumptions that you have made about the data during the normalisation process. Also note any logical anomalies that you (as an intelligent human being who knows something of how the world works) can see in the data. At least one such anomaly exists and you don’t have to be particularly knowledgeable about cars to see it/them. In addition you should include an Access .MDB (or .ACCDB) file with some sample normalised data.