Based on the following Data Attribute Dictionary and Interview Transcript, develop an entity-relationship diagram. To better clarify the relationships among entities, you may read the copy of interview transcript attached below. If you discover while drawing the diagram that the scenario is incomplete, make up reasonable explanations to complete the story. Supply your explanations along with the diagram.

Note: Each entity must have entity name, attributes, and primary key (identifier) Some entities may not have primary key. If you want, you can create the primary key of those entities. If necessary, you can add additional attributes. Place maximum and minimum cardinality to each relationship. Data Attribute Dictionary Below is a Data Attribute Dictionary that contains all the attributes and definitions.

Note: the attributes below are listed in alphabetical order, not by entity.

Attribute Description Address A 30-character alphanumeric field holding the clients street or box address. Barcode A 20-character alphanumeric barcode ID that is either pre-printed on each component package or added via a label at Coastline Consulting. City A 20-character alphanumeric field holding the name of the clients city. ClientID A system-generated large integer numeric value unique to each client. ClientName A 50-character alphanumeric field holding the clients company name. ComponentType A 10-character alphanumeric classification of the type of component. ConfigurationID A system-generated large integer numeric value unique to each software configuration record. ContactFirstName A 20-character alphanumeric field consisting of the first name of the client contact person. ContactLastName A 30-character alphanumeric field consisting of last name of the client contact person. DateInstalled Date field consisting of the date a component was installed into a piece of equipment. DatePurch A date field representing the date a component was purchased.

The following is a copy of the transcript of an interview conducted by Anna Kelly with IT consultant Jeff Summers and receptionist/bookkeeper Kathy Gray of Coastline Systems Consulting. This interview transcript helps you to clarify the relationships among entities. The goal of this interview was to obtain sample forms and to ask questions about them to discover data entities of the system.

