Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Create an EER diagram using MySQL Workbench (Crow's Foot Notation) to support the described business scenario and its operations. After, translate the diagram into a
Create an EER diagram using MySQL Workbench (Crow's Foot Notation) to support the described business scenario and its operations. After, translate the diagram into a DB schema consisting of tables and provide screenshots
If you feel that some information, you need when modeling relationships or sub-types, is missing in a question specs, then make reasonable assumptions and state them clearly at the beginning of your answer to the question. If you fell that a question contains contradictory requirements, describe what the contradiction is, and make a reasonable assumption to resolve it (and, of course, describe the contradiction and the assumption (that will resolve the contradiction) at the beginning of vour answer As Workbench does not have notations for composite and multi-valued attributes, note such attributes when describing entities (see below). Furthermore, as Workbench does not provide notation to represent sub-types, you should represent a sub-type in your Workbench ER diagram as an entity with a 1:1 relationship to its super-type. The sub-type entity will have the primary key being the foreign key. For instance, if A is the super-type and B is a sub-type of A, then there is 1:1 relationship between A and B, where one of A and B will have a mandatory membership while the other may be an optional membership #1 List your assumptions and clearly state your assumptions that you made (in addition to the question specs for the question), such as any assumption that you make to determine whether a membership in a relationship is optional or mandatory o As an example, if two entities, EMPLOYEE and DEPARTMENT, have a one-to-many relationship Works-for (from DEPARTMENT to EMPLOYEE) and the question is silent on whether any instance EMPLOYEE must belong to an instance of DEPARTMENT in the relationship Works-for, then you make and describe/state your assumption and state why you made it, such as o Assumption: Every employee must work for a department o Reason: It is needed to determine whether the membership of an EMPLOYEE instance is mandatory or optional in the relationship Works-for between EMPLOYEE and DEPARTMENT entities. Another example is if you have a super-type and its sub-types, such as the super-type EMPLOYEE and sub- types (of EMPLOYEE) being PILOT and ACCOUNTANT. If the question is silent on whether or not each employee must be either a pilot or an accountant, or might not be neither of the two, then you need to make an assumption about it and also state it. Also, if the question does not state whether an employee can be both a pilot and an accountant, then again, you need to make an assumption about it and state that it is to determine whether sub-types are disjoint or not. o #2 Show a screenshot of your diagram o When showing a screenshot of your EER diagram (created using Workbench) in your answer, your entities should not show attributes, but rather should appear as shown here for an entity called book: book To not-show attributes of an entity in a diagram, click on the little triangle at the top right corner of the entity-rectangle " o Show your whole EER diagram. If the screenshot does not show sufficient details (e.g., the crow's feet are not clear enough to show optional/mandatory memberships), then you need to also show its sub-parts as screenshots so that such details are recognizable by the marker o List entities and for each entity also list its attributes. Names of attributes for primary keys should be such that they end with the last two digits of you student ID. For each attribute identify whether it has unigque values, must not have NULL values, " is multi-valued, is composite, in which case list the attributes components. o List super-types and their corresponding sub-types. For any super-type and of its sub-types, you need to state whether sub-types are disjoint or optional and also whether a super-type instance must be or does not have to be any of its sub-types. o List relationships and for each one whether it is 1:1, 1:N, or N:M, and memberships (mandatory or optional). Use the following table to show these properties. You need not show sample data stored in rows/tuples of tables/relation Relationship (El:E2) El Membership E2 Membership Relationship Name Entity E1 Name Entity E2 Name Type N N:M Mand Opt Mand Opt ChairsDep EMP DEP #3 Translate the EER diagram intoa DB scheme consisting of a set of relations/tables and show the list of relations. For each relation, show its list of attribute names. For each o Ifit is a part of the table's primary key, add "PK" in parentheses after its name. o Ifit is a part of a foreign key, add "FK" in parentheses after its name. o If it has unique values, add "UQ" in parentheses after its name. If its value cannot be NULL, add o If its value cannot be NULL, add "NN" in parentheses after its name. after its name. If an attribute (could be a composite one) is a primary key, it is assumed that no NULL values may appear in it and that the values are unique (i.e., you need not put UQ or NN for primary key attributes) Question Specifications: A friend of yours is opening the Professional Electronics and Repairs shop to repair smartphones, laptops, tablets, and other smart devices. She wants you to create a database to help her run her business. Create a Crow's Foot notation ERD to support the following business operations: When a customer brings a device to PEAR for repair, data must be recorded about the customer, the device, and the repair. The customer's name, address, and contact phone numbers must be recorded (if the customer has used the shop before, the information already in the system for the customer is verified as being current). For the device to be repaired, the type of device, model, and serial number are recorded (or verified if the device is already in the system). Only customers who have brought devices into the shop for repair will be included in this system. Since a customer might sell an older device to somcone else who then brings the device to the shop for repair, it is possible for a device to be brought in for repair by more than one customer. However, each repair is associated with only one customer. When a customer brings in a device to be fixed, it is referred to as a repair request, or just "repair," for short. Each repair request is given a reference number, which is recorded in the system along with the date of the request, and a description of the problem(s) that the customer wants fixed. It is possible for a device to be brought to thoe shop for repair many different times, and only devices that are brought in for repair are recorded in the system. Each repair request is for the repair of one or more devices. There are a limited number of repair services that the shop can perform. For each repair service, there is a service ID number, description, time required, rate, and charge. "Charge" is how much the customer is charged for the shop to perform the service and is calculated by the time required times rate, plus costs of parts required for the service. The actual repair of a device is the performance of the services that were required to to address the problems described by the customer. Completing a repair request may require the performance of many services. Each service can be performed many different times during the repair of different devices, but each service will be performed only once during a given repair request. Any repair eventually requires the performance of at least one service, but which services will be required may not be known at the time the repair request is Some services involve only labor activities and no parts are required, but most services require the replacement of one or more parts. How many of cach part are required for a specific service is specified when the service is entered into the system. For cach part, the part number, part description, quantity in stock, and cost per unit is recorded in the system. Some parts may be used in more than one service If you feel that some information, you need when modeling relationships or sub-types, is missing in a question specs, then make reasonable assumptions and state them clearly at the beginning of your answer to the question. If you fell that a question contains contradictory requirements, describe what the contradiction is, and make a reasonable assumption to resolve it (and, of course, describe the contradiction and the assumption (that will resolve the contradiction) at the beginning of vour answer As Workbench does not have notations for composite and multi-valued attributes, note such attributes when describing entities (see below). Furthermore, as Workbench does not provide notation to represent sub-types, you should represent a sub-type in your Workbench ER diagram as an entity with a 1:1 relationship to its super-type. The sub-type entity will have the primary key being the foreign key. For instance, if A is the super-type and B is a sub-type of A, then there is 1:1 relationship between A and B, where one of A and B will have a mandatory membership while the other may be an optional membership #1 List your assumptions and clearly state your assumptions that you made (in addition to the question specs for the question), such as any assumption that you make to determine whether a membership in a relationship is optional or mandatory o As an example, if two entities, EMPLOYEE and DEPARTMENT, have a one-to-many relationship Works-for (from DEPARTMENT to EMPLOYEE) and the question is silent on whether any instance EMPLOYEE must belong to an instance of DEPARTMENT in the relationship Works-for, then you make and describe/state your assumption and state why you made it, such as o Assumption: Every employee must work for a department o Reason: It is needed to determine whether the membership of an EMPLOYEE instance is mandatory or optional in the relationship Works-for between EMPLOYEE and DEPARTMENT entities. Another example is if you have a super-type and its sub-types, such as the super-type EMPLOYEE and sub- types (of EMPLOYEE) being PILOT and ACCOUNTANT. If the question is silent on whether or not each employee must be either a pilot or an accountant, or might not be neither of the two, then you need to make an assumption about it and also state it. Also, if the question does not state whether an employee can be both a pilot and an accountant, then again, you need to make an assumption about it and state that it is to determine whether sub-types are disjoint or not. o #2 Show a screenshot of your diagram o When showing a screenshot of your EER diagram (created using Workbench) in your answer, your entities should not show attributes, but rather should appear as shown here for an entity called book: book To not-show attributes of an entity in a diagram, click on the little triangle at the top right corner of the entity-rectangle " o Show your whole EER diagram. If the screenshot does not show sufficient details (e.g., the crow's feet are not clear enough to show optional/mandatory memberships), then you need to also show its sub-parts as screenshots so that such details are recognizable by the marker o List entities and for each entity also list its attributes. Names of attributes for primary keys should be such that they end with the last two digits of you student ID. For each attribute identify whether it has unigque values, must not have NULL values, " is multi-valued, is composite, in which case list the attributes components. o List super-types and their corresponding sub-types. For any super-type and of its sub-types, you need to state whether sub-types are disjoint or optional and also whether a super-type instance must be or does not have to be any of its sub-types. o List relationships and for each one whether it is 1:1, 1:N, or N:M, and memberships (mandatory or optional). Use the following table to show these properties. You need not show sample data stored in rows/tuples of tables/relation Relationship (El:E2) El Membership E2 Membership Relationship Name Entity E1 Name Entity E2 Name Type N N:M Mand Opt Mand Opt ChairsDep EMP DEP #3 Translate the EER diagram intoa DB scheme consisting of a set of relations/tables and show the list of relations. For each relation, show its list of attribute names. For each o Ifit is a part of the table's primary key, add "PK" in parentheses after its name. o Ifit is a part of a foreign key, add "FK" in parentheses after its name. o If it has unique values, add "UQ" in parentheses after its name. If its value cannot be NULL, add o If its value cannot be NULL, add "NN" in parentheses after its name. after its name. If an attribute (could be a composite one) is a primary key, it is assumed that no NULL values may appear in it and that the values are unique (i.e., you need not put UQ or NN for primary key attributes) Question Specifications: A friend of yours is opening the Professional Electronics and Repairs shop to repair smartphones, laptops, tablets, and other smart devices. She wants you to create a database to help her run her business. Create a Crow's Foot notation ERD to support the following business operations: When a customer brings a device to PEAR for repair, data must be recorded about the customer, the device, and the repair. The customer's name, address, and contact phone numbers must be recorded (if the customer has used the shop before, the information already in the system for the customer is verified as being current). For the device to be repaired, the type of device, model, and serial number are recorded (or verified if the device is already in the system). Only customers who have brought devices into the shop for repair will be included in this system. Since a customer might sell an older device to somcone else who then brings the device to the shop for repair, it is possible for a device to be brought in for repair by more than one customer. However, each repair is associated with only one customer. When a customer brings in a device to be fixed, it is referred to as a repair request, or just "repair," for short. Each repair request is given a reference number, which is recorded in the system along with the date of the request, and a description of the problem(s) that the customer wants fixed. It is possible for a device to be brought to thoe shop for repair many different times, and only devices that are brought in for repair are recorded in the system. Each repair request is for the repair of one or more devices. There are a limited number of repair services that the shop can perform. For each repair service, there is a service ID number, description, time required, rate, and charge. "Charge" is how much the customer is charged for the shop to perform the service and is calculated by the time required times rate, plus costs of parts required for the service. The actual repair of a device is the performance of the services that were required to to address the problems described by the customer. Completing a repair request may require the performance of many services. Each service can be performed many different times during the repair of different devices, but each service will be performed only once during a given repair request. Any repair eventually requires the performance of at least one service, but which services will be required may not be known at the time the repair request is Some services involve only labor activities and no parts are required, but most services require the replacement of one or more parts. How many of cach part are required for a specific service is specified when the service is entered into the system. For cach part, the part number, part description, quantity in stock, and cost per unit is recorded in the system. Some parts may be used in more than one service
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started