Please consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm - find out why here .
FlatFileDemo.mdb
This example illustrates some of the difficulties inherent in bad (non-normalized) database design and how they can be corrected. It is not a formal tutorial on Normalization, rather it is a series of examples which allow you to demonstrate the kind of trouble that a non-normalized can produce. I use this example in my classes as an introduction to why you would want to normalize a database design.
I use it like this:
1) Open table: NonNormalized. This has repeated values. How do you create a query/report which shows each employee's employment history and the average salary? You have to interrogate each set of job history fields (these are the repeated values). Very difficult. (See Report: NonNormalized)
Solution: Put each employment history in its own RECORD, but you have to repeat the common employee information in order to do that.
2) Open table: FlatFile_1NF. Now you can create a query/report (FlatFile_1NF) to easily see the employment history, but you now have data integrity problems. I add a new record. I have to type all the employee information over again. I spell the employee name wrong. I type the SS# (primary key) wrong. My report is wrong. I show the data discrepancies.
Solution: Split the table into two tables: Employee and Salary History. Show the ease of reporting.
3) Show the two tables and explain that the information is grouped logically and linked based on the SS#.
4) Go to the query "FullTable" which joins the two tables so that it looks just like FlatFile_1NF. Type in an existing SS# into the NewRecord. All the employee information automatically appears. I can't mis-spell or input incorrect employee information. Download Access 97 (48 KB): http://www.rogersaccesslibrary.com/forum/uploads/5/FlatFileDemo97.zip - uploads/5/FlatFileDemo97.zip Access 2000 (60 KB): http://www.rogersaccesslibrary.com/forum/uploads/5/FlatFileDemo2k.zip - uploads/5/FlatFileDemo2k.zip
------------- Consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm" rel="nofollow - find out why here .<
|