Learn how to analyze real-world applications to extract entities and relationships for a relational data model. Gain an understanding of relational concepts and how they relate to mathematical tuples, sets, and relations. Learn about different types of keys and associations and their uses. Understand the differences between conceptual, logical, and physical data models. See how graphical modeling can be used to visualize data models, and how constraints are used to validate data in tables. Finally, design entity-relationship diagrams for a racing game, such as Mario Kart, and other applications.
Examples
Exercises
Context
The development of a data model typically occurs in multiple stages. As a first step, we analyze the application context and existing usage patterns.
A good starting point for our analysis is to examine existing systems in both analog or digital form, such as forms, files, or spreadsheets. Additionally, we can gain insight from problem descriptions provided by users, interviews, or written requirements.
Based on the sources, we can extract entities, relationships, rules and base data which are required for our data model. Further we can extract technical requirements such as expected overall data size and access frequency and write it down.
| Search for | Example | Result |
|---|---|---|
| Entities | An author can write many books | Entity: Book |
| Relationships | An author can write many books | Relationship: 1:n (author:book) |
| Persons and Roles | An author can write many books | Role or Entity: Author |
| Verbs | An author can write many books | Insert of data (author,book) |
Let's see how an analysis for a database with suppliers and parts can look like (date19).
The following statements were collected from descriptions and interviews with stakeholders**:**
| S1 | A supplier has a name and is located at a city |
|---|---|
| S2 | We sort our preferred suppliers by their status |
| S3 | Each supplier has a unique supplier number |
| S4 | We have a list of parts with names, their color and weight |
| S5 | Parts are stored at a city |
| S6 | Each part has a unique part number |
| S7 | We receive shipments with parts from our suppliers |
| S8 | We want to count the number of parts each supplier has shipped us |
| S9 | We only have one shipment per supplier and part |
| S10 | The status is dependent on the city, each city always has the same status |
What can we learn from this?