1.3 Data Analysis 141 1 3 Data Analysis ey Ne eH eae 1.3.1 ERD ‘The "Entity-Relationship model (E-R model)" is a dia ing the conceptual mode! ship i gram expressing th lent of DBMS. The enity-relationship diagram ERD) is wed hee: ERD reprseot le wat fee terms of entities, their relationships and their attfbutes, none ea The E-R model consists ofthe following three elements: + Entities Entities are objects to be managed as depicted by rectangles. + Relationships A relationship indicates a relation between an entity entity and a relationship, and is depicted by diamonds. and another entity or a relationship between an © Attributes Auributes are characteristics of entities and of relationships, and are depicted by ovals, [Figure 4-3-1 =4 ER Model Teacher ‘eu ‘Saude ares) Gate The E-R model in Figure 1-3-1 shows the following: ~ “Teacher” and "Student" are connected by “Lecture.” = "Teacher" has "Teacher's name.” = *Studeat" has "Name" and "Score." = “Lecture” has "Subject name.” ‘There are three types of relationships: “one-to-one,” “one-to-many,” and “many-to-many.” In Figure 1-3-1, if one teacher gives a lecture to more than one student, and a student receives lectures from more than one teacher, the relationship between "Teacher" and "Student” is "many-to-many." 1.3.2 Normalization To design a database that fils the users: purposes, the database structure must be thoroughly examined, If not fully examined, users may make demands for other ways to use the database after loading the actual data. Such modifications tend to be very time-consuming and inefficient. Company A, for example, is a distributor of office automation equipment and uses the onler slip shown in142 Chapter 1 Overview of Database ‘Slip of Company A One Sip Duc ___ Snes ber The characteristics of the merchandises, customers, and order-receiving data of Company A are as follows: ~ "Customers" are lasting clients and each customer has its own "customer number.” = Each "merchandise" has its “merchandise number" and “unit price." = "No." isa sequential aumber for order received for *merchandlises.” = “Amount” is calculated by “unit price" x “quantity.” = "Order aniount" is the total of amounts." Company A plans to design a database of these order slips and related data for efficient order management. For example, when designing a database by the relational data model after deciding the purpose of applications, tables are created by classifying necessary data items to manage. Normalization of data is necessary in this phase. The purpose of normalization isto eliminate the redundancy from data and achieve integrity and consistency of data. ‘There are five stages for the normalization of a relational database: ~ The Ist normalization = The 2nd normalization = The 3rd normalization ~ The 4th normalization ~ The Sth normalization However, since a relational database requires only the Ist to the 3rd normalization, explanations up to the 3rd normalization are given here. In the example of Company A, the data items inthe order slip can be arranged in a table as shown in Figure 133, Table of Order Stip of Company A (order detail able) GSS Re SS SS [Soe en Ea de oe se [Pe Work Uae waa [aaa Uae [uy] Anse ‘The database in this phase is called the unnormatized form (non-Ist normal form The underlined items here are key items. Key items means the items used to identify records. Thus, ita certain data item is identified, other data items are uniquely determined. This is called “functional dependency (FD)." (1) The 1st normalization ‘There are fixed parts and repetition parts in the unnormalized data as follows:1.3 Data Analysis 143 + Fixed part Order stip number, customer number, customer name, customer address, date, and order amount + Repatition part No., merchandise number, merchandise name, unit price, quantity, and amount {In the Ist normalization, data is divided into the fixed part and the repetition part, and the fixed part is ‘overlapped with the repetition part. In this stage, both amount and order amount are excluded because they are decided by calculation of other items, and do not have to be included in the database. AAs a result of the Ist normalization, the ovder slip of Ce i epee This is called the Ist normal form. 'p of Company A is arranged as shown in Figure 1. Figure 1-3-4 Tha tst Normal Form dt tie Ce a ee Ble] t ween ois rele eps pat In the order slip of Company A (unnormalized form), only the slip number was specified as a key Item. However, in the Ist normal form, the order slip number and No. are specified as key items because the order slip number cannot specify the repetition items (No., merchandise number, merchandise name, unit price, and quantity). Therefore, combinations of multiple data items such as "slip mumber + No." are used as concatenated keys. (2) The 2nd normalization In the 2nd normalization, data items are divided into those data iteras completely Functionally dependent on the Key items ("slip number" + "No.") and the data items partially dependeat on the key items (functionally dependent on cither of the "slip number" or *No.”). + Dala items completely functionally dependent on key items Merchandise number, merchandise name, unit price, quantity * Data items partially functionally dependent on key items (“order slip number”) ‘Customer number, customer name, customer address, date ‘The result of the 2nd normalizat S, This is called the 2nd normal form. us on ply fey [Ee Can] S| OS The 2nd depends on bey tems “umber | umber | came | adden | O° Normal Form ett casein ies ve Poesy sete] we [SRS [ RS] US Tose] (3) The 3rd normalization In the 3rd normalization, data items functionally dependent on the data items other than key items, are divided from the data in the 2nd normal form. ‘The 3rd normalization procedure is as follows: 1. If the customer number is identified, the customer same and the customer address are uniquely determined. So, the order table is divided into the groups of "order slip nurnber and date” and “customer number, customer name, and customer address.” "Customer number" Is included in the order table to ‘coordinate it to have relationship with the customer table.144 Chapter1 Ovorview of Database 2 If the merchandise number is identified, the merchandise name and the unit price are uniquely determined. So, the order table is divided into the groups of Yorder slip number, No., and quantity” and "merchandise number, merchandise name, and unit price.” "Merchandise number" is included in the order table to coordinate it o have relationship with the merchandise table. The resull ofthe 3rd normalization is shown in Figure 1-3-6. Tis is called the 3rd normal form. erable ner ds we fois] pa, [oman ‘aii. “Gani | Me ‘usiet = peer |S ee Normal Form ‘cavomer abe Pores ental [Sums | Cue [Cx] [exis Meche | Un ‘ati | eee | siren ‘at | nome | ce AAs the above example, the redundancy of the data can be eliminated by data normalization. Divided tables ‘can be reproduced in the original table in the unnormalized form by means of key items. Concrete data examples in line with the steps of normalization are shown below. By reference to these examples, we can firmly grasp the image of normalization.145, 13 Data Analysis §}-|~|-}+]-)<1-]-2| =| 9]148 Chapter 1 Overview of Database DoFeOT ane TSN FAD ETT PTOI ID AI] ON [inoue apo, 3a Tape FUER Suey aOR oF OTE DORI] OND BUDE ET PHOS AAD UTD] Tou PIO) 2a Spe WEIN EDGE) oorere our FORA TG TET Tanase pI ‘SSPE Pwo oOTCT8 (Sen aemapeorts oxo (eT . T_looo'0¢ | Srenyos Buresssoxd pam | 1 Jo00"008 aaiuud s35e 7) s re g = Joors BRS RVT, ; | Tee = [00st ‘31ae NVA eee $joore seded oie $0 Me i ee Bion rv) zee 000, snow z Tee & 1 Joooon1 ‘BIUNYOR PaUIADOL & i ine - TZ looo'0st. “amd aes ped #7 WIFE a 1eee ae < {pore SEO {oo oat | —siemagor pomest=a 100" THe NV 1 Joos'r a1q0o NV] fees “depo jroraas ToooroRT | 2arUues jeuoned donwaa] coma ‘9 [000° — OF [oot asa FV 2 oor | SEATS Baa.00T BOR [0000s SHERYOE SUDESITS| 300s BONY 9 oos"t 31ers NVI] 000007 SORTS FIERSIT] 1 foo = ]000°00r Joos o 5 Jooo'oe ——] aienior Fuwsss5and aay a $ Joo ot [inline jason Tora [00°F ‘Semon Foor BORIS Sa = les ialeT oz eV] Toor or SreRYOS pareLADTU| 100 0or TBIUUT IT] Sooo os aaron a wimg) saudi, | auRey ompuRYSIay ae wuog FEWUON 135 OWL RE EIuNin verona TERETous [Semper eam] “aos or 1 Jooo%or semyor Tuyses5ex7 pO) —O1OIS | 6 1 oooro0e uBT FT| OOH | [000s BRONV|_O200N[-c [2 foostt 1008/9 Joos 5 $ |o00'E, £ Z [000% £ 1 Jooooor z Z_looorose 1 yateg 1 fooo'r g Tjooooor —|-—sienor para 1 1 oars PNY] * Toast 3198 NVA € 1 Joowroz Zerdope uTUHRL|OGDIN |e TTooor081 | rues wont dont T ated loo aston ai ar peat BIRT TY 6 Z_looo'0¢ __[sieayor Sussavoxd poy A Z_|oo0'0s | ~ sien nouspeaids L 3-|ooo's PONV] 5 3 joos"t 31989 NVA 5 a Rayos pa * i € 7 z £ i zie s £ + r a Tes : ict wo z Late ize ma Eat 148 Chapter Overview of Database 3141 32p10 ‘uuio, JeULON puz eu,1.3 Data Analysis 149 RrSROWNRaRG Pea] pri" oMuny ee _coTe YORI a ETI WT SD eeu MD 1 ‘O11 aes amet150 Chapter 1 Overview of Database PAT ay OTTIRS wa HT"0D FAD =H} ie RD Sq TAWOIRN TaeIses $4] sade oH FV) aBRyOS paHETUT STEAYOS TIPS) “senor Sasol | eae aA HONW 319 NYT eu aerT| ins ET TNA] oe ‘=moyi| OOOH norr|_F | aan | ee enpmmisony PO) | dig SE SPUBYION SIRE AIIP HIG,