Initial Form Evaluation
Designing Database Tables – Part 2
Division 2
Forward: In this part of the series, we look at initial form evaluation; along side, we also start learning how to create database tables from forms and reports.
By: Chrysanthus Date Published: 5 Aug 2012
Introduction
The word, “form”, used in database, can have three meanings. It can mean papers (or report) that users fill with the pen; it can mean computer display forms (or report) that users fill with the keyboard; it can mean, type, as in First Normal Form, Second Normal Form, etc.
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.
Table Notation
Consider the following:
Sale(SaleID, SaleDate, CustomerID, Employee)
This is an example of a table notation. It gives the name of the table and the names of the columns in the table. The primary key name or names are on the left in the parentheses. Here, there is one primary key and the name is SaleID. All primary key names are underlined. You can underline the foreign key such as CustomerID here with a dash line.
The table notation, gives the most basic description of the table; it does not show the cells. For the notation here, SaleID is the primary key of the table. SaleDate is the date the sale was made. CustomerID is the ID of the customer who bought from your company. Employee is the name of the member of staff who made the sale. We shall use the table notation a lot in this division of the series.
When tables of a database are created, they have to satisfy the three criteria mentioned in the previous part of the series. The first, second, and third normal forms are standards, which ensure that the three criteria are satisfied. One way to create tables for a database is to collect all the forms and reports (paper and/or computer screen) that a company is using to run the company. From these forms (and reports) you create the tables according to the normal form standards. There are two other ways we shall learn to create tables in order to obtain normal forms. This first way, we learn now is also used to explain the meaning of normal forms.
Example
Imagine that you are the owner of a CD rental store. Assume that each CD has one film and you rent them out to customers. Now you need a database to manage your business. The final database software will make all your business calculations automatic. It will greatly increase the speed of the management of your business. You will be able to retrieve any information concerning your business at the click of a mouse button (in practice some information retrieval may need the click of several screen buttons). First you have to create the tables efficiently. That is what we begin doing.
As I said above, in the course of explaining the normal forms, you will learn the Form/Report Approach to create database tables.
Initial Form Decisions
Open the following link in a new window tab, and then come back to this window (link below).
While reading the rest of this tutorial, you will be referring to the tables and/or diagrams of the opened web page.
The opened page should have a form, titled, Renting Form. It should also have a table, titled, Un Normalized Table. The form shows the transaction of a customer, called, Mary Jones. Mary Jones takes one copy of the film, “End of the Road”. She also takes two copies of the film, “People on the Move”. The top half of the form has information about Mary Jones. The bottom half has information about the CDs rented, by Mary Jones. Remember, we assume that one film is in one CD. At the bottom of the form, you have some computed values. Read through the form.
The table in the opened page, has transaction information about two customers. All the information for each of the customers can be displayed in the form in one screen. If you want information for another customer, you have to click a button (not shown) to see it in another screen.
The first thing to do is as follows: For each form and for each report, write down everything you want to store as column headings in table notation. For the form in the opened web page above, you would have something like:
RentingForm(Name, TransID, CustID, Phone, Address, City, State, ZipCode, RentDate, (VideoID, NoTaken, Title, Price))
In the notation, you do not have to type the names of the computed values. You start at the top-left of the form or report and write down a column name for each data element. Give the form (or report) notation a name. For the above case, the name, RentingForm, has been given. The form in the opened web page is not modern. This was done deliberately in order to illustrate the features of normalization.
The next thing to do is to bring items together that fall into natural groupings, putting what you think might be the primary key to the left of each group. You should end up with something like:
RentingForm(TransID, RentDate, CustID, Phone, Name, Address, City, State, ZipCode, (CDID, NoTaken, Title, Price))
The form looks like a transaction form, so TransID, for transaction ID, is made the key for the whole table. Note that computed values such as, Sub Total, Tax and Total should not appear in the table (form) notation and should not appear as you develop the tables. However, if you really think a computed value should appear in a final table, then put it at this stage. For this series the computed values will not appear in the notation and in the final tables. Computed values should normally be calculated by the software, when the user of the software (database) needs them.
At this stage any repeating (see below) group should be put in parentheses. The group “VideoID, NoTaken, Title, Price” above is a repeating group, and is in parentheses. In other cases, you can have nested repeating groups and so nested parentheses.
Our example of a repeating group is the data lines under the heading, “Rent Details” in the form of the opened page. In a form (opened page), a repeating group is a group that has more than one line of data under the same names (headings). So the headings, “CDID, NoTaken, Title, Price” form a repeating group in the form.
A repeating group for a table or a form is a group that has the potential for several similar entries (data).
The second table notation above is better than the first but it is not in any normal form. In the opened web page above, there is a table below the form. This table shows entries for two customers according to the second table notation above. In this table, data for the customer group repeat (in the rows) but data for the actual repeating group, which is the CD group does not really repeat (just a few entries repeat). So you have to be careful when trying to imagine a repeated group in a table (un-normalized table).
That is it for this part of the series. I hope you are finding the design of database tables interesting. We stop here and continue in the next part of the series.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT