Roger's Access Library Homepage
Forum Home Forum Home > Roger's Access Library > Roger's Download Samples
  New Posts New Posts RSS Feed - FlatFileDemo.mdb (beginner)
  FAQ FAQ  Forum Search   Events   Register Register  Login Login


Please consider donating $1 per sample downloaded, (find out why here)

FlatFileDemo.mdb (beginner)

 Post Reply Post Reply
Author
Message
Roger Carlson View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 20 Feb 2008
Status: Offline
Points: 249
Post Options Post Options   Thanks (0) Thanks(0)   Quote Roger Carlson Quote  Post ReplyReply Direct Link To This Post Topic: FlatFileDemo.mdb (beginner)
    Posted: 01 Aug 2008 at 4:27am

Please consider donating $1 per sample downloaded, 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):
uploads/5/FlatFileDemo97.zip
Access 2000 (60 KB): uploads/5/FlatFileDemo2k.zip

Consider donating $1 per sample downloaded, find out why here.<
Back to Top
Sponsored Links


Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down