Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2015

    Unanswered: ERD help/normalise

    So me and a friend have a case study we want to create a db from. I have been struggling to pick all the correct entities and primary/foreign keys out so i can identify relationships.
    Any help would be greatly appreciated, I can send money through Skrill for anyone who can help if needed.

    What I have so far:
    Click image for larger version. 

Name:	IMG_2657.JPG 
Views:	7 
Size:	672.2 KB 
ID:	16207

    Case study:
    Case Study
    Although ServiceWatch is not a real enterprise, this case study is intended to simulate a real situation and provide a vehicle to help you assimilate and develop all the material taught on this course. It is also intended to develop your practical modelling and problem solving abilities.
    ServiceWatch provides high quality repair and maintenance services for domestic and industrial heating systems. Through its team of experienced engineers, supported by an effective office organisation, ServiceWatch provides a first class service to its customers.
    A team of salespersons is responsible for ensuring that customers have current and appropriate contracts to cover their installations; ServiceWatch engineers will only deal with contract customers and equipment. Customers are very varied in their situations and requirements ranging from private households requiring a single contract to commercial customers with many contracts; a contract being for some or all of the equipment at a single address.

    Results from contacting ServiceWatch staff
    Bob Elrick, the Service Director.
    My role is to run all service operations, that includes the servicing and repair of equipment. Service operations consist of the Help Desk - that's Joe Ackworth and his people, Ashford Myricks and the stock people, and James Cheviot with Joyce Deerhurst and all of the service engineers.
    We only deal with contract situations and equipment listed on our Equipment Type list (Free Standing Boilers, Warm Air Central Heating, etc). Customers may have a number of contracts each listing one or more pieces of equipment; each piece of equipment is given a unique code by ServiceWatch when it is first recorded on a contract. Contracts may be for different lengths of time, they are never amended or renewed; a new contract with a new number is issued in such situations.
    Joe Ackworth, Help Desk Manager.
    I manage all the help desk staff and operations. There are four of us in here, that's me, Sally, Greg and Perminder. I report to Bob Elrick, the Service Director.
    If our customers require a repair they phone us and tell us their contract number, which piece of equipment needs attention and what's wrong with it. We check the details of the customer and equipment against our copy of the contracts file. If it is covered then we fill out a Call Log Report form.
    We agree a visit date with the customer, and whether the visit is to be in the morning or in the afternoon. We fill in the agreed date and time on the Call Log Report form and then pass it over to the engineers' manager for the call to be assigned to an engineer. We always tell the customer the Call Log Report Number so that if they phone again and tell us the number we can trace their details quickly. We also photocopy the forms as well so that we have our own copies.
    Sally Mackie, Help Desk Operator.
    The Call Log Report forms record the progress of jobs from when they are initiated by being logged, to when they are completed. When a customer makes a repair request we fill in the date and time that the request was received at the top of the form. Every request is also given a unique log number. The customer will tell us who they are and which equipment they want us to attend to. We ask them for their contract number and check it against the contracts file, and then we can fill in the contract number and contract line number boxes. We also get the customer to confirm we have their correct name, address and phone number. We write those details on the form too so that the engineer knows where to go and who to see. In the Fault Details section we write down the equipment type, code and description, and what the customer says is wrong with it. Finally, we agree a date for the visit, and whether the visit is to be in the morning or in the afternoon, and then put the Call Log Report form in a tray for someone from Engineering to collect.
    James Cheviot, Engineers' Manager.
    I report to Bob Elrick. I am responsible for appointing and managing the engineers. We have forty of them at present. I also have Joyce Deerhurst who looks after the scheduling of servicing and repair jobs.
    Joyce Deerhurst, Engineers' Administrator.
    I report to Jim Cheviot. I allocate servicing and repair calls to the engineers. I get the three-part Call Log Report forms from the Help Desk and I have to decide which engineer I am going to send out to deal with the service call.
    For each job I write down the name of the engineer who is going to see to the call in the section of the form headed Engineer's Time Record. I file the top copy in the Outstanding Calls File and give the other two to the engineer.
    After doing the job the engineer comes back with the second copy filled in to show what he did. If the job is for a service I record the date on the contract. Percy Proscott then comes and collects both the engineer's copy and the one from my Outstanding Calls File and takes them away.
    Finally I am responsible for initiating service calls for current contracts. I review all current contracts and determine whether equipment is due for service. To do this I look at the contracts to determine when the last service occurred on the various pieces of equipment, then at the Equipment Type list to determine the service frequency. If a service is required I pass a list to Help Desk for them to contact the customer to arrange a visit. They pass a Call Log Report form, marked as 'SERVICE JOB', to me for all arranged services. I process them in the same way as I do the fault jobs.
    A trip out with Jesiah Oakroyd, Service Engineer.
    Each day I pick up the Call Log Report forms for the jobs I am to complete. The Call Log Report form tells me where to go and who to see. If the visit is for a fault, the sheet contains the customer's description of the fault. If the visit is for equipment servicing, the sheet contains the item to be serviced. When I have completed the repair or servicing I have to complete the Call Log Report form with the start and end times of the visit and any parts used. Finally I get the customer's signature and leave them the back copy.
    I return all the forms to the office when I have completed the jobs.
    ServiceWatch ensures that engineers are fully trained for all types of equipment that we might experience. Stock control is excellent with the result that we always complete all of the work on a Call Log Report in one visit.
    Percy Proscot, Completed Calls Administrator.
    I work for James Cheviot. What happens is that when an engineer gets back from a job they will have filled out the Call Log Report form with the completion details. So I go to Joyce Deerhurst's room to get the forms the engineers have brought back, and I match them with the copies from her Outstanding Calls File. I bring both copies back to my office, cross check the details and if they correspond I record the visit date on the copy from Joyce's Outstanding Calls File and file it away. I then check the engineer's copy to ensure that the details are complete and legible, and then forward it D S Singh's Invoicing Department so that they can price it up and make out the invoice for the customer.
    Ashford Myricks, Stock Controller.
    I've been in charge of the parts here for about 16 years now. I make sure we always have enough for what we need. We've got so many different parts now that I don't think many other people could do it. Everybody knows that if they want to know what stock code goes with such and such a piece of equipment then Ash will be able to tell them straight away. I'm the resident expert. I've got things organised just as I want them and everything works just fine.
    I do it by keeping stock record cards. When I notice that the quantity of a particular part is getting low because the engineers have used them I order some more from the equipment manufacturers.

    A round table of the needs of ServiceWatch staff
    Bob Elrick, the Service Director.
    I would like to see what contracts a customer is taking, by date, I'll be able to understandewed, so I can understand why certain customers are not satisfied by our service.
    Joe Ackworth, Help Desk Manager.
    I need to view all call log reports for a customer, to see their recurring problems.
    Sally Mackie, Help Desk Operator.
    I want to see if all engineers are out on a given date, so I don't pencil in a date on a call log when everyone is busy.
    James Cheviot, Engineers' Manager.
    I would like to see the workload of engineers - who is already very busy, so I can schedule visits fairly
    Joyce Deerhurst, Engineers' Administrator.
    There should be a way to find out automatically if a service is due on equipment in a contract.
    Jesiah Oakroyd, Service Engineer.
    Before I go out on a call, I need to see the past call logs on the same contract, in case there is something in the history.
    Ashford Myricks, Stock Controller.
    If I can see how low stock is, and how fast the stock is being used for items, then I'll be much better at the job!

  2. #2
    Join Date
    Mar 2015

    Case study

    Last edited by server2; 03-01-15 at 15:12.

  3. #3
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Quote Originally Posted by server2 View Post

    ...Any help would be greatly appreciated, I can send money through Skrill for anyone who can help if needed...
    Sorry, but dBforums is not a for-hire database consultancy firm. Nor do we do home work assignments!

    We exist to help posters, whether Access newbies or experienced developers, help themselves by solving problems and/or answering questions. What we do not do is to create entire databases, which, I suspect, is why 100 members have read your post but not responded! While some members prefer not to help students, at all, others are willing to help, but only if the student has shown that he or she has made a real effort to solve their problems and only if they ask specific questions! Simply giving a list of proposed Tables/Fields doesn't really constitute 'making a real effort.'

    I would suggest that you need to read your instructor's instructions again, paying particular attention to the parts in red:

    Quote Originally Posted by server2 View Post

    Case Study

    ...Although ServiceWatch is not a real enterprise, this case study is intended to simulate a real situation and provide a vehicle to help you assimilate and develop all the material taught on this course. It is also intended to develop your practical modelling and problem solving abilities...
    Once you start to put some effort into this project, if you run into problems, feel free to post specific questions and I'm sure someone will be glad to assist you.

    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts