In: Computer Science
Create an ERD based on the following business rules and
requirements. Make sure that the ERD...
- Create an ERD based on the following business rules and
requirements. Make sure that the ERD follows good database design
practices covered in Chapter 6. use an online visual paradigm tool
to create the ERD
- NOST offers many different tours. For each tour, the tour name,
approximate length (in hours), and fee charged is needed.
- Tours are classified into five categories: family friendly,
adventure, hiking, camping, and water activities.
- Guides are identified by an employee ID, but the system should
also record a guide’s name, home address, and date of hire.
- Guides take a test to be qualified to lead specific tours. It
is important to know which guides are qualified to lead which tours
and the date that they completed the qualification test for each
tour. A guide may be qualified to lead many different tours. A tour
can have many different qualified guides. New guides may or may not
be qualified to lead any tours, just as a new tour may or may not
have any qualified guides.
- Every tour must be designed to visit at least three locations.
For each location, a name, type, and official description are kept.
Some locations (such as the Hobbiton) are visited by more than one
tour, while others (such as the Glow Worm cave) are visited by a
single tour. All locations are visited by at least one tour.
- When a tour is actually given, that is referred to as an
“outing.” NOST schedules outings well in advance so they can be
advertised and so employees can understand their upcoming work
schedules. A tour can have many scheduled outings, although newly
designed tours may not have any outings scheduled. Each outing is
for a single tour and is scheduled for a particular date and time.
All outings must be associated with a tour. All tours at NOST are
guided tours, so a guide must be assigned to each outing. Each
outing has one and only one guide. Guides are occasionally asked to
lead an outing of a tour even if they are not officially qualified
to lead that tour. Newly hired guides may not have ever been
scheduled to lead any outings.
- Tourists, called “clients” by NOST, pay to join a scheduled
outing. For each client, the name, address, and telephone number
are recorded. Clients may sign up to join many different outings,
and each outing can have many clients. Information is kept only on
clients who have signed up for at least one outing, although newly
scheduled outings may not have any clients signed up yet.
a) Identify all possible entities and relationships. Name all
relationships in both directions, with the exception that the
relationship name may be omitted on a relationship travelling from
an associative or bridging entity.
(b) Resolve any many-to-many relationships.
(c) Identify
(i) strong and weak entities
(ii) identifying and non-identifying relationships.
(d) Identify the main attributes in each entity including all
primary and foreign keys.
(e) Identify the Cardinality and Participation for each
relationship.
logical database design question