Question

The Rapid Response Refurbishment Company is a large company whose sole job is to refurbish consignments of faulty electronics goods for major manufacturers. The Company currently operates 50 Refurb_Centers. The company is is overhauling its information system, a necessary step after the computer the records were held on died irrecoverably in April.

The new system will be created using a traditional relational database system. You have been called in as a contractor to design the database. Your first task is to create the conceptual design which will be a conceptual entity relationship diagram (ERD).

You will take the information presented herein and represent it as a set of entity types and their primary keys and some major attributes and the relationships between the entity types (not instances).

The following narrative was provided by the CEO Tractatus Querulous.

The company was formed in 1997 and has grown substantially. As you probably know we have 50Refurb_Centers. Basically one in every state even Alaska. We deal with large consignments of faulty goods. Manufacturers outsource the refurb Projects to us instead of having to set up new facilities to do the jobs themselves. We contract with manufacturers to take on refurbishment Projects.

Each Refurb_Centers handles multiple Projects and each Project is assigned exclusively to one specificRefurb_Center based on proximity to the manufacturer and current workload.

Each Project consists of multiple Items and each Item belongs to just one Project. To repair a single Item often requires many different Tasks (Disassembly, Diagnostics, repair, reassembly and so on). Each Task is uniquely identified and applies to a single Item.

Tasks of course require someone to do them, often several someones. Each Task may require the services of many Technicians and of course each Technician will do many Tasks. This is one of those problematic many to many relationships. For this exercise you are allowed to show many to many relationships.

Tasks of course also require tools, materials, or other equipment to do them, often several pieces. For simplicity we refer to all of these things as Kit. Each Task may require many bits of Kit and of course each bit of Kit may be used on many Tasks. This is another one of those annoying Many to Many relationships. For this exercise you are allowed to show many to many relationships.

Each bit of Kit has a single Supplier and each Supplier supplies us with many bits of Kit.

To keep our techs honest we assign each and every bit of Kit (down to reels of copper wire or packets of solder) to one specific Technician. Each technician will have many bits of kit assigned to them. We call Toby Esterhase "Sargent Solder" as he always has about a ton of the stuff on hand, but don't expect to borrow any of it from him, he is as tight as a gnats chuff.

To make sure our techs are both properly skilled and up to date about the items they refurbish we make sure they all attend 2 or 3 Training_Courses per year. Each Training_Course involves between 5 and 20 Techs and each Tech as mentioned attends several courses. Guess what this is? Yep, tis yet another many to many relationships. For this exercise you are allowed to show many to many relationships.

Part One

You will create a conceptual ERD. The ERD will show a rectangle for each entity type which will be split in two. The top half will include the Entity type name as a singular noun so Technician not Technicians (points will be deducted for every mistake) .

The bottom half will list the attribute(s) used to create the primary key (guarantee uniqueness for each instance). As the CEO has given you few clues you may exercise your creativity, if you can find a natural set of attributes that guarantee uniqueness use them if not define your own primary key.

Relationships between entities will be shown by a line with a relationship name and an arrow

pointing towards the "possessed" or object entity for instance

Part two

The ERD is the first part - the second part is a list of important non-key attributes for each entity (plus the primary key), for instance for an entity called Necromancer you might list it thusly…

Necromancer

Necromancer_ID{PK} Fname Sname Dateofbirth Datewentinsane Dateofdeath Dateofreturn Street City State Country Planet

However, for this exercise have no more than about 6 non-key attributes for each entity type (plus the primary key {pk} as above, but you must list some attributes for all entity types if you come up with an entity type and you cannot think of any attributes for it that's a big hint that it probably is not a terrific entity type after all. The challenge here is to come up with a set of important attributes that we will want to know about each entity instance

As this is a conceptual ERD do not create Foreign Keys

You may use any diagramming tool to create the ERD and attributes lists as long as I can read them.

Far and away the easiest tool for this job is creaky old PowerPoint which can draw rectangles, lines, triangles, and text boxes dead easy and stuff can be easily scaled. PP Hints coming soon…

A Huge helpful Hint

Long before you even draw the first box in PowerPoint (or whatever you use) you are strongly advised to scope out your design on paper, yes good old wood pulp, cleaned, bleached, compressed, dried then shaped into very thin rectangles…

Answer 1

ERD for given data base:

Part 1:

There are 8 entities in the diagram :

1. Refurb_center entity
2. Project entity
3. Training_Course entity
4. Technician entity
5. Item entity
6. Task entity
7. Kit entity
8. Supplier entity

Relationships:

1. There are Handle relationship between Refurb_center entity and Project entity.
2. There are Belongs_To relationship between Item entity and Project entity.
3. There are repair relationship between Item entity and Task entity.
4. There are require relationship between Kit entity and Task entity.
5. There are Services relationship between Technician entity and Task entity.
6. There are Attend relationship between Technician entity and Training_Course  entity.
7. There are assign relationship between Kit entity and Technician entity.
8. There are has relationship between Kit entity and Supplier entity.

Multiplicity:

1. There are 1:M multiplicity between Refurb_center entity and Project entity.
2. There are M:1 multiplicity between Item entity and Project entity.
3. There are 1:M multiplicity between Item entity and Task entity.
4. There are M:M multiplicity between Kit entity and Task entity.
5. There are M:M multiplicity between Technician entity and Task entity.
6. There are M:M multiplicity between Technician entity and Training_Course  entity.
7. There are M:M multiplicity between Kit entity and Technician entity.
8. There are M:1 multiplicity between Kit entity and Supplier entity.

ERD :

Part 2:

Attributes of entities are :

1) Refurb_center entity

Refurb_center location (PK) phone_no email state city ZIPCODE

2) Project entity

Project Project_ID (PK) Project_name description

3) Training_Course entity

Training_Course Course_ID (PK) Course_name duration Validity

4) Technician entity

Technician Technician_ID name phone_no address

5) Item entity

Item Item_ID (PK) Item_name Item_type

6) Task entity

Task Task_ID (PK) Task_Type

7) Kit entity

Kit tool_id (PK) material equipments

8) Supplier entity

Supplier Supplier_ID name phone_no address

Thank you...............