APPROACH TO THE PROBLEM

I was of the view that working through the problem involved simply arriving at a concise explanation of normalisation and that teaching a lesson about the the subject of normalisation would involve expanding this by way of example. The question obviously provides a good backdrop to such a lesson, which in turn can be directly related to practical work that the students would be doing. Essentially the identification of key fields and linking the tables.

THE LESSON PLAN

The lesson would be structured in such a way as to allow an introduction to the topic based upon the pupils assumed knowledge and that normalisation is the process of devising the best design of a relational database. The principle points of:-

1. No unnecessary duplication of data is obviously the most important.

2. Consistency across the database, the inability to store different addresses in different tables.

3. Flexibility in the storage capacity of data items.

4. The ability to make complex queries.

This would be explained alongside a demonstration of the way in which the tables are broken down in the question and to explain the importance and significance of the Invoice table. First second and third normal form would be explained as the tables were broken down, using standard notation and the visual demonstration of it in Access. The home work would be question 2 on page 114 of Heathcote.

MY LEARNING

The concept of normalisation is a complex one when considered in a practical sense. To actually sit down and work out the relationships that are needed between tables or files in order that complex queries can be performed on all of the data is for me the easiest way to solve such problems and would be the way that I would initially try to teach it but alongside a developing solution that the pupils would input to Access. The setting up of the tables was straightforward, validation was not an issue for this lesson and thus it was simply necessary to identify the key field in each table from having written it down and then considering the problem from a practical perspective. The key fields were easily identified and inserted into the table the last relationship is one to one, there is no relational integrity between the stock file and the other files. There is no need for this because as each transaction will be invoiced an error message would display or the program would crash if an entry for a deleted item of stock was made. The customers orders and invoices are not dependent upon the existence or otherise of stock whereas the other tables are mutually dependent. This way there is no duplication of any of the fields in the linked tables and they are, in other words, in third normal form. This being the reason for the fourth table.

CONCLUSION

The concept of relational databases and normalisation and SQL are really mutually dependent and the topic cannot really be fully appreciated until all areas are covered in more practical detail, in the context of working examples created by the students and possibly shown on an IA at the same time. Having to think about the subject in such a way as to focus on the teaching of it made me think carefully about the subject analytically.Rather than simply solving the problem without thinking about how I solved it I tried to focus on the processes I had gone through myself to learn the subject matter and hopefully pass this on.