The Student Room Group

Entity Relationship Modelling confusion

In an exam past paper I am asked:
Use Entity Relationship modelling to identify relations, including attributes and indicating primary and foreign keys.
The narrative is as follows:

A Pizza joint stores data on pizza orders. Orders have a unique order id, customer name and consist of one or more pizza requests. For each pizza requested in an order, an identifying number is allocated, and the pizza name and type of base desired is held; optional extra topping details are also stored. Toppings are identified by a topping code and have the name stored.



So, going with the narrative, I figured:

Order(OrderID, CustomerName, PizzaorderNo*)

PizzaOrder(OrderID*, PizzaOrderNo, pizzaName, baseType)

Topping(ToppingCode , ToppingName)

Please note that I have used bold for Primary key, and * for a Foreign Key.
However, in the answers section, it states:
Order(OrderId, CustomerName)

OrderPizza(OrderId*, PizzaOrderNo, PizzaName, DesiredBase)

Topping(ToppingCode, ToppingName)

OrderPizzaTopping(OrderId*, PizzaOrderNo*, ToppingCode*)

I understand that OrderPizzaTopping above is used to denote all the foreign keys, thereby rendering it as a link entity, but I don't understand how to determine where a link entity is required using a narrative such as this.

I understand that the relational model does not like/permit M:N (many-to-many) and so this requires the use of the link entity to decompose this into multiple 1:M or 1:1 relationships.
Is there a hard and fast rule such as, if there is more than 2 primary keys included, a link entity is required?


I am also unsure as to why the answer section has the Order relation as only containing the OrderID and customerName, because the narrative clearly stipulates that "...and consists of one or more pizza requests".

Why then has the pizzarequests been parsed into its own relation and not included in the Order relation? Is it because it contains attributes not directly related to the Order, but which are more pertinent to the order directly(pizzaName, baseType)
Original post by elpresidente90
In an exam past paper I am asked:
Use Entity Relationship modelling to identify relations, including attributes and indicating primary and foreign keys.
The narrative is as follows:

A Pizza joint stores data on pizza orders. Orders have a unique order id, customer name and consist of one or more pizza requests. For each pizza requested in an order, an identifying number is allocated, and the pizza name and type of base desired is held; optional extra topping details are also stored. Toppings are identified by a topping code and have the name stored.



So, going with the narrative, I figured:

Order(OrderID, CustomerName, PizzaorderNo*)

PizzaOrder(OrderID*, PizzaOrderNo, pizzaName, baseType)

Topping(ToppingCode , ToppingName)

Please note that I have used bold for Primary key, and * for a Foreign Key.
However, in the answers section, it states:
Order(OrderId, CustomerName)

OrderPizza(OrderId*, PizzaOrderNo, PizzaName, DesiredBase)

Topping(ToppingCode, ToppingName)

OrderPizzaTopping(OrderId*, PizzaOrderNo*, ToppingCode*)


Are you sure you've written that answer down correctly? If so, the OrderPizza primary key is slightly wrong.

PizzaOrderNo cannot be a primary key by itself because it has duplicates -- Primary keys must be unique, but the data sample you've shown there shows duplicates in the PizzaOrderNo column.

PizzaOrderNo depends upon OrderId, therefore the OrderPizza table's primary key is a Composite primary key consisting of OrderId and PizzaOrderNo together. (OrderId is also a foreign key as well as being part of the OrderPizza composite primary key)

Original post by elpresidente90

I understand that OrderPizzaTopping above is used to denote all the foreign keys, thereby rendering it as a link entity, but I don't understand how to determine where a link entity is required using a narrative such as this.

I understand that the relational model does not like/permit M:N (many-to-many) and so this requires the use of the link entity to decompose this into multiple 1:M or 1:1 relationships.
Is there a hard and fast rule such as, if there is more than 2 primary keys included, a link entity is required?


The hard-and-fast rule is that a foreign key (on a single row) is just a single cell in that row, therefore it can only link to another single row from the joined/linked table, which is why a link table must be used in the Many-to-Many scenario.

To enable 'Many-to-Many' between rows in the Topping table against rows in the OrderPizza table, both sides need to link to multiple rows; which means that neither side can contain a foreign key to the other, because a foreign key can only link to a single row.

That's why the solution is to put both foreign keys inside a "join table" or "link entity", because that table can contain multiple rows which link to any permutation of the rows on either side.

The OrderPizzaTopping for your example data will contain the following Rows:
+---------+--------------+-------------+
| OrderId | PizzaOrderNo | ToppingCode |
+---------+--------------+-------------+
| 1 | 1 | T01 |
| 1 | 2 | T01 |
| 1 | 2 | T02 |
| 2 | 1 | T02 |
| 2 | 1 | T03 |
| 2 | 3 | T02 |
| 2 | 3 | T04 |
+---------+--------------+-------------+

(OrderId 2 with PizzaOrderNo 2 seems to have no toppings so there'd be no rows for that one)



Original post by elpresidente90

I am also unsure as to why the answer section has the Order relation as only containing the OrderID and customerName, because the narrative clearly stipulates that "...and consists of one or more pizza requests".

A foreign key in a row can only join to a single row in the joined/linked table, so if the Order table contained a foreign key for the PizzaOrderNo then it would be impossible for an order to consist of more than one pizza.

The OrderPizza table contains an OrderId as part of its composite primary key, which is also a foreign key back to the Order table, so this allows multiple rows in the OrderPizza table to be joined to the same OrderID

I.e. the OrderPizza table can contain data like this:
+---------+--------------+-------------+--------------+
| OrderId | PizzaOrderNo | Pizza Name | Desired Base |
+---------+--------------+-------------+--------------+
| 1 | 1 | Margherita | Thin Crust |
| 1 | 2 | American | Deep Crust |
| 2 | 1 | Margherita | Deep Crust |
| 2 | 2 | Margherita | Stuffed Crust|
| 2 | 3 | American Hot| Thin Crust |
+---------+--------------+-------------+--------------+


Original post by elpresidente90

Why then has the pizzarequests been parsed into its own relation and not included in the Order relation? Is it because it contains attributes not directly related to the Order, but which are more pertinent to the order directly(pizzaName, baseType)

If an order can consist of multiple pizzas, then an order will contain a single row in the Order table, but multiple rows in the PizzaOrder table - where each PizzaOrder row for a single Order would have different 'pizzaName' and 'baseType'.
(edited 5 years ago)

Quick Reply

Latest

Trending

Trending