Results 1 to 3 of 3

Thread: ERD help!

  1. #1
    Join Date
    Oct 2009
    Posts
    2

    ERD help!

    I have to make an ERD diagram using this scenario case and I'm not sure if I have all the required tables/fields in my ERD. Is my entities normalized up to 3NF? Are the relationships correct?

    Background

    The Brisbane Driving School was established in Brisbane in 1992. Since then, the School has grown steadily and now has several offices in most of the main suburbs of Brisbane. However, the School is now so large that more and more administrative staff members are being employed to cope with the ever-increasing amount of paperwork. Furthermore, the communication and sharing of information between offices is poor. The Director of the School, Dave Darcy, feels that too many mistakes are being made and that the success of the School will be short-lived if he does not do something to remedy the situation. He knows that a database could help in part to solve the problem and has approached you to help in creating a database system to support the run*ning of the Brisbane Driving School. The Director has provided the following brief description of how the Brisbane Driving School operates.

    The company has now employed over 120 staff. Around 100 of them are driving instructors. Over the years BDS has accumulated over 4000 customers. Around 100,000 lessons have been delivered and around 5000 driving tests arranged.

    How the company operates

    Each office has a Manager (who tends to also be a Senior Instructor), several Senior Instructors, Instructors, and administrative staff. All instructors should possess a valid Queensland Instructor licence. The Manager is responsible for the day-to-day running of the office. Clients must first register at an office and this requires that they complete an application form, which records their personal details. Before the first lesson, a client is requested to attend an interview with an Instructor to assess the needs of the client and to ensure that the client holds a valid provisional driving license. A client is free to ask for a particular Instructor or to request that an Instructor be changed at any stage throughout the process of learning to drive. After the interview, a driving course is agreed and the first lesson is booked. A client may request individual lessons or book a block of lessons for a reduced fee. An individual lesson is for one hour, which begins and ends at the office. A lesson is with a particular Instructor in a particular car at a given time. Lessons can start as early as 8am and as late as 8pm. After each lesson, the Instructor records the progress made by the client and notes the mileage used during the lesson. The School has a pool of cars, which are adapted for the purposes of teaching. Each Instructor is allocated to a particular car. As well as teaching, the Instructors are free to use the cars for personal use. The cars are inspected at regular intervals for faults. Once ready, a client applies for a driving test date. The school will arrange to make the booking. To obtain a driving license the client must pass the road test. It is the responsibility of the Instructor to ensure that the client is best prepared for the test. The Instructor is not responsible for testing the client and is not in the car during the test but should be available to drop off and pick up the client before and after the test at the Driving Test Centre. If a client fails to pass, the Instructor must record the reasons for the failure.

    In this case study you have to design a database that incorporates all the above information and records, and design a user-friendly user interface for data entry staff and driving instructors to update information. Driving instructors are allowed to access and update data for those lessons and driving tests under his/her own name. They are allowed to see their learners’ details but not allow to change make any changes. The data entry staff and the branch manager can see all data and have all rights.
    Here's a screenshot of the ERD diagram
    http://www.lovelyanime.com/erd.gif
    Last edited by andrewst; 10-30-09 at 10:32.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by MuffinKun
    Are the relationships correct?
    You'd do well going through the spec and underlining each bit of data required ie instructor has license etc and then check to see if your ERD has these fields. Then I'd consider any obvious fields which haven't been mentioned ie the address of the school. Then I'd go through and see which entities are linked to other entities - I'd just list them without really thinking too much about it ie schools have a pool of cars. Does your ERD reflect this? Can any of the entities be grouped together into a more general entity but with a type field ie instructor, admin and employee. This will simplify your ERD and your program. This seems quite involved for a homework assignment if you have to produce an application as well - what level of homework is this?

    PS if you just use the quote icon rather than the # icon (which is for code) then we don't have to scroll across the page.

  3. #3
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Muffin

    I would be happy to help, but the link is broken.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

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