Scene: The meeting room at Coastline Systems Consulting. Anna Kelly scheduled the interview to obtain instructions and sample forms for designing the data structure for the customer response system. Jeff: Good morning, Anna! Anna: Good morning, Jeff. Good morning, Kathy. Thanks for taking the meeting. Jeff: You requested some samples of the forms we use now out on site. Here are copies of the main forms I think will be relevant. Anna: Great! That will be a big help. I think you have received copies of the use case glossary, diagram, and narratives. The use cases and those forms you brought will be guiding our discussion in this meeting. What I want to accomplish is to get answers on some questions I have concerning the data requirements. Jeff: The first form is the PC Configuration Sheet. This is just a spreadsheet that we currently use to keep track of equipment in each PC. We build one of these sheets for each client where we service hardware and keep it in our disorganized binders. Anna: OK. Are these columns all the pieces of information that need to be tracked for each PC? Jeff: I dont think this whole format works very well. A few years ago we had to change the name of the CD-ROM column to CD/DVD when DVD drives started getting popular. Anna: Today, we may need a column for mouse as we are getting all kinds of specialty mice and other pointing devices on the market. Jeff: We may need a column for web cam, also. But the point is that we dont want to be restructuring the data every time theres a technology shift. Also, we have a problem with this format in that it doesnt allow for multiple hard drives or multiple CD ROMs. That happens pretty often. Anna: I see. So we ought to move away from having specific components as fields. Jeff: That's what I think. For each machine, we should be able to enter any number of components. And for each component, we should be able to select a component type from a list and then fill in the detailed information. Anna: When I was talking to Ben and Doug about it the other week, they thought we could use barcodes to speed the entry process and tie the information back to when we check it into inventory. Jeff: I can see how that would help. As you can see from the spreadsheet, some of our data is pretty sketchy. A barcode would tie each entry to a specify model number. Anna: I've never worked with barcodes in an information system. Jeff: I have. Every barcode symbol is associated with a numeric or alpha-numeric identifier. The identifier is printed just above or below the barcode symbol, so you can actually see what it is. When you scan the barcode, that identifier is entered into the computer just as if someone had typed the identifier on the keyboard. Often the identifier is the serial number of the component, so every one is unique. Anna: I see. How long are those identifiers? Jeff: It varies. But I think 20 characters should be sufficient. Anna: But then we would end up with the equivalent of this spreadsheet filled with identifiers. That would be less informative than what we have already. That's where that Check In Inventory use case comes in. Kathy, how do you check in inventory now? Kathy: I have an Access database. I type in model number, a description of the item, quantity, date purchased, and the vendor. Anna: Not the purchase price? Kathy: No. That information is in the accounting system. But it isn't relevant to inventory. Anna: OK. We would need those same pieces of information. Plus we would need you to scan the barcode. Kathy: Sounds like more work. Anna: An extra second or two to scan the barcode. But I remember about a month ago you had to dig up a list of all Teac DVD+RW drives brought into inventory over a three month period. Kathy: Oh, yes. What a hassle that was! That was to see if a particular drive was still under warranty. Anna: Well, I think this new system could eliminate those searches. We would tie every installed component to a specific purchase date with the barcode. Kathy: Then that would be well work the extra second. But I heard Jeff say that "often" the identifier is a unique serial number. What about cases where it's just a model number? That wouldn't be unique and so we couldn't tie the installed component to a specific purchase date. Anna: I'll put that on my open issues list to check out. Worst-case scenario is that we put our own barcode on those items. We could generate a whole list of unique numbers and print barcode labels for them. It would be an extra step to apply those labels... Kathy: But still worth it in the long run. Anna: I'm glad you agree. What else do we have to talk about with the component end of the system? Jeff: Well, lets say I replace the video card. I know what the PC now has. But I dont know what it had before, how long that component was in service. Anna: So you want a history of each PC. Jeff: From a component standpoint, I just need to see a list of all components that have ever been in the PC, when those components were added, and when they were removed. Anna: Its not that Im questioning your processes, but why do you need to know about components that are no longer in a PC? Jeff: For one thing, clients like us to tell them about PCs that are causing problems over and over. Another reason is so on continuing problems we can see what was tried before. Anna: That makes sense. So we don't want to just write over the information of the old component with the information of the new component. We want to keep adding to the list with an installed date and a removed date. Jeff: For things such as RAM, I need to track a quantity, too. Anna: OK. Given the changes you want, I think we ought to define the word component. Jeff: Good question. You have to think about how we buy and upgrade. Sometimes we buy a complete system with CPU, monitor, mouse, keyboard the works in one package. Anna: If you buy it as one unit, do you get all the detailed component information and enter it to the columns? Jeff: No, because if we bought it as a unit we let the vendor service it as a unit under warranty. In those cases a complete system would be a single component. But then later we upgrade a hard drive, add RAM, replace something, etc. A replacement NIC can be a separate component. Anna: I know we custom build some PCs. What about them? Jeff: A PC that we build from individual parts is all components. Anna: So, if everything is a component, is there any information that pertains to the PC in general? Jeff: Yes. First of all, this PC Configuration Sheet doesnt show it, but we need to track whether we are talking about a PC, a printer, a router, a hub or some other kind of technology equipment. Anna: We service all those? Jeff: If by service, you mean repair, then no. But if you mean make sure it is operational and handle sending it in for warranty work, then yes. Anna: So I should call it equipment instead of PC. What else do we track about each piece of equipment? Jeff: Each piece of equipment is given a name. We let the users name their own machines. Sometimes they change them. Also, everything has an In Service Date. And, of course, we track which client owns the equipment. Anna: I notice this sheet tracks the user. Jeff: Yeah. But tracking the user just doesnt work. We dont get informed of personnel turnover. So we go into an office months later and cant find the people we have on file. Thats why we have started using a numeric ID for each piece of equipment. We just have it printed on a sticker that we attach to the machine. Anna: I think that covers the equipment and component questions. What about the software configurations? Jeff: I brought along some sample information [Exhibit 4.3]. This isn't all the kinds of configuration information we track. But it is representative. Anna: Walk me through this, if you would. Jeff: We see the client name at the top: Family Vacation Rentals. All this configuration information pertains to them. The DSL IP is simple. They have a DSL line, and this is the static IP address for it. The other items are more complex. You see we have a LAN IP for the network server. We also have the administrator username and password for that server. Then we have the LAN IP and username/passwords for three different logins for the SQL Server machine. We have a little configuration information for the network router, also it's LAN IP and username/password. There should be more information on the router, such as the DHCP range, port forwarding information, etc. Anna: Can't you just view all that information on the router once you login? Jeff: Sure. Unless the router dies. Then we need to have it documented. Anna: Got it. I can think of several ways to structure this data. Could we just dump all this information into a memo type of field? Jeff: I've brought you a short one. Some of these configuration lists are pages and pages. We desperately need some organization so it can be searched. Anna: So one big memo field isn't a good idea. I'll have to think through the pros and cons of various implementations. That leaves the service requests. Unfortunately we dont have any forms for that, do we? Kathy: Not unless you count sticky notes and e-mails. Anna: So lets approach it this way: What information do you need to communicate to a tech when a service request comes in? Kathy: Well, which client it is, of course. Also a description of the problem and the person reporting the problem. Jeff: If it deals with a particular machine, we need that, too. But not all do. Some deal with web hosting or software. Kathy: And the date when the request comes in. Sometimes that is a point of contention. Anna: All right. Then we need to track the work a technician does on a request. What would that look like? Jeff: Well, we would want the date of that work and the technician's initials and kind of a memo pad for notes. Anna: Earlier we had talked about a mechanism for marking requests as resolved. So we need that tracked. My opinion is a resolution date field would give us better information in the long run than a resolution checkbox. Jeff: Makes sense. We had also talked about an automatic resolution mechanism after a tech does work. So we need a FinishTime field. We just as well have a StartTime field, too. Anna: OK. This system is about client service requests and client equipment and client configurations. What kind of general information do we need to keep on each client? Kathy: They are all companies with company names. And we always have a primary contact person. Jeff: I dont know how many times Ive needed a client phone number or e-mail address or mailing address. I would say we should have all that data handy in the system. Anna: Thats a good point, Jeff. Wow. Im glad I talked with you two. This is giving me lots of good information. In fact, I think I have everything I need now to design the data. Jeff: Were just glad youre working on this system. It sounds like a lifesaver. Anna: Well, thanks for your time. You have both been a big help.