Rebel Sports Store has a chain of retails stores

Using a case study as an example:

• Rebel Sports Store has a chain of retails stores who sells athletic products such as shirts, shorts, and shoes. Rebel sources its athletic shirts only from Global Athletic Apparel Manufacturer (GAAM) while the other products are from different manufacturers.
• To support the growing store and internet order, Rebel uses a database to ensure that the data is current, accurate and easily accessible.
• In running different retail store (branches), Rebel gathers and organises information about branches, products and manufacturers.
• Each branch has a number the uniquely identifies the branch. In addition, Rebel tracks the branch’s name and its location.
• Each product has a code that uniquely identifies the product. In addition, Rebel tracks the product name, product colour, quantity and price.
• Each manufacturer has a code the uniquely identifies the manufacturer. In addition, each manufacturer has a manufacturer name and location

Identify the entities:

• Entities
  1. Branch
  2. Product
  3. Manufacturer

Identify the attributes:

• Branch
  1. Branch ID
  2. Branch Name
  3. Branch Location
• Manufacturer
  1. Manufacturer ID
  2. Manufacturer Name
  3. Manufacturer Location
• Product
  1. Product ID
  2. Product Name
  3. Product Colour
  4. Product Quantity
  5. Product Price

Identify the relationships:

• A branch sells several products.
• A product can be sold in different branches
• A product is manufactured by only one manufacturer
• A manufacturer makes different products

Entity-Relationship diagram:

PK = Primary Key

Entity-Relationship diagram –expanded:

Rebel Sports wants to keep track of amount of sales per product in each of the branches

• Where do you put the attribute salesAmount?

• If you put it in branch, it means that it tracks the total amount of sales for the branch but not per product.

• If you put it in the product entity, it means it is the total amount of sales of product but not amount of sales of product per branch

Attribute will only exist if there is a relationship

• You place the attribute in the relationship entity

• For example:
  1. ProductPerBranch is a relationship entity (created from converting many-to-many to one-to-many relationships)
  2. We can put the attribute “salesAmount” in this entity

The entity-Relationship diagram is further updated based on the above.

(This space is left blank intentionally)

The BranchID and ProductID are both primary keys

and foreign keys

(This space is left blank intentionally)

Commonly used terms:

ER Model	Relational Model	Database	Traditional Programmer
Entity	Relation	Table	File
Entity Instance	Tuple	Row	Record
Attribute	Attribute	Column	Field
Primary key	Primary key	Primary key	Primary key(or link)