Database Normalization from Top-Level View
Designing Database Tables – Part 12
Division 2
Forward: In this part of the series, we look at a summary of the package approach to produce normalized tables and I also give you some advice.
By: Chrysanthus Date Published: 5 Aug 2012
Introduction
A normalized table is a table that is in at least the 1NF, 2NF and 3NF. A table in the 3NF most be in the 1NF and 2NF as well. In the first portion of this division, I explain the Form and Report Approach to produce normalized tables. A designer may produce tables using the Form and Report Approach and then use the Package Approach to check if the designing was alright and to finalize the tables. The opposite is true, which I explain in this tutorial.
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Producing Normalized Tables from Associations
We have taken long to learn how to produce normalized tables from associations. I will not repeat or summarize the methods here. If you have any problem with that, then go back and read the relevant parts.
Producing Normalized Tables from Top-Level View
In division 1 of this series, you learned the different types of relationships. Precisely, you learned the one-to-one, one-to-many, many-to-many, many-to-many-to-many, n-ary, aggregation, composition, generalization, and reflexive associations.
Here is the secret. To create normalized tables from the top-level view, go to each package and for each package, check if it can be broken down into any of the above associations. You then go to each block in the broken down package and check if it can still be broken down into any of the associations. You continue this way for each resulting block in a package, until no new block can be broken anymore. Many-to-many relationships should be converted to two one-to-many relationships. Many-to-many-to-many relationships should be converted to an n-ary diagram. Break blocks into compositions and generalizations. Do not forget the reflexive relationships. If you do all these perfectly your tables will be in at least the 3NF.
In practice, you do not have to keep breaking down for too long in a package before you reach the final blocks.
While establishing the normalized tables in the previous parts of the series, we were not sure whether we should add columns when we arrived at the final tables. For this, you have to see the workers who will use the database, in order to know if you need to add the columns. The workers do not need to know anything on database techniques, like you do. When you get a column name (attribute) from the workers, place the column in the table notation; as you place the column, check if 1NF, 2NF and 3NF are satisfied for the table. If there is no satisfaction, then solve the problem using the Form and Report Approach (with table notations).
Even if there is no new column to add in the database, it is still a good idea to check your tables using the Form and Report Approach (with table notations).
We have learned two ways of producing normalized forms. There is still another way. We shall begin to learn that in the next part of the series. After that, we shall go into the third division of this series, where we shall learn how to make a database with its tables efficient (in its use). The story does not end there. After that we go into a new division where we shall learn how to put all what we would have learned into code. That is, we shall learn how to code with a Database Management System (DBMS). Examples of DBMS are Sybase, MySQL, MSSQ, and MS Access. These are actually big software packages where each has its proper DBMS, and other programs to facilitate the use of the DBMS.
Is that all? No. There after we shall learn how to display forms and reports in windows. For that you will need to learn a language such as C++, or PHP, or Perl and win32/64 API or HTML. You can learn such programs and APIs in this blog. Well, if your database gets corrupted (spoilt) accidentally, you have to get a copy, which you should have backed up to replace the corrupted database. We shall learn that. Backing up a database deserves a division on its own, so it will be in a division. That should be all, but there is still a problem. There are many people out there who want to still information; they want to still things like your account number, your passwords and your confidential texts. So you will have to learn how to secure your database, to crown everything. I will treat database security as an independent series.
So, let us take a break here and continue in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT