Identify main entities and attributes, including the primary keys. Document and describe the steps you used to identify the entities, attributes.
Mike's Shuttle Services, our case-study business, is a new shuttle business. The business
started small with Mike transporting customers to key locations in Cape Town such as Cape
Town International Airport regularly. In spite of a lot of competition, his business has
steadily grown and he acquired a few shuttle mini-buses recently. He applied for all the
necessary licenses and was approved to he can now legally operate a shuttle business. He
subsequently also reached out and co-opted the services of a shuttle drivers. His aim is of
course to let his vehicles do as many trips as possible, so his drivers are on a schedule to
rotate daily.
Mike has been conducting his daily business on paper and on his cell phone. He records
bookings he receive on paper so that he can schedule the vehicles and drivers. Mike spends a
lot of time maintaining all of this data and his current process is error prone because the
scheduling of trips and customers, as well as vehicles and drivers, is a complicated process.
He managed to acquire a second hand computer to assist him but at this stage he uses it
mainly to play games, write letters, and browse the internet and social media. The only
business-related task he performs on the computer is an attempt to keep track of the vehicles
and their necessary maintenance and roadworthiness using a spreadsheet program. This is
done because he is worried about the roadworthiness of the vehicles and the fact that they
could be impounded by traffic police if they are not roadworthy, especially when being used
to transport the public.
Recently, Mike learned that using a database would be a good way to store and work with
data related to his business. Using a database would greatly diminish the amount of time he
currently spends maintaining his data, and he could always ensure that the data is up-to-date
and that the information is accurate. Although he thinks a database is a good idea, he's aware
of the fact that he doesn't know the first thing about properly designing a database.
Undaunted, Mike has decided to hire a database consultant to design the database for him.
You are, in this story, the consultant Mike hired for the project. You completed your
Computer Science degree at UWC, and you are very enthusiastic to use your database design
and programming skills to develop a solution for Mike’s business.
You captured the following initial business rules:
• Each of the shuttle vehicles are used for several trips each day.
• All trips are round trips and trips depart from the depot (at Mike’s premises) at a
specific time towards a final destination such as Cape own International Airport, the
V&A Waterfront or a hospital (e.g. Groote Schuur).
• A trip follows a specific route and picks up customers along the way at pre-arranged
pick-up points.
• The information about pick-up times are available and customers then book trips and
indicate their pick-up point at a specific time. They do this primarily via WhatsApp
and Mike keep a record on paper which he gives to drivers when they depart from the
depot. This list indicates which customers must be picked up at which pick-up point.
• Sometimes a customer can book a special pick-up at extra cost if the shuttle can
accommodate this request. These special cases would include an ill or disabled patient
that has to go to hospital.
• Drivers work in a shift – a shift starts at a specific time when the shuttle and driver
depart at the depot and then when they return. There is a compulsory rest time for
drivers between shifts (minimum 2 hours), so sometimes drivers may work more than
one shift a day. Shift durations depend on the length of the trip as well as how busy
the route normally is.
• Sometimes a shift may include more than one trip, sometimes only one, it depends on
the distance of the trip.
• A shift implies that a driver is assigned a shuttle vehicle for that shift, so a driver may
be assigned to more than one shuttle a day and a shuttle may have more than one
driver a day.
You know that these are initial business rules, and that they will be refined as the project
matures. For now you will use these initial business rules to develop an first version ERD,
and make assumptions if something is not yet clear.
In your answers to the questions below – document all your assumptions when necessary, e.g.
the use of primary keys, or implied attributes such as Driver’s driver license.
- Identify main entities and attributes, including the primary keys. Document and describe the steps you used to identify the entities, attributes.
- Identify the relationships from the business rules:
- Indicate the relationships (PK/FK) and the connectivity using Crow’s Foot notation. First describe the relationship and then indicate the relationship in the ER diagram.
- Include at least one many-to-many relationship.
- Given the relationships you identified, define an optional and mandatory relationship and identify one of each in your model if possible.
Step by step
Solved in 3 steps
