a. Design and draw an ER diagram for these application. Specify key attributes of each entity type, and structural constraints on each relationship type. Note any unspecified requirements, and make appropriate assumptions to make the specification complete.
b. And after that Apply Transformation rules to map the obtained ERD into Database schema?
II. For each of the following questions
a. Design and draw an ER diagram for these application. Specify key attributes of each entity type, and structural constraints on each relationship type. Note any unspecified requirements, and make appropriate assumptions to make the specification complete.
b. And after that Apply Transformation rules to map the obtained ERD into Database schema?
12. SALESREP
Create an ERD based on the Crow's Foot notation, using the following requirements:
An INVOICE is written by a SALESREP. Each sales representative can write many invoices, but each invoice
is written by a single sales representative.
The INVOICE is written for a single CUSTOMER. However, each customer can have many invoices.
An INVOICE can include many detail lines (LINE), each of which describes one product bought by the
customer.
The product information is stored in a PRODUCT entity.
The product's vendor information is found in a VENDOR entity.
7. Hudson Engineering Group
The Hudson Engineering Group (HEG) has contacted you to create a conceptual model whose application will
meet the expected database requirements for the company's training program. The HEG administrator gives you
the description (see below) of the training group's operating environment.
(Hint: Some of the following sentences identify the volume of data rather than cardinalities. Can you tell
which ones?)
The HEG has 12 instructors and can handle up to 30 trainees per class. HEG offers five Advanced Technology
courses, each of which may generate several classes. If a class has fewer than 10 trainees, it will be canceled.
Therefore, it is possible for a course not to generate any classes. Each class is taught by one instructo. Each
instructor may teach up to two casses or may be assigned to do research only. Each trainee may take up to
two classes per year.
Given that information
9. The Jonesburgh County Basketball
Conference
The Jonesburgh County Basketball Conference (JCBC) is an amateur basketball association. Each city in the
county has one team as its representative. Each team has a maximum of 12 players and a minimum of 9 players.
Each team also has up to three coaches (offensive, defensive, and physical training coaches). During the season,
each team plays two games (home and visitor) against each of the other teams. Given those conditions, do the
10. Automata Inc.
Automata Inc. produces specialty vehicles by contract. The company operates several departments, each o
which builds a particular vehicle, such as a limousine, a truck, a van, or an RV.
Before a new vehicle is built, the department places an order with the purchasing department to request specifie
components. Automata's purchasing department is interested in creating a database to keep track of orders and
to accelerate the process of delivering materials.
The order received by the purchasing department may contain several different items. An inventory is maintained
so that the most frequently requested items are delivered almost immediately. When an order comes in, it i
checked to determine whether the requested item is in inventory. If an item is not in inventory, it must be ordered
from a supplier. Each item may have several suppliers.
Given that functional description of the processes encountered at Automata's purchasing department, do the
following:
7. Hudson Engineering Group The Hudson Engineering Group (HEG) has contacted you to create a conceptual model whose application will meet the expected database requirements for the company's training program. The HEG administrator gives you the description (see below) of the training group's operating environment. (Hint: Some of the following sentences identify the volume of data rather than cardinalities. Can you tell which ones?) The HEG has 12 instructors and can handle up to 30 trainees per class. HEG offers five Advanced Technology courses, each of which may generate several classes. If a class has fewer than 10 trainees, it will be canceled. Therefore, it is possible for a course not to generate any classes. Each class is taught by one instructor. Each instructor may teach up to two classes or may be assigned to do research only. Each trainee may take up to two classes per year. Given that information, do the following: a. Define all of the entities and relationships. (Use Table 4.4 as your guide.) b. Describe the relationship between instructor and class in terms of connectivity, cardinality, and existence- dependence. 11. medical clinic Using the Crow's Foot notation, create an ERD that can be implemented for a medical clinic, using at least the following business rules: a. A patient can make many appointments with one or more doctors in the clinic, and a doctor can accept appointments with many patients. However, each appointment is made with only one doctor and one patient. b. Emergency cases do not require an appointment. However, for appointment management purposes, an emergency is entered in the appointment book as "unscheduled." c. If kept, an appointment yields a visit with the doctor specified in the appointment. The visit yields a diagnosis and, when appropriate, treatment. d. With each visit, the patient's records are updated to provide a medical history. e. Each patient visit creates a bill. Each patient visit is billed by one doctor, and each doctor can bill many patients. f. Each bill must be paid. However, a bill may be paid in many installments, and a payment may cover more than one bill. 9. A patient may pay the bill directly, or the bill may be the basis for a claim submitted to an insurance company. h. If the bill is paid by an insurance company, the deductible is submitted to the patient for payment. 12. SALESREP Create an ERD based on the Crow's Foot notation, using the following requirements: An INVOICE is written by a SALESREP. Each sales representative can write many invoices, but each invoice is written by a single sales representative. The INVOICE is written for a single CUSTOMER. However, each customer can have many invoices. An INVOICE can include many detail lines (LINE), each of which describes one product bought by the customer. The product information is stored in a PRODUCT entity. The product's vendor information is found in a VENDOR entity. 9. The Jonesburgh County Basketball Conference The Jonesburgh County Basketball Conference (JCBC) is an amateur basketball association. Each city in the county has one team as its representative. Each team has a maximum of 12 players and a minimum of 9 players. Each team also has up to three coaches (offensive, defensive, and physical training coaches). During the season, each team plays two games (home and visitor) against each of the other teams. Given those conditions, do the following: a. Identify the connectivity of each relationship. b. Identify the type of dependency that exists between CITY and TEAM. c. Identify the cardinality between teams and players and between teams and city. d. Identify the dependency between coach and team and between team and player. e. Draw the Chen and Crow's Foot ERDs to represent the JCBC database. f. Draw the UML class diagram to depict the JCBC database. 10. Automata Inc. Automata Inc. produces specialty vehicles by contract. The company operates several departments, each of which builds a particular vehicle, such as a limousine, a truck, a van, or an RV. Before a new vehicle is built, the department places an order with the purchasing department to request specific components. Automata's purchasing department is interested in creating a database to keep track of orders and to accelerate the process of delivering materials. The order received by the purchasing department may contain several different items. An inventory is maintained so that the most frequently requested items are delivered almost immediately. When an order comes in, it is checked to determine whether the requested item is in inventory. If an item is not in inventory, it must be ordered from a supplier. Each item may have several suppliers. Given that functional description of the processes encountered at Automata's purchasing department, do the following: a. Identify all of the main entities. b. Identify all of the relations and connectivities among entities. c. Identify the type of existence dependency in all the relationships. d. Give at least two examples of the types of reports that can be obtained from the database. 9. The Jonesburgh County Basketball Conference The Jonesburgh County Basketball Conference (JCBC) is an amateur basketball association. Each city in the county has one team as its representative. Each team has a maximum of 12 players and a minimum of 9 players. Each team also has up to three coaches (offensive, defensive, and physical training coaches). During the season, each team plays two games (home and visitor) against each of the other teams. Given those conditions, do the following: a. Identify the connectivity of each relationship. b. Identify the type of dependency that exists between CITY and TEAM. c. Identify the cardinality between teams and players and between teams and city. d. Identify the dependency between coach and team and between team and player. e. Draw the Chen and Crow's Foot ERDs to represent the JCBC database. f. Draw the UML class diagram to depict the JCBC database. 10. Automata Inc. Automata Inc. produces specialty vehicles by contract. The company operates several departments, each of which builds a particular vehicle, such as a limousine, a truck, a van, or an RV. Before a new vehicle is built, the department places an order with the purchasing department to request specific components. Automata's purchasing department is interested in creating a database to keep track of orders and to accelerate the process of delivering materials. The order received by the purchasing department may contain several different items. An inventory is maintained so that the most frequently requested items are delivered almost immediately. When an order comes in, it is checked to determine whether the requested item is in inventory. If an item is not in inventory, it must be ordered from a supplier. Each item may have several suppliers. Given that functional description of the processes encountered at Automata's purchasing department, do the following: a. Identify all of the main entities. b. Identify all of the relations and connectivities among entities. c. Identify the type of existence dependency in all the relationships. d. Give at least two examples of the types of reports that can be obtained from the database. 7. Hudson Engineering Group The Hudson Engineering Group (HEG) has contacted you to create a conceptual model whose application will meet the expected database requirements for the company's training program. The HEG administrator gives you the description (see below) of the training group's operating environment. (Hint: Some of the following sentences identify the volume of data rather than cardinalities. Can you tell which ones?) The HEG has 12 instructors and can handle up to 30 trainees per class. HEG offers five Advanced Technology courses, each of which may generate several classes. If a class has fewer than 10 trainees, it will be canceled. Therefore, it is possible for a course not to generate any classes. Each class is taught by one instructor. Each instructor may teach up to two classes or may be assigned to do research only. Each trainee may take up to two classes per year. Given that information, do the following: a. Define all of the entities and relationships. (Use Table 4.4 as your guide.) b. Describe the relationship between instructor and class in terms of connectivity, cardinality, and existence- dependence. 11. medical clinic Using the Crow's Foot notation, create an ERD that can be implemented for a medical clinic, using at least the following business rules: a. A patient can make many appointments with one or more doctors in the clinic, and a doctor can accept appointments with many patients. However, each appointment is made with only one doctor and one patient. b. Emergency cases do not require an appointment. However, for appointment management purposes, an emergency is entered in the appointment book as unscheduled." c. If kept, an appointment yields a visit with the doctor specified in the appointment. The visit yields a diagnosis and, when appropriate, treatment d. With each visit, the patient's records are updated to provide a medical history. e. Each patient visit creates a bill. Each patient visit is billed by one doctor, and each doctor can bill many patients. f. Each bill must be paid. However, a bill may be paid in many installments, and a payment may cover more than one bill. 9. A patient may pay the bill directly, or the bill may be the basis for a claim submitted to an insurance company. h. If the bill is paid by an insurance company, the deductible is submitted to the patient for payment. 12. SALESREP Create an ERD based on the Crow's Foot notation, using the following requirements: An INVOICE is written by a SALESREP. Each sales representative can write many invoices, but each invoice is written by a single sales representative. The INVOICE is written for a single CUSTOMER. However, each customer can have many invoices. An INVOICE can include many detail lines (LINE), each of which describes one product bought by the customer. The product information is stored in a PRODUCT entity. The product's vendor information is found in a VENDOR entity. 7. Hudson Engineering Group The Hudson Engineering Group (HEG) has contacted you to create a conceptual model whose application will meet the expected database requirements for the company's training program. The HEG administrator gives you the description (see below) of the training group's operating environment. (Hint: Some of the following sentences identify the volume of data rather than cardinalities. Can you tell which ones?) The HEG has 12 instructors and can handle up to 30 trainees per class. HEG offers five Advanced Technology courses, each of which may generate several classes. If a class has fewer than 10 trainees, it will be canceled. Therefore, it is possible for a course not to generate any classes. Each class is taught by one instructor. Each instructor may teach up to two classes or may be assigned to do research only. Each trainee may take up to two classes per year. Given that information, do the following: a. Define all of the entities and relationships. (Use Table 4.4 as your guide.) b. Describe the relationship between instructor and class in terms of connectivity, cardinality, and existence- dependence. 11. medical clinic Using the Crow's Foot notation, create an ERD that can be implemented for a medical clinic, using at least the following business rules: a. A patient can make many appointments with one or more doctors in the clinic, and a doctor can accept appointments with many patients. However, each appointment is made with only one doctor and one patient. b. Emergency cases do not require an appointment. However, for appointment management purposes, an emergency is entered in the appointment book as "unscheduled." c. If kept, an appointment yields a visit with the doctor specified in the appointment. The visit yields a diagnosis and, when appropriate, treatment. d. With each visit, the patient's records are updated to provide a medical history. e. Each patient visit creates a bill. Each patient visit is billed by one doctor, and each doctor can bill many patients. f. Each bill must be paid. However, a bill may be paid in many installments, and a payment may cover more than one bill. 9. A patient may pay the bill directly, or the bill may be the basis for a claim submitted to an insurance company. h. If the bill is paid by an insurance company, the deductible is submitted to the patient for payment. 12. SALESREP Create an ERD based on the Crow's Foot notation, using the following requirements: An INVOICE is written by a SALESREP. Each sales representative can write many invoices, but each invoice is written by a single sales representative. The INVOICE is written for a single CUSTOMER. However, each customer can have many invoices. An INVOICE can include many detail lines (LINE), each of which describes one product bought by the customer. The product information is stored in a PRODUCT entity. The product's vendor information is found in a VENDOR entity. 9. The Jonesburgh County Basketball Conference The Jonesburgh County Basketball Conference (JCBC) is an amateur basketball association. Each city in the county has one team as its representative. Each team has a maximum of 12 players and a minimum of 9 players. Each team also has up to three coaches (offensive, defensive, and physical training coaches). During the season, each team plays two games (home and visitor) against each of the other teams. Given those conditions, do the following: a. Identify the connectivity of each relationship. b. Identify the type of dependency that exists between CITY and TEAM. c. Identify the cardinality between teams and players and between teams and city. d. Identify the dependency between coach and team and between team and player. e. Draw the Chen and Crow's Foot ERDs to represent the JCBC database. f. Draw the UML class diagram to depict the JCBC database. 10. Automata Inc. Automata Inc. produces specialty vehicles by contract. The company operates several departments, each of which builds a particular vehicle, such as a limousine, a truck, a van, or an RV. Before a new vehicle is built, the department places an order with the purchasing department to request specific components. Automata's purchasing department is interested in creating a database to keep track of orders and to accelerate the process of delivering materials. The order received by the purchasing department may contain several different items. An inventory is maintained so that the most frequently requested items are delivered almost immediately. When an order comes in, it is checked to determine whether the requested item is in inventory. If an item is not in inventory, it must be ordered from a supplier. Each item may have several suppliers. Given that functional description of the processes encountered at Automata's purchasing department, do the following: a. Identify all of the main entities. b. Identify all of the relations and connectivities among entities. c. Identify the type of existence dependency in all the relationships. d. Give at least two examples of the types of reports that can be obtained from the database. 9. The Jonesburgh County Basketball Conference The Jonesburgh County Basketball Conference (JCBC) is an amateur basketball association. Each city in the county has one team as its representative. Each team has a maximum of 12 players and a minimum of 9 players. Each team also has up to three coaches (offensive, defensive, and physical training coaches). During the season, each team plays two games (home and visitor) against each of the other teams. Given those conditions, do the following: a. Identify the connectivity of each relationship. b. Identify the type of dependency that exists between CITY and TEAM. c. Identify the cardinality between teams and players and between teams and city. d. Identify the dependency between coach and team and between team and player. e. Draw the Chen and Crow's Foot ERDs to represent the JCBC database. f. Draw the UML class diagram to depict the JCBC database. 10. Automata Inc. Automata Inc. produces specialty vehicles by contract. The company operates several departments, each of which builds a particular vehicle, such as a limousine, a truck, a van, or an RV. Before a new vehicle is built, the department places an order with the purchasing department to request specific components. Automata's purchasing department is interested in creating a database to keep track of orders and to accelerate the process of delivering materials. The order received by the purchasing department may contain several different items. An inventory is maintained so that the most frequently requested items are delivered almost immediately. When an order comes in, it is checked to determine whether the requested item is in inventory. If an item is not in inventory, it must be ordered from a supplier. Each item may have several suppliers. Given that functional description of the processes encountered at Automata's purchasing department, do the following: a. Identify all of the main entities. b. Identify all of the relations and connectivities among entities. c. Identify the type of existence dependency in all the relationships. d. Give at least two examples of the types of reports that can be obtained from the database. 7. Hudson Engineering Group The Hudson Engineering Group (HEG) has contacted you to create a conceptual model whose application will meet the expected database requirements for the company's training program. The HEG administrator gives you the description (see below) of the training group's operating environment. (Hint: Some of the following sentences identify the volume of data rather than cardinalities. Can you tell which ones?) The HEG has 12 instructors and can handle up to 30 trainees per class. HEG offers five Advanced Technology courses, each of which may generate several classes. If a class has fewer than 10 trainees, it will be canceled. Therefore, it is possible for a course not to generate any classes. Each class is taught by one instructor. Each instructor may teach up to two classes or may be assigned to do research only. Each trainee may take up to two classes per year. Given that information, do the following: a. Define all of the entities and relationships. (Use Table 4.4 as your guide.) b. Describe the relationship between instructor and class in terms of connectivity, cardinality, and existence- dependence. 11. medical clinic Using the Crow's Foot notation, create an ERD that can be implemented for a medical clinic, using at least the following business rules: a. A patient can make many appointments with one or more doctors in the clinic, and a doctor can accept appointments with many patients. However, each appointment is made with only one doctor and one patient. b. Emergency cases do not require an appointment. However, for appointment management purposes, an emergency is entered in the appointment book as unscheduled." c. If kept, an appointment yields a visit with the doctor specified in the appointment. The visit yields a diagnosis and, when appropriate, treatment d. With each visit, the patient's records are updated to provide a medical history. e. Each patient visit creates a bill. Each patient visit is billed by one doctor, and each doctor can bill many patients. f. Each bill must be paid. However, a bill may be paid in many installments, and a payment may cover more than one bill. 9. A patient may pay the bill directly, or the bill may be the basis for a claim submitted to an insurance company. h. If the bill is paid by an insurance company, the deductible is submitted to the patient for payment. 12. SALESREP Create an ERD based on the Crow's Foot notation, using the following requirements: An INVOICE is written by a SALESREP. Each sales representative can write many invoices, but each invoice is written by a single sales representative. The INVOICE is written for a single CUSTOMER. However, each customer can have many invoices. An INVOICE can include many detail lines (LINE), each of which describes one product bought by the customer. The product information is stored in a PRODUCT entity. The product's vendor information is found in a VENDOR entity
