Juic'd

Juic'd is a franchise of juice bars (not unlike the real-life Boost juices) that is expanding and decides to implement a new database system.

You have been engaged as the database designer, and you must produce the following deliverables for a database design that will meet the requirements from the initial requirements analysis, which is given below.

  1. An ER-diagram using the conventions used in lectures (following Ramakrishnan & Gehrke) and incorporating appropriate cardinality and participation constraints.

  2. The SQL commands necessary to implement your model as (empty) database tables in an MySQL database.

You must decide what entities and relationships are appropriate.

Requirements

  1. Juic'd customers are all customers with a Juic'd card who receive regular email newsletters and earn Juic'd points on their purchases.
  2. Juic'd has a number of different outlets in popular metropolitan shopping centres, each of the stores has a full-time manager responsible for the performance of that outlet.
  3. Juic'd has a number of other employees, who each work for one or more outlets, but the percentage of time they spend in each particular outlet is fixed.
  4. Each (non-manager) employee has a line-manager who is one of the outlet managers.
  5. These percentages need not add up to 100% since some of these employees are part-time only.
  6. Juic'd sells a range of “mixed juices”, along with additional non-juice items, such as bottles of water, protein balls etc.
  7. The non-juice items are all fixed-size items with a fixed price (e.g. bottle of water = 2 dollars, protein bar = 3 dollars, and so on)
  8. The mixed juices come in three different cup-sizes - either 400ml, 500ml or 600ml.
  9. The mixed juices are made by combining various pure juices and other ingredients (such as coconut water) in any combination that the customer wants - for example, 20% grape juice, 60% watermelon juice and 20% mango juice.
  10. Each of the ingredients (you may assume that all are liquid) has a unit price (a price per ml) and the price of the mixed juice is just the sum of the costs of the ingredients.
  11. A customer places an order, which might have any number of different mixed juices and any number of non-juice items, with an employee at a particular outlet, who makes that order and serves the customer.
  12. Juic'd management wishes to be able to track the sales performance of each outlet on each particular day in order to learn how demand varies over the week (for example, Saturdays might be busy.

Not all of the business requirements can be modelled in an ER-diagram - for example, the requirement that the percentages of ingredients in a mixed juice cup add up to 100% cannot be captured by the diagram. However they can be captured by integrity constraints in the actual running database.

Also notice that the requirements have been simplified from reality - most drastically is the assumption that all customers are known customers, rather than including some "walk-up" customers who would not be tracked.

Written with StackEdit.