Make an ERD for TAL data, and answer the questions included below related to TAL data. Please check your answers carefully before submission. No programming is needed, the reports must be hand calculated/tabulated. Price stored in Item should be viewed as List_Price.

Study TAL design carefully. It also illustrates the use of header/line table structures found in designs related to multi-line business documents (e.g., Orders, and Order_Line here).

Chapter 2 DATABASE CONCEPTS Before learning how to design a database, you need to be familiar with some important database concepts related to relational databases, which are the types of databases you examined in Chapter 1 and that you will use throughout the rest of this text. The terms entity, attribute, and relationship are important to understand when designing a database; the concepts of functional dependence and primary keys are critical when learning about the database design process. Relational Databases A relational database is a collection of tables like the ones you examined for TAL Distributors in Chapter 1 and that appear in Figure 2-1. Formally, these tables are called relations, and this is how this type of database gets its name. Than REP REP NUM LAST NAME FIRST NAME STREET CITY STATE POSTAL CODE COMMISSION RATE 15 Rafael 721 Vincent Gree CA $2157.50 JO Grade Men 2 Tratris St Pullition $1131700 15 Thul 1785 Tyler And Northed CA SKIS $27.79.35 an 357 Os Car CUSTOMER CUSTOMER NUM CUSTOMER NAME STREET CITY STATE POSTAL CODE BALANCE CREDIT LIMIT REP NUM 126 Toys Galore 28 Lakatos Fullson $1.21025 57.500.00 15 20 452Cam De Cine CA OM $$78.00 $10,000 000 23 The Everything 32 MS Core CN OT $2.145.75 $7,500.00 45 Shop 134 Man Northfuld CA SON SN79.25 $7.500.000 Department 410 3256. Central de Poltron $5.00 $5.000.0045 0104 $5.025.75 $5,000.00 15 $1456.75 Groe Historical Mwm Store Cards and More Almondton General Stone Cricket Shop Cross Stone Unique Klima All Seasons 713 79 167 Hale S. Me De Ave Almond CA 372 Chfonds Gro CA 12 Rising Sun Ave. Come CA TN Pemere Se North GA 945 Gilham st Mew CA 352 Wide All CA $15.00.0045 S7 Son $10.000.00 15 $7,500 45 $15.000 $7.5000 15 SINO $12.00 $124.75 $3.475.90 $93575 SOS 104 FIGURE 2-1 Sample data for TAL Distributors ORDER LINE ORDER NUM ITEMUNUM NUM_ORDERED QUOTED PRICE SMS) 25 51 S1 51610 51610 KITS TRO ORDERS ORDER.NUM ORDER_DATE CUSTOMER_NUM 5160 TOV13/2013 12 5161 HIS 5163 JOVENS 51614 11 51617 TWINS 51619 11/2015 126 51633 INS 5102 10/2015 31618 51617 31617 DII NIN TWS FDNI $12.95 STS SIT 4 3 2 3 SMS 31128 51 $1633 51625 FH24 134 10 $4579 PRICE STOREHOUSE 3 2 1 ITEM ITEM NUM DESCRPTION A1174 S Wood Block Set ple 1.31 Casio Railway Set DIST Cant Star Teaser Mame VI Stocking on FI24 Hele het KATS s. 19 51079 23 SO $13 ON HAND CATEGORY GE 21CM 36TORY 12 TOY 34 PEL GM TONY PL 56 PEL 110 SPIL 32 TOY 75 GHEE 30 TOY 1 $75.00 KLIN MTU NUN TRO TWS Pick Up Sick Zaubersten Bruin Woodlackpl 2 1 1 3 2 2 545.19 $119.75 Fire time STIS 95 FIGURE 2-1 Sample data for TAL Distributors (continued) NOTE The names of columns and tables in this text follow a common naming convention in which column names use uppercase letters and replace spaces between words with underscores. For example, TAL Distributors uses the column named LAST_NAME to store last names and the column named CREDIT_LIMIT to store credit limits. Entities, Attributes, and Relationships There are some terms and concepts that are very important for you to know when working in the database environment. The terms entity, attribute, and relationship are fundamental when discussing databases. An entity is like a noun; it is a person, place, thing, or event. The entities of interest to TAL Distributors, for example, are such things as customers, orders, and sales reps. The entities that are of interest to a school include students, faculty, and classes; a real estate agency is interested in clients, houses, and agents; and a used car dealer is interested in vehicles, customers, and manufacturers. appears on its own line. Using this method, you represent the TAL Distributors database as follows: REP (REP_NUM, LAST_NAME, FIRST_NAME, STREET, CITY, STATE, POSTAL_CODE, COMMISSION, RATE) CUSTOMER (CUSTOMER_NUM, CUSTOMER_NAME, STREET, CITY, STATE, POSTAL_CODE, BALANCE, CREDIT_LIMIT, REP_NUM) ORDERS (ORDER_NUM, ORDER_DATE, CUSTOMER_NUM) ORDER_LINE (ORDER_NUM, ITEM_NUM, NUM_ORDERED, QUOTED_PRICE) ITEM (ITEM_NUM, DESCRIPTION, ON_HAND, CATEGORY, STOREHOUSE, PRICE) Notice that some tables contain columns with duplicate names. For example, the REP_NUM column appears in both the REP table and the CUSTOMER table. Suppose a situation existed wherein someone (or the DBMS) might confuse the two columns. For example, if you write REP_NUM, it is not clear which REP_NUM column you want to use. You need a mechanism for indicating the REP_NUM column to which you are referring, One common approach to solving this problem is to write both the table name and the column name, separated by a period. Thus, you would reference the REP_NUM column in the CUSTOMER table as CUSTOMER. REP_NUM, and the REP_NUM column in the REP table as REP. REP_NUM. Technically, when you reference columns in this format; you say that you qualify the names. It is always acceptable to qualify column names, even when there is no potential for confusion. If confusion might arise, however, it is essential to qualify column names. FUNCTIONAL DEPENDENCE The concept of functional dependence is crucial to understanding the rest of the material in this chapter. Functional dependence is a formal name for what is basically a simple idea. To illustrate functional dependence, suppose that the REP table for TAL Distributors is structured as shown in Figure 24. The only difference between the REP table shown in Figure 2-4 and the one shown in Figure 2-1 is the addition of an extra column named PAY_CLASS. RATE 15 Grove CA 90092 0.06 REP REP LAST FIRST STREET CITY STATE POSTAL COMMISSION PAY NUM NAME NAME CODE CLASS Campos Rafael 724 Vinca Dr. $23.457.501 Gradwy Megan 632 Liatris St. 90085 $11.317.00 2 Tian Hui 1785 Tyler Ave. Northfield CA 90095 $27.789.25 1 Sefton Janet 267 Oakley St. Congare CA $0.001 30 Fullton CA 0.05 45 0.06 60 20097 0.06 FIGURE 2-4 REP table with a PAY_CLASS column Chapter 2 DATABASE CONCEPTS Before learning how to design a database, you need to be familiar with some important database concepts related to relational databases, which are the types of databases you examined in Chapter 1 and that you will use throughout the rest of this text. The terms entity, attribute, and relationship are important to understand when designing a database; the concepts of functional dependence and primary keys are critical when learning about the database design process. Relational Databases A relational database is a collection of tables like the ones you examined for TAL Distributors in Chapter 1 and that appear in Figure 2-1. Formally, these tables are called relations, and this is how this type of database gets its name. Than REP REP NUM LAST NAME FIRST NAME STREET CITY STATE POSTAL CODE COMMISSION RATE 15 Rafael 721 Vincent Gree CA $2157.50 JO Grade Men 2 Tratris St Pullition $1131700 15 Thul 1785 Tyler And Northed CA SKIS $27.79.35 an 357 Os Car CUSTOMER CUSTOMER NUM CUSTOMER NAME STREET CITY STATE POSTAL CODE BALANCE CREDIT LIMIT REP NUM 126 Toys Galore 28 Lakatos Fullson $1.21025 57.500.00 15 20 452Cam De Cine CA OM $$78.00 $10,000 000 23 The Everything 32 MS Core CN OT $2.145.75 $7,500.00 45 Shop 134 Man Northfuld CA SON SN79.25 $7.500.000 Department 410 3256. Central de Poltron $5.00 $5.000.0045 0104 $5.025.75 $5,000.00 15 $1456.75 Groe Historical Mwm Store Cards and More Almondton General Stone Cricket Shop Cross Stone Unique Klima All Seasons 713 79 167 Hale S. Me De Ave Almond CA 372 Chfonds Gro CA 12 Rising Sun Ave. Come CA TN Pemere Se North GA 945 Gilham st Mew CA 352 Wide All CA $15.00.0045 S7 Son $10.000.00 15 $7,500 45 $15.000 $7.5000 15 SINO $12.00 $124.75 $3.475.90 $93575 SOS 104 FIGURE 2-1 Sample data for TAL Distributors ORDER LINE ORDER NUM ITEMUNUM NUM_ORDERED QUOTED PRICE SMS) 25 51 S1 51610 51610 KITS TRO ORDERS ORDER.NUM ORDER_DATE CUSTOMER_NUM 5160 TOV13/2013 12 5161 HIS 5163 JOVENS 51614 11 51617 TWINS 51619 11/2015 126 51633 INS 5102 10/2015 31618 51617 31617 DII NIN TWS FDNI $12.95 STS SIT 4 3 2 3 SMS 31128 51 $1633 51625 FH24 134 10 $4579 PRICE STOREHOUSE 3 2 1 ITEM ITEM NUM DESCRPTION A1174 S Wood Block Set ple 1.31 Casio Railway Set DIST Cant Star Teaser Mame VI Stocking on FI24 Hele het KATS s. 19 51079 23 SO $13 ON HAND CATEGORY GE 21CM 36TORY 12 TOY 34 PEL GM TONY PL 56 PEL 110 SPIL 32 TOY 75 GHEE 30 TOY 1 $75.00 KLIN MTU NUN TRO TWS Pick Up Sick Zaubersten Bruin Woodlackpl 2 1 1 3 2 2 545.19 $119.75 Fire time STIS 95 FIGURE 2-1 Sample data for TAL Distributors (continued) NOTE The names of columns and tables in this text follow a common naming convention in which column names use uppercase letters and replace spaces between words with underscores. For example, TAL Distributors uses the column named LAST_NAME to store last names and the column named CREDIT_LIMIT to store credit limits. Entities, Attributes, and Relationships There are some terms and concepts that are very important for you to know when working in the database environment. The terms entity, attribute, and relationship are fundamental when discussing databases. An entity is like a noun; it is a person, place, thing, or event. The entities of interest to TAL Distributors, for example, are such things as customers, orders, and sales reps. The entities that are of interest to a school include students, faculty, and classes; a real estate agency is interested in clients, houses, and agents; and a used car dealer is interested in vehicles, customers, and manufacturers. appears on its own line. Using this method, you represent the TAL Distributors database as follows: REP (REP_NUM, LAST_NAME, FIRST_NAME, STREET, CITY, STATE, POSTAL_CODE, COMMISSION, RATE) CUSTOMER (CUSTOMER_NUM, CUSTOMER_NAME, STREET, CITY, STATE, POSTAL_CODE, BALANCE, CREDIT_LIMIT, REP_NUM) ORDERS (ORDER_NUM, ORDER_DATE, CUSTOMER_NUM) ORDER_LINE (ORDER_NUM, ITEM_NUM, NUM_ORDERED, QUOTED_PRICE) ITEM (ITEM_NUM, DESCRIPTION, ON_HAND, CATEGORY, STOREHOUSE, PRICE) Notice that some tables contain columns with duplicate names. For example, the REP_NUM column appears in both the REP table and the CUSTOMER table. Suppose a situation existed wherein someone (or the DBMS) might confuse the two columns. For example, if you write REP_NUM, it is not clear which REP_NUM column you want to use. You need a mechanism for indicating the REP_NUM column to which you are referring, One common approach to solving this problem is to write both the table name and the column name, separated by a period. Thus, you would reference the REP_NUM column in the CUSTOMER table as CUSTOMER. REP_NUM, and the REP_NUM column in the REP table as REP. REP_NUM. Technically, when you reference columns in this format; you say that you qualify the names. It is always acceptable to qualify column names, even when there is no potential for confusion. If confusion might arise, however, it is essential to qualify column names. FUNCTIONAL DEPENDENCE The concept of functional dependence is crucial to understanding the rest of the material in this chapter. Functional dependence is a formal name for what is basically a simple idea. To illustrate functional dependence, suppose that the REP table for TAL Distributors is structured as shown in Figure 24. The only difference between the REP table shown in Figure 2-4 and the one shown in Figure 2-1 is the addition of an extra column named PAY_CLASS. RATE 15 Grove CA 90092 0.06 REP REP LAST FIRST STREET CITY STATE POSTAL COMMISSION PAY NUM NAME NAME CODE CLASS Campos Rafael 724 Vinca Dr. $23.457.501 Gradwy Megan 632 Liatris St. 90085 $11.317.00 2 Tian Hui 1785 Tyler Ave. Northfield CA 90095 $27.789.25 1 Sefton Janet 267 Oakley St. Congare CA $0.001 30 Fullton CA 0.05 45 0.06 60 20097 0.06 FIGURE 2-4 REP table with a PAY_CLASS column