@ow it is time to figure out which data tpes need to be used for the
attributes. There are a lot of different data tpes. % few are standardi"ed, but man databases have their own data tpes that all have their own advantages. ome databases offerthe possibilit to define our own data tpes, in case the standard tpes cannot do the things ou need.
The standard data tpes that ever database knows, and are most:used, are) 06%3, V%306%3, THT, 4'C%T, DC1', and I@T.
Text)
• 06%38length9 : includes text 8characters, numbers, punctuations...9.
06%3 has as characteristic that it alwas saves a fixed amount of
positions. If ou define a 06%3859 ou can save up to ten positions maximum, but if ou onl use two positions the database will still save 5 positions. The remaining eight positions will be filled b spaces.
• V%306%38length9 : includes text 8characters, numbers,
punctuation...9. V%306%3 is the same as 06%3, the difference is that V%306%3 onl takes as much space as necessar.
• THT : can contain large amounts of text. Depending on the tpe of database this can add up to gigabtes.
@umbers)
• I@T : contains a positive or negative whole number. % lot of
databases have variations of the I@T, such as TI@7I@T, B%''I@T, BDIBI@T, 1IJI@T, I@T<, I@T=, I@TE. These variations differ from the I@T onl in the si"e of the figure that fits into it. % regular I@T is = btes 8I@T=9 and fits figures from :<5=K=E?L=K to M<5=K=E?L=L, or if
ou define it as @IJ@D from to =<N=NLK<NL. The I@TE, or 1IJI@T, can get even bigger in si"e, from to
5E==LK==K?KNOO5L5L, but takes up to E btes of diskspace, even if there is (ust a small number in it.
• 4'C%T, DC1' : The same idea as I@T, but can also store floating point numbers. . Do note that this does not alwas work perfectl. 4or instance in B&' calculating with these floating point numbers is not perfect, 85/?9>? will result with B&'-s floats in .NNNNNNN, not 5.
Cther tpes)
• 1'C1 : for binar data such as files.I@T : for I# addresses. %lso useable for netmasks.
4or our example the data tpes are as follows)
Figure 12: ,ata mo"el "isplaying "ata types.
@ormali"ation
@ormali"ation makes our data model flexible and reliable. It does generate some overhead because ou usuall get more tables, but it enables ou to do man things with our data model without having to ad(ust it.
@ormali"ation, the 4irst 4orm
The first form of normali"ation states that there ma be no repeating groups of columns in an entit. +e could have created an entit -sales- with
attributes for each of the products that were bought. This would look like this)
Figure 13: ot in 1st normal form.
+hat is wrong about this is that now onl ? products can be sold. If ou would have to sell = products, than ou would have to start a second sale or ad(ust our data model b adding -product=- attributes. 1oth solutions are unwanted. In these cases ou should alwas create a new entit that ou link to the old one via a one:to:man relationship.
Figure 14: n aor"ane with 1st normal form.
@ormali"ation, the econd 4orm
The second form of normali"ation states that all attributes of an entit
should be full dependent on the whole primar ke. This means that each attribute of an entit can onl be identified through the whole primar ke.
uppose we had the date in the alesGdetails entit)
Figure 1!: ot in 2n" normal form.
This entit is not according the second normali"ation form, because in
order to be able to look up the date of a sale, I do not have to know what is sold 8productnr9, the onl thing I need to know is the sales number. This was solved b splitting up the tables into the sales and the alesGdetails table)
Figure 1&: n aor"ane with 2n" normal form.
@ow each attribute of the entities is dependent on the whole #F of the entit. The date is dependent on the sales number, and the 2uantit is dependent on the sales number and the sold product.
@ormali"ation, the Third 4orm
The third form of normali"ation states that all attributes need to be directl
dependent on the primar ke, and not on other attributes. This seems to be what the second form of normali"ation states, but in the second form is actuall stated the opposite. In the second form of normali"ation ou point out attributes through the #F, in the third form of normali"ation ever
attribute needs to be dependent on the #F, and nothing else.
Figure 1': ot in 3r" normal form.
In this case the price of a loose product is dependent on the ordering
number, and the ordering number is dependent on the product number and the sales number. This is not according to the third form of normali"ation.
%gain, splitting up the tables solves this.
Figure 1(: n aor"ane with 3r" normal form.
@ormali"ation, Bore 4orms
There are more normali"ation forms than the three forms mentioned above, but those are not of great interest for the average user. These other forms are highl speciali"ed for certain applications. If ou stick to the design rules and the normali"ation mentioned in this article, ou will create a design that works great for most applications.
@ormali"ed Data Bodel
If ou appl the normali"ation rules, ou will find that the -manufacturer- in de product table should also be a separate table)
Figure 1): ,ata mo"el in aor"ane with 1st/ 2n" an" 3" normal form.
Jlossar
%ttributes : detailed data about an entit, such as price, length, name
0ardinalit : the relationship between two entities, in figures. 4or example, a person can place multiple orders.
ntities : abstract data that ou save in a database. 4or example) customers, products.
4oreign ke 84F9 : a referral to the #rimar Fe of another table. 4oreign Fe:columns can onl contain values that exist in the #rimar Fe column that the refer to.
Fe : a ke is used to point out records. The most well:known ke is the
#rimar Fe 8see #rimar Fe9.