From the conceptual model to the relational model
The rules that turn a conceptual model into a relational schema: entity into table, identifier into primary key, relationship into a foreign key or a junction table, and the cases of attribute-carrying, reflexive and ternary relationships.
- 28 cards
- 6 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- The numbers
- Scenarios
- Understand
A few cards from the deck
Relational schema: what is it?
Show the answer
The list of a database's tables, written as NAME(column1, column2, …) with the keys marked.
ORDER(num, date, #customer_id) reads: the ORDER table has three columns; num is the primary key (underlined), customer_id is a foreign key (preceded by #).
CUSTOMER 0,n — PLACE — 1,1 ORDER. Where does the foreign key go in the relational schema?
- In ORDER: ORDER(num, date, #customer_id)
- In CUSTOMER: CUSTOMER(id, name, #order_num)
- In both tables, to make the link in both directions
- In a new table PLACE(#customer_id, #order_num)
Show the answer
In ORDER: ORDER(num, date, #customer_id)
The foreign key goes on the side whose maximum is 1: an order has one customer. In CUSTOMER there would need to be several order numbers per row. A dedicated table is pointless when only one side has n.
Every entity becomes a …, and its identifier becomes the … of that table.
Show the answer
Every entity becomes a table, and its identifier becomes the primary key of that table.
That is the first rule, with no exception: its attributes become the columns, its occurrences the rows.
Rules for moving from the conceptual model to relational: what is it?
Show the answer
A mechanical procedure that turns an entity-relationship diagram into tables: one table per entity, then one more column or one more table per link, depending on the maximums.
For a binary relationship, the pair of maximums decides: one side at 1 gives a foreign key, both sides at n give a table.