169 Chapter 5 - Database Management Systems implemented in a relational database. The analyst E-R diagram by following these five steps: 1. Create a relational table for each entity. In Figure 5.10, figure 5.10 is the logical design for our database. lt is the data model in Figure 5.9 can create this logical model from the cUSTOMERS, entity tables. Tables must be INVENTORY, ORDERS, and SALES are the uniquely named. RIGURE 5.10 Relational Database shaded Attributel . Prinary Key 200.00 0000-00 20 in. nicycle 24 in. 8icyele Bo-054 10-0050 20 in. icycle Kickatand Handiebar Peda 26n 3.13 1012 WIKEEL 1010 2010-0050 1011 1001-1 1011 1002-1 1012 2003-1 1012 2002-1 1.25 13 2001-2 003-2 016 1003-1 1016 3961-1042 0161000- 021207028 1002-1 170 Accounting Information Systems a primury ky for each of the entity tables. The uniquely idenify each row within the table, and it must conta y primary key o number (ltem Number 2. Determine a and order number (SO Number) are commonly used to identify followed by a three-digit sequentially assigned serial number (the umi row, A custoner items of inventory, orders of the Shipment Number) to represent each shipment record (row) A custormer code or number (Cust Code), stock n and orders. The SALES table uses the date (y afhinld and is represented in a database table as a column. The accepted among relational database designers is to put the primary key attribute in de 3. Determine the arabater for each of the entities. An attribute is some The other columns shown in these entity tables contain typical attributes, h you will see different attributes included in databases used by different organ tions. Attributes (columns) must have unique names within each table first column of the table. User requirements determine the other attr 4. Implement the relationships among the entities. This is accomplished by ensu ing that the primary key in one table also exists as an attribute in every tabie menting the many-to-many relationships requires the creation of ta (entiry) for which there is a relationship specified in the E-R diagram, Imple elatrionship tables or jivnuction tables Relationship (junction) tables are tables wi bles, calle cowpocite primary keys that connect (join) tables in a many-to-many r This is necessary because relational DBMSs cannot model a many-to-mary relationship directly. Each many-to-many relationship must be modeled as pair of one-to-many relationships. For example, the SALES and INVENTORY relationship (MN) is modeled as two (1:N) relationships. One of these relacior ships is between SALES and the relationship table titled "SALES line INVENTORY," and the other is between INVENTORY and "SALES INVENTORY." Relationship tables always have composite primary dhat are combination of the primary keys of the entity tables that parniopan the MN relationship. 5. Determine the atmiete, if any, for each of the relationshp bles, So m ian tionship tables only need the columns that make up their composite prma keys. Other relationship tables provide a way to store interesting i compi that depends on the combination of the attributes contained in primary keys The two relationship tables shown in Figure 5.1y tional attributes. For example, the "SALES line items INVENTOfad includes the attribute Qty Shipped. This attribute stores the quanu s item on a particular sale. This value is determined jointly by the aup Number and the Item Number, which are the two attributes that table's composite primary key Using Databases and Intelligent Suc Chapter 5-Database Management Systems 1 E-R diagram by following these five steps 1. Create a relational table for each entity. In Figure S Figure 5.10 is the logical design for our database. It is the dat mica implemented in a relational database. The analyst can create this in Figure 5.9 logical model from the steps NVENTORY, ORDERS, and SALES are the entity tables. uniquely named. 10 Relational Database shaded Attribute (el- Primary key Code Cuee Name inter. cycle Salon STANS Center Havt 0000.00 26 in. Bieyele 24 in sierele 20 in. aieycle 1280-054 3050-2197 961-1010 Pormed Handlebar Pedal Tire, 26 in. 72 3961-1042 965-1050 Spoke Reflector Wheel 232 3970-1022 ORDERS so Number Cust Code Cuet Order Number 0 Date 347 56-6 3422 1012 WHEEL 1013IBS 1014 ETC 1015 WHEEL 1016ETC 041205 061205 061206 5673 345 shi BTC 021207028 021207042 ORDERS lin 1010 1000-1 1010 2010-0050 1011 2001-1 1011 1002-1 1012 1003-1 1012 1001-1 1013 1001-1 1014 1003-1 1015 1003-1 1016 2003-1 1016 3965-1050 1016 3961-1041 1016 1000-1 137.50 11.25 127.50 150.00 60.93 127.50 78.30 37.42 37.42 60.93 33.00 3.13 137.50 10 50 25 25 50 Shi 021207028 1001-1 021207028 1002-1 Accounting Information Systems primary key aitremcustomer bles. The primary nd it must contain a val uniquely identify each row within the table, and it must contain row. A customer code or number (Cust Code), stock number (Itern e in and order number (SO Number) are commonly used to ident Nun ems of inventory, and orders. The SALES table uses the date 2. Determine a primary k niqprimury bey for each of the entity tables. Th lowed by a three-digit sequentially assigned serial number (th M of the hipment Number) to represent each shipment record ine the arribure for each of the entities. An attribute i represented in a database table as a column. Th custom 3. Determine the attribute ary key attribute in a field andis among relational databas first column of the table. User requirements ts determine the other atti the in databases used by diffee database designers is to put the primary The other columns shown in these entity tables contain typical attri you will see different attributes included in databases used by different tions. Attributes (columns) must have unique names within each table. 4. Implement the relationships among the entities. This is accomplished by butes, but. ing that the primary key in one table also exists as an attribute insur (entity) for which there is a relationship specified in the E-R diagatable mple. menting the many-to-many relationships requires the creation of tabl nationship tables or junction tables. Relationship junction) tables are tabl sd compastte primary This is necessary because relational DBMSs cannot model a many-to relationship directly. Each many-to-many relationship must be modeled as pair of one-to-many relationships. For example, the SALES and INVENTOR relationship (M:N) is modeled as two (1:N) relationships. One of these relation. iten ships is between SALES and the relationship table titled "SALES line INVENTORY," and the other is between INVENTORY and "SALES line em INVENTORY." Relationship tables always have composite primary keys thar are a combinatiofepimar kys of the entity tables that participate i the M:N relationship 5. Determine the attributes, if any, for each of the relationship tables. Some rela- tionship tables only need the columns that make up their composite primary keys. Other relationship tables provide a way that depends on the combination of the attributes contained in their composite primary keys. The two relationship tables shown in Figure 5.11 each have ad- tional attributes. For example, the "SALES line items INVENTORY" table includes the attribute Qty_Shipped. This attribute stores the quantity of each item on a particular sale. This value is determined jointly by the Shipment umber and the Item Number, which are the two attributes that make up to store interesting information table's composite primary key s and lIntelligent Su