Databases Question
INFO 364 Assignment 2 Deadline: 11:59 PM, EDT, Wednesday, March 27th, 2024 Submission Rules: 1. Submit a digital copy of your answer to the Canvas folder for Assignment 2. 2. Only answers and question numbers, such as Q1 (a), should be included. Do not include THE questions themselves. 3. Name your file as: “INFO_364_HW2_FirstName_MiddleName (if applicable) _LastName”. 4. Submit one single PDF or Word file only. Do not split your submission into two files. 5. Sumit before the deadline. No extension will be granted. No late submission will be accepted. Q1: Draw an ER diagram for the following situations using LucidChart. a) Companies, identified by Company ID and described by Company Name and Industry Type, hire consultants, identified by Consultant ID, and described by Consultant Name and Consultant Specialty, which is multivalued. Assume that a consultant can work for only one company at a time, and we need to track only current consulting engagements. Draw an ERD for this situation. Now, consider a new attribute, Hourly Rate, which is the rate a consultant charges a company for each hour of his or her services. Redraw the ERD to include this new attribute. Now, consider that each time a consultant works for a company, a contract is written describing the terms for this consulting engagement. Contract is identified by a composite identifier of Company ID, Consultant ID, and Contract Date. Assuming that a consultant can still work for only one company at a time, redraw the ERD for this new situation that accounts for the history of employment. Did you move any attributes to different entity types in this situation? b) A company called Emerging Electric wishes to create a database with the following entities and attributes: a. Customer, with attributes Customer ID, Name, Address (Street, City, State, Zip Code), and Telephone. b. Location, with attributes Location ID, Address (Street, City, State, Zip Code), and Type (values of Business or Residential) c. Rate, with attributes Rate Class and RatePerKWH. After interviews with the owners, you have come up with the following business rules: 1) Customers can have one or more locations. 2) Each location has two rate classes, the peak rate class and the off-peak rate class. Draw an ERD for this situation and place minimum and maximum cardinalities on the ERD. Q2: Draw an EER diagram for the following situation. A non-profit organization depends on several different types of persons for its successful operation. The organization is interested in the following attributes for all of these persons: SSN, Name, Address, (City/State/Zip), and Telephone. Three types of persons are of greatest interest: employees, volunteers, and donors. Employees have only a Date Hired attribute, and volunteers have an Available Day and a Skill attribute, both of which are multivalued. Donors have a Birthday attribute, which allows the organization to send annual thank-you letters. Donors also participate in a relationship (named Donates) with an Item entity type. A donor must have donated one or more items, and an item may have one or more donors. 1 There are persons other than employees, volunteers, and donors who are of interest to the organization so that a person need not belong to any of these three groups. On the other hand, at a given time a person may belong to two or more of these groups (e.g., employee and donor). Q3: Develop an EER diagram for the following situation. A technology company provides offerings to its customers. Offerings are of two separate types: products and services. Offerings are identified by an offering ID and an attribute of description. In addition, products are described by product name, standard price, and date of first release; services are described by name of the company’s unit responsible for the service and conditions of service. There are repair, maintenance, and other types of services. A repair service has a cost and is the repair of some products; a maintenance service has an hourly rate. Fortunately, some products never require repair. However, there are many potential repair services for a product. A customer may purchase an offering, and the company needs to keep track of when the offering was purchased and the contact person for that offering with the customer. Unfortunately, not all offerings are purchased. Customers are identified by customer ID and have descriptive data of name, address, and phone number. When a service is performed, that service is billed to some customer. Because some customers purchase offerings for their clients, a customer may be billed for services he or she did not purchase, as well as for ones that were purchased. When a customer is billed for a service (although some may never require a service of any type), the company needs to keep track of the date the service was performed, the date the bill is due, and the amount due. Q4: The following table contains sample data for parts and for vendors who supply those parts. In discussing these data with users, we find that Part No (but not Description) uniquely identifies parts, and that Vendor Name uniquely identifies vendors. Answer the following questions. a) Convert this table to a relation (a data table that fulfills the requirements for a relation). You can name this relation PART SUPPLIER and create such a table in Excel or Word. b) List the functional dependencies in the newly created relation PART SUPPLIER (Your answer should follow the format: The determinant → The attribute(s) the determinant determines. Note that the determinant can be a combination of attributes.) c) In what normal form is your newly created relation, PART SUPPLIER? Develop a set of 3NF (third normal form) relations from PART SUPPLIER. Make sure that you indicate the referencing relationships between the foreign keys and the primary keys. Q5: The following figure shows an EER diagram for Vacation Property Rentals. This organization rents properties in several states. As shown in the figure, there are two basic types of properties: beach properties and mountain properties. 2 a) Transform the EER diagram to a set of relations following the rules we discussed in class. b) Determine the normal form for each of the relations. c) Convert all relations to 3NF (third normal form) if it is not in 3NF. Make sure that you indicate the referencing relationships between the foreign keys and the primary keys. 3
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
