Question

Consider the following bike shop database Amy's Bikes is a new bike shop located in a suburb in Ontario, offering a wide range of bicycles and related accessories. Amy, the shop's owner, has been conducting her daily business mostly on paper. She records sales on pre-printed forms. Employee and customer information is maintained on sheets of paper. For each employee, this includes his/her social insurance number, first and last Hame, and home phone number. For each customer, Amy records the first, middle (if any) and last name, as well as at least one phone number and home address (consisting of street number and post code). To keep track of the product, Amy uses a spreadsheet program to record the number, name, price and quantity of the products in stock. For each product, a range of services is offered. The spreadsheet program is also used to list the type (es, repair, exchange) and charge for each service. Multiple types of service may be offered to one product (0., repair and exchange for bikes) and a service may be offered to multiple products (e. repair for bikes and accessories) at different prices. The service is optional and is offered only if the customers opts for it. Each employee of the shop is connected to the product itsells. Amy spends a lot of time maintaining this information. Recently, she has decided to use database to manage all this data. Now to help her draw an ER diagram using crow's footnotation in a better way, answer few questions to help her setup. 1. Identify the relationship between the Employee and Customer? Find the crow's foot notation for both side of the relation. Employee Customer 2. What are primary keys for Employee and Product? 3. Is there a relationship between employee and product based on the information provided in the ER model, answer True/False? Employee Customer 2. What are primary keys for Employee and Product? 3. Is there a relationship between employee and product based on the information provided in the ER model, answer True/False? 4. Suppose a new entity named sales is to be added to the ER model, which entities will it maintain a relation with? Identify, name it and write a potential primary key for the sales entity formed? Mention the Entities Sales has relation: Potential Primary key Name:

Answer 1

This answer explains the relationships between Employee and Customer, identifies the primary keys for Employee and Product, and discusses the possibility of a relationship between Employee and Product. It also suggests the entities with which a new entity named Sales would have a relationship and provides a potential primary key for the Sales entity.

Step-by-step explanation:

1. Relationship between Employee and Customer:
The relationship between Employee and Customer is a one-to-many relationship. This means that one employee can serve multiple customers, but each customer can be served by only one employee. The crow's foot notation for this relationship would be a line with a crow's foot (or three lines) on the Employee side, and a regular line on the Customer side.

2. Primary keys for Employee and Product:
The primary key for the Employee entity would be the social insurance number, as it uniquely identifies each employee. The primary key for the Product entity would depend on how the products are identified. It could be a product ID or a combination of attributes like name and price.

3. Relationship between Employee and Product:
Based on the information provided, there is no direct relationship between Employee and Product in the ER model.

4. Sales entity:
If a new entity named Sales is added to the ER model, it would maintain a relationship with both the Employee and Customer entities. The potential primary key for the Sales entity could be a combination of attributes like sales date and a unique sales ID.