Question

In: Computer Science

Create an ERD based on the following business rules and requirements. Make sure that the ERD...

  1. 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

Solutions

Expert Solution

E-R Diagram-

  • This Er model has 6 main entities-
  1. Guides
  2. client
  3. tour
  4. outing
  5. locations
  6. qualification test
  • This Er model has has been converted into 3NF
  • Here each entity and its attributes are in tabular form
  • Data types of each attribute has been mentioned with it
  • The primary key as PK and foriegn key as FK has been mentioned there.

If any doubt, please feel free to ask and comment, i would like to answer them.
Thank you


Related Solutions

Phase 1 – ERD In this phase of the project you will create an ERD based...
Phase 1 – ERD In this phase of the project you will create an ERD based upon the following requirements and business rules. Limit your ERD to entities and relationships based on the business rules showed here. In other words, do not add realismto your design by expanding or refining the business rules.  However, make sure you include all attributes needed that would permit the model to be successfully implemented, including all primary and foreign keys. 1.Trinity College (TC) is divided...
Develop an ERD. If you make additional assumptions, document them as business rules. all entities, relationships,...
Develop an ERD. If you make additional assumptions, document them as business rules. all entities, relationships, primary keys, foreign keys and weak entities must be labeled. You have been asked to develop a database to help the Wisconsin Snowmobile Association track their club members. The following are the predefined business rules: 1)Snowmobile Clubs are tracked by Wisconsin county. 2)A club can be assigned to multiple counties. 3)Members can belong to one or more clubs. 4)Some clubs could have the same...
Use information from text to create (ERD) Entity Relationship Diagram . Make sure relationship sentences are...
Use information from text to create (ERD) Entity Relationship Diagram . Make sure relationship sentences are consistent with relationship in ERD. Video Store (AVS) runs a series of fairly standard video stores: • Every customer must have a valid AVS customer card in order to rent a video. Customers rent videos for three days at a time. Every time a cus- tomer rents a video, the system must ensure that he or she does not have any overdue videos. If...
Use the following business rules to draw the fully labeled Crow's Foot ERD in Visio. The...
Use the following business rules to draw the fully labeled Crow's Foot ERD in Visio. The diagram must include all entities, attributes, and relationships. Primary keys and foreign keys must be clearly identified on the diagram. Write all appropriate connectivities and cardinalities on the ERD. a). A company has many divisions. Each division operates many departments, but each department is operated by one division. b). A department employs many employees but not more than 100. c). Each employee is employed...
Create an ERD (using Crow’s Foot Notation) for the following description. Be sure to include all...
Create an ERD (using Crow’s Foot Notation) for the following description. Be sure to include all necessary entities, attributes, and correct cardinality in relationships between entities. You have been asked to build a database for a sportswear company called AthFleet. The company owner needs to keep track of the customers that buy their products, the employees that work at AthFleet, the vendors that provide them with products, and the products themselves. Each vendor has contact with one employee representative and...
Create a Crow’s Foot notation ERD to support the following business operations: Create a Crowsfoot notation...
Create a Crow’s Foot notation ERD to support the following business operations: Create a Crowsfoot notation ERD to support the following business operations: - A friend of yours has opened Marshfield Electronics and Repairs (MER) to repair smartphones, laptops, tablets, and MP3 players. She wants you to create a database to help her run her business. When a customer brings a device to MER for repair, data must be recorded about the customer, the device, and the repair (also referred...
I need a list of business rules and corresponding ERD for a simple, very basic Pet...
I need a list of business rules and corresponding ERD for a simple, very basic Pet Store. Don’t think Petco or PetSmart, think little shop with only one location. They sell puppies, kittens, fish, and some reptiles. They also sell fancy food, toys, and beds for all these pets. Basically, the store sells things (2 entities), by whom (entity), to who (entity), and how (entity). I need two things: First, information containing the operation and business rules for a simple...
case study 1.    Draw an Entity Relationship Diagram (ERD) for the following problem. Make sure you identify...
case study 1.    Draw an Entity Relationship Diagram (ERD) for the following problem. Make sure you identify correct relationships, attributes, and identifiers (keys). 2.    Implement the database design into tables using an industrial-strength database management system (e.g. Oracle SQL+). case: HELP is a voluntary organization that provides aid to people. Based on the following brief description of operations, create the appropriate fully labeled Crow’s Foot ERD. Individuals volunteer their time to carry out the tasks of the organization. For each volunteer, their...
use the business model canvas to create four canvases on different business concepts. Make sure to...
use the business model canvas to create four canvases on different business concepts. Make sure to include the following in the margins as per the video. 1)Key Trends 2) Industry Trends 3) Market Forces 4) Marco-economic forces
Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy if appropriate....
Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy if appropriate. Two-Bit Drilling Company keeps information on employees and their insurance dependents. Each employee has an employee number, name, date of hire, and title. If an employee is an inspector, then the date of certification and the renewal date for that certification should also be recorded in the system. For all employees, the Social Security number and dependent names should be kept. All dependents must...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT