Question

In: Computer Science

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 needs this support. Employees in their first year usually don’t have vendors to support, as they are learning the ropes, but as they work there longer and more vendors are reached, they may be assigned their only vendor. The owner wants to track employee names and email addresses, as well as vendor names and locations.

Vendors provide products to AthFleet, like shoes, apparel, and equipment. Each product they sell comes from only one vendor. Some products are made in house, but if a vendor is in the system they must have provided a product. The owner wants to track product names and each product’s unique SKU. Finally, customers purchase products from AthFleet and that transactional data needs to be recorded. Each product is often purchased by lots of customers, especially if it is popular. Customers are only put in the system if they have made a purchase, but a product does not need to have been purchased to be stored. The owner wants to keep track of customer names and email addresses.

Solutions

Expert Solution

Components used in the creation of an ERD:

Entity – A person, place or thing about which we want to collect and store multiple instances of data. It has a name, which is a noun, and attributes which describe the data we are interested in storing. It also has an identifier, which uniquely identifies one instance of an entity. The attribute which acts as the identifier is marked with an asterisk.

          [b2]

Relationship – Illustrates an association between two entities. It has a name which is a verb. It also has cardinality and modality.

         [b3]

Cardinality and Modality are the indicators of the business rules around a relationship. Cardinality refers to the maximum number of times an instance in one entity can be associated with instances in the related entity. Modality refers to the minimum number of times an instance in one entity can be associated with an instance in the related entity.

Cardinality can be 1 or Many and the symbol is placed on the outside ends of the relationship line, closest to the entity, Modality can be 1 or 0 and the symbol is placed on the inside, next to the cardinality symbol. For a cardinality of 1 a straight line is drawn. For a cardinality of Many a foot with three toes is drawn. For a modality of 1 a straight line is drawn. For a modality of 0 a circle is drawn.

         zero or more [b4]

          1 or more [b5]

           1 and only 1 (exactly 1) [b6]

           zero or 1 [b7]

Cardinality and modality are indicated at both ends of the relationship line. Once this has been done, the relationships are read as being 1 to 1 (1:1), 1 to many (1:M), or many to many (M:M).

                                    1:1

[b8]

                        1:M

[b9]

                        M:M

[b10]

                        1:M

[b11]

Typically, ERDs are much more complex than this, involving quite a number of entities and relationships. If we join all of the above relationships together and add a few attributes, a small collection of data might be depicted in the following way using Crow’s Foot Notation:

Consider the following business rules for a patient appointment system:

A doctor can be scheduled for many appointments, but may not have any scheduled at all. Each appointment is scheduled with exactly 1 doctor. A patient can schedule 1 or more appointments. One appointment is scheduled with exactly 1 patient. An appointment must generate exactly 1 bill, a bill is generated by only 1 appointment. One payment is applied to exactly 1 bill, and 1 bill can be paid off over time by several payments. A bill can be outstanding, having nothing yet paid on it at all. One patient can make many payments, but a single payment is made by only 1 patient. Some patients are insured by an insurance company. If they are insured, they can only carry insurance with one company. An insurance compnay can have many patients carry their policies. For patients that carry insurance, the insurance company will make payments, each single payment is made by exactly 1 insurance company.

Given the above information, the following ERD can be drawn:

Intersection Entities

Intersection entities are used in the resolution of a many to many relationship. This resolution is done in order to store additional information which doesn’t fit into the attribute list of either entity in the M:M relationship. For instance, in the instructor-course example given earlier, there is a M:M relationship between Course and Section. A course can have many sections, and a section can have many courses. If we want to know how many students are registered in a particular class, we cannot store the information on either entity. We could store the total number of students registered in all sections of a particular course, in the course entity, ie: 300 students are registered in CS270, all sections included. We could store the total number of students registered in all courses with a particular section number, in the section entity, ie: 25,000 students are registered in a section numbered 002. If we want more practical information, such as how many students are registered in section 002 of CS270, we need another entity in which to store the information. So, we create an intersection entity, and place it between its related entities with the additional attributes. The M:M relationship becomes two 1:M relationships. See resolution below.

Independent Entities

Independent entities are those which can exist without another entity. Student, seat, instructor etc. are examples of independent entities.

Dependent Entities

Dependent entities rely upon the existence of other entities. Some of their attributes are taken from the identifiers in the entities upon which they depend. Class is an example of a dependent entity. It relies upon course and section, and gets two of its attributes from their identifiers.


Related Solutions

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...
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...
Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy (EER) if...
Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy (EER) 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 a full-time inspector, then the date of certification, the renewal date for that certification, and the yearly salary should also be recorded in the system. If an employee is a part-time inspector, then the date of...
ERD Lucid chart with Crow's foot notation for the following. include all entities, attributes, and correct...
ERD Lucid chart with Crow's foot notation for the following. include all entities, attributes, and correct carnality in relationships between entities. primary and foreign keys. You have been asked to build a database for a sportswear company called Ath Fleet. The company owner needs to keep track of the customers that buy their products, the employees that work at Ath Fleet, the vendors that provide them with products, and the products themselves. Each vendor has contact with one employee representative...
Use following case and create Entity-Relationship Diagrams using the Crow’s Feet method for each. Each ERD...
Use following case and create Entity-Relationship Diagrams using the Crow’s Feet method for each. Each ERD should be completed on a separate sheet of paper, if drawn by hand. If additional assumptions are made for any of your 2cases, be sure to document the additional business rules and include them with your ERDs. Entity-Relationship Diagram : TEXTBOOK REVIEWS Your website would like to add the ability for school BIT students to provide reviews for textbooks they have previously used. Using...
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...
1. Create an ERD that has at least 5 tables in it and include all of...
1. Create an ERD that has at least 5 tables in it and include all of the details and the correct notation (Min/Max cardinality and relationship name.) 2. Write a paragraph that explains the ERD. 3. Write the SQL Create, Insert, and Update statement to insert one of the entities from this ERD into a database.
Given the information below, create the fully labeled Crow's Foot ERD using a specialization hierarchy where...
Given the information below, create the fully labeled Crow's Foot ERD using a specialization hierarchy where appropriate (use Visio). The ERD must contain all primary keys, foreign keys, and main attributes. Business rules are defined as follows: At a hospital, each patient is classified as either a resident patient or an outpatient. All patients have common attributes - ID, first and last name, and diagnosis - and unique attributes that are specific to their groups. The list of such attributes...
Draw the ERD in Chen notation corresponding to the following spec: Sailors are identified by an...
Draw the ERD in Chen notation corresponding to the following spec: Sailors are identified by an sid, having a name, age, and rating Boats are identified by a bid and also have a name and a color. Sailors reserve boats on a given date. Every sailor reserves a boat at some time
Based on the following situation,draw a complete Entity Relationship Diagram using theCrow’s Foot notation...
Based on the following situation, draw a complete Entity Relationship Diagram using theCrow’s Foot notation which includes:All entities and attributes Relationships Connectivity and relationship participation (4.5 Marks)Primary and foreign keys (3.5 Marks)A lecturer in a university can manage multiple projects. But, it is not compulsory for a lecturer to manage a project. Each project is managed by only one lecturer. Lecturer will have a staff number, a name, a rank, and a research specialty. Projects have a project number, a...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT