BITS Corporation exercises 3-5

poly dent tudent Cou Tacuity student Faculty dente. Se esta Student to Describe the entity su s ulty traculty R um) the relationship between columns that can be u 11. uld these columns lead to more than a s es. Un Under what circumstance entity Subtype? Describe the entity-relationship a composite entity? Describe the approach to model. How are enter s What is a com and w you 's too Desc present cardinality in an E-R diagram a survey form of your own. Faitout might have been complet during the database design Henry Books. For any questions you 100 le information to make a reasonable Guess document at your own school for example in the ument. Using your knowledge of the policies at your school, determine the function the document. Use these dependencies to cre to a set of tables and columns that you could use to proce represented in this moder? 13 Design a survey form 14. Using a doo the attributes present in the functional dependencies present the document designed in Your Tum 6-1 in this chapter. ou may use any of the styles presented in ITS Corporation Exercises allowing exercises are based on the BITS database hoxercise, represent your answer in DBDL and with a diagram You may use any of the i s designed in Your Tum is chapter for the diagram indicate the changes you need to make to the desion of the BITS database to support the following A client is not necessarily represented by a single consultant but can be represented by several consone consultants Indicate the changes you need to make to the desian the BITS database to support the following me There is no relationship between clients and consultants When a client places an order (service request may be performed by any consultant. On the order identity both the client placing the order and the cons responsible for the order. Indicate the changes you need to make to the BITS database design to support the following situation The region where clients are located is divided into territories. For each territory, store the territory number la unique identifier) and territory name. Each consultant is assioned to a single territory. Each client also is assigned to a single territory, but the territory must be the same as the territory to which the client's consultant is assigned Indicate the changes you need to make to the BITS database design to support the following situation The region where clients are located is divided into territories. For each territory, store the territory number la unique identifier) and territory name. Each consultant is assigned to a single territory. Each client also is assigned to a single territory, which may not be the same as the territory to which the client's consultant is assigned 5. Indicate the changes you need to make to the BITS database design to support the following situation. The client address may or may not be the same as the Bill To address on the client's invoice W is called a surmate key or any what is usually hidden from users. When RS returnate le kembering data type, such as the Access Auto Number data type. For exa relation table) for Customer pay ustomer payments stomerum, Payment Date, Payment Amount) wary key cha matie numb she following a better choice is an artificial key. y for a synthetie key), is a system-generated 18 creates a surrogate key, it is usually an T example, suppose you have Because a custe is poem customer can make multiple payments Customer Numannot be the nation of for a customer to make more than one payment on a particular day, the and Payment Date cannot be the primary ya Mdine an artificiales means you would have to assien a Pav i me the custom ate key, such as Payment ID, would make more sense because the e value to each payment. Users do not need to be aware of the Payment o be the primary key. Assuming Customer Numan Payment Numm ind a surrogate key, such as a an artificial key, such as e a unique wyment Num every time the customer makes a payment would make more sense the DBMS will automatic tically re of the Payment ID value, however DATABA To carry out the desi mocation you h no represent alte BASE DESIGN LANGUAGE (DBDL) out the design proces, you must have a mechanism for n ting tables and keys. you have used thus far for representing tables is met de not far enough-them et alternate, secondary. or foreign keys. Because the information level design met e to represent tables with the standardmetation To do so, you will add al features capable of representing additional information One approach to doing this is Design Language (DBDL). Figure 6-1 shows samle DRD documentation for the Employe relational model, it is desirable ditional features capable of me Database Design Langua ables and keys. The standard but it does not go far enough there is no way atlon-level design method is based on nation. One approach to doing this is called documentation for the Employee table. State, Zipcode. lovee (EmployeeNum, LastName, FirstName Street, City WageRate, SocSecNum, Department Num) AK SocSecNum SK LastName, FirstName FK Department Num-Department FIGURE 6-1 DBDL for the Employee table In DBDL, you represent a table by listing all columns and then underlining the primary key. Below the tan definition, you list any alternate keys, secondary keys, and foreion keys, using the abbreviations AK, SR, and respectively. For alternate and secondary keys, you can list the column or collection of columns by name. In case of foreign keys, however, you must also represent the table whose primary key the foreign key must ma In DRDL, you write the foreign key followed by an arrow pointing to the table that the foreign key identifies The rules for defining tables and their keys using DBDL are as follows: Tables (relations), columns attributes), and primary keys are written by first listing the ta name and then, in parentheses, listing the columns that make up the table. The columns make up the primary key are underlined. poly dent tudent Cou Tacuity student Faculty dente. Se esta Student to Describe the entity su s ulty traculty R um) the relationship between columns that can be u 11. uld these columns lead to more than a s es. Un Under what circumstance entity Subtype? Describe the entity-relationship a composite entity? Describe the approach to model. How are enter s What is a com and w you 's too Desc present cardinality in an E-R diagram a survey form of your own. Faitout might have been complet during the database design Henry Books. For any questions you 100 le information to make a reasonable Guess document at your own school for example in the ument. Using your knowledge of the policies at your school, determine the function the document. Use these dependencies to cre to a set of tables and columns that you could use to proce represented in this moder? 13 Design a survey form 14. Using a doo the attributes present in the functional dependencies present the document designed in Your Tum 6-1 in this chapter. ou may use any of the styles presented in ITS Corporation Exercises allowing exercises are based on the BITS database hoxercise, represent your answer in DBDL and with a diagram You may use any of the i s designed in Your Tum is chapter for the diagram indicate the changes you need to make to the desion of the BITS database to support the following A client is not necessarily represented by a single consultant but can be represented by several consone consultants Indicate the changes you need to make to the desian the BITS database to support the following me There is no relationship between clients and consultants When a client places an order (service request may be performed by any consultant. On the order identity both the client placing the order and the cons responsible for the order. Indicate the changes you need to make to the BITS database design to support the following situation The region where clients are located is divided into territories. For each territory, store the territory number la unique identifier) and territory name. Each consultant is assioned to a single territory. Each client also is assigned to a single territory, but the territory must be the same as the territory to which the client's consultant is assigned Indicate the changes you need to make to the BITS database design to support the following situation The region where clients are located is divided into territories. For each territory, store the territory number la unique identifier) and territory name. Each consultant is assigned to a single territory. Each client also is assigned to a single territory, which may not be the same as the territory to which the client's consultant is assigned 5. Indicate the changes you need to make to the BITS database design to support the following situation. The client address may or may not be the same as the Bill To address on the client's invoice W is called a surmate key or any what is usually hidden from users. When RS returnate le kembering data type, such as the Access Auto Number data type. For exa relation table) for Customer pay ustomer payments stomerum, Payment Date, Payment Amount) wary key cha matie numb she following a better choice is an artificial key. y for a synthetie key), is a system-generated 18 creates a surrogate key, it is usually an T example, suppose you have Because a custe is poem customer can make multiple payments Customer Numannot be the nation of for a customer to make more than one payment on a particular day, the and Payment Date cannot be the primary ya Mdine an artificiales means you would have to assien a Pav i me the custom ate key, such as Payment ID, would make more sense because the e value to each payment. Users do not need to be aware of the Payment o be the primary key. Assuming Customer Numan Payment Numm ind a surrogate key, such as a an artificial key, such as e a unique wyment Num every time the customer makes a payment would make more sense the DBMS will automatic tically re of the Payment ID value, however DATABA To carry out the desi mocation you h no represent alte BASE DESIGN LANGUAGE (DBDL) out the design proces, you must have a mechanism for n ting tables and keys. you have used thus far for representing tables is met de not far enough-them et alternate, secondary. or foreign keys. Because the information level design met e to represent tables with the standardmetation To do so, you will add al features capable of representing additional information One approach to doing this is Design Language (DBDL). Figure 6-1 shows samle DRD documentation for the Employe relational model, it is desirable ditional features capable of me Database Design Langua ables and keys. The standard but it does not go far enough there is no way atlon-level design method is based on nation. One approach to doing this is called documentation for the Employee table. State, Zipcode. lovee (EmployeeNum, LastName, FirstName Street, City WageRate, SocSecNum, Department Num) AK SocSecNum SK LastName, FirstName FK Department Num-Department FIGURE 6-1 DBDL for the Employee table In DBDL, you represent a table by listing all columns and then underlining the primary key. Below the tan definition, you list any alternate keys, secondary keys, and foreion keys, using the abbreviations AK, SR, and respectively. For alternate and secondary keys, you can list the column or collection of columns by name. In case of foreign keys, however, you must also represent the table whose primary key the foreign key must ma In DRDL, you write the foreign key followed by an arrow pointing to the table that the foreign key identifies The rules for defining tables and their keys using DBDL are as follows: Tables (relations), columns attributes), and primary keys are written by first listing the ta name and then, in parentheses, listing the columns that make up the table. The columns make up the primary key are underlined