Question

Starting a Family Video, This is an SQL Assingment

Each family video has a store number, a street address, city, state, zipcode, and phone number. Each store will have a number of employees. Each staff member should have an employee number, name, salary, phone number. Each store should have a single employee that is the manager for that store.

assume that every DVD is a movie. Every movie will have a title and a single category (Action, Drama, Horrow, SciFi, etc.). Any given store will have any number of DVD copies of a movie. The rental cost is set for the movie and is the same across all stores.

Family Video track of all borrowers (borrower number,name, phone number). A borrower is allowed to rent from any store, and may rent any number of items, but each rented item must record the day it was rented and its return date.

1. Identify and list the strong entities.
2. Identify and list the weak entities.
3. For each entity list its attributes.
4. Identify and list the relationships between entities. If the relationship has any attributes, list them.
5. Draw the E/R diagram for your model.

Answer 1

you1) A strong entity is not dependent of any other entity in the schema. A strong entity will always have a primary key. Strong entities are represented by a single rectangle. The relationship of two strong entities is represented by a single diamond.

Therefore strong entity are

1.store

2.employee

2)A weak entity is dependent on a strong entity to ensure the its existence. Unlike a strong entity, a weak entity does not have any primary key. It instead has a partial discriminator key. A weak entity is represented by a double rectangle.

Weak entity are

1. Video track
2. Family video
3. Movie

3) Entity followed by attributes:

Videotrack(store_number,name_customer,rented_date,due_date)

Employee (employee_number,name, phone number, salary)

Store(store_number, employee_number)

Family video( store_number,street,city,state,zipcode,phonenumber)

Movie(category,title)

4)

• Familyvideo has N-N RELATIONSHIP WITH STORE
• Family video has N-N RELATIONSHIP WITH Movie
• Store HAS N-N RELATIONSHIP WITH Employee
• Store has one manager who is an employee
• Store has 1-1 RELATIONSHIP with video track

5) refer image