The conceptual data model
The full approach, from business rules to the entity-relationship diagram: the three levels (conceptual, logical, physical) and the five classic faults of a conceptual model.
- 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 steps
- Scenarios
- Understand
A few cards from the deck
Conceptual data model (MCD): what is it?
Show the answer
A diagram that describes the data of a domain and its meaning with entities, relationships and cardinalities, saying nothing of the technology that will store it.
It can be discussed with the client without talking about tables or a DBMS. The same conceptual diagram can become a PostgreSQL, SQLite or other database.
Rule: "a screening shows only one film". The conceptual model shows SCREENING 0,n — SHOW — 1,1 FILM. What is the fault?
- Swapped cardinalities: it needs 1,1 on SCREENING and 0,n on FILM
- Cardinality too weak: it needs 1,n on SCREENING and 1,1 on FILM
- Extra entity: FILM should be a plain attribute of SCREENING
- Wrong minimum: it needs 0,1 on FILM, since a film may not be shown
Show the answer
Swapped cardinalities: it needs 1,1 on SCREENING and 0,n on FILM
As drawn, a screening could show several films and a film would have a single screening. The rule demands the opposite: the maximum 1 goes next to SCREENING, the n next to FILM.
The three model levels, from the most abstract to the most concrete, are the …, the … and the ….
Show the answer
The three model levels, from the most abstract to the most concrete, are the conceptual, the logical and the physical.
The conceptual says what (entities, relationships), the logical says how in relational terms (tables, keys), the physical says how in a DBMS (types, indexes).
Logical data model (MLD): what is it?
Show the answer
The translation of the conceptual model into the model of a type of DBMS: here tables, primary keys and foreign keys.
It is where foreign keys appear, which do not exist at the conceptual level: this is the step where a link is represented by a column.
Building a conceptual data model: which step comes after "Collect the business rules and draw up the data dictionary"?
- Spot the relationships and their attributes
- Place the cardinalities of the legs
- Spot the entities and their identifiers
- Check the diagram against every business rule
Show the answer
Spot the entities and their identifiers
Start from the written need, list the data, sort it into entities, link them, quantify the links, then reread the diagram rule by rule: a rule with no trace in the diagram, or an element with no rule, is a fault.