Who has the book?
A school library has 5,000 books and 800 students. Aisha borrows two books. Sam borrows one.
How can the library know who has which book, and when it is due back?
First you decide what data you need and how the parts connect. You draw it. This drawing is a data model.
Key words
| Word | What it means | Example |
|---|---|---|
| Database | An organised store of data that programs can search and change. | |
| Data model | A picture of the data you need and how it is organised. | |
| Entity | A thing you keep data about. | Student, Book |
| Attribute | One fact about an entity. | name, title |
| Relationship | A link between two entities. | A student borrows a book |
| Primary key | A value that is different for every row. It finds one row. | student_id |
| Foreign key | A column that holds the primary key of another table. | loan.student_id |
| Data type | The kind of value a column holds, such as whole numbers or text. | |
| Integer | A whole number with no decimal point. | 42 |
| Index | A list that helps the database find rows fast. | |
| Hierarchical model | Data organised as a tree. Each item has one parent. | |
| Relational model | Data in tables that are linked by keys. | |
| E-R model | Entity Relationship model. A diagram of entities and their links. | |
| UML | Unified Modelling Language. A standard way to draw designs, including classes. |
The cells students fill in are left blank in this preview.
Why draw before you build?
A building needs a plan before the work starts. Data is the same.
If you find a mistake in a drawing, you change the drawing. If you find a mistake after you built the database, you must change the data too. That costs a lot more.
A data model shows the data you need and how it is organised.
Quick check: entity or attribute?
Have a go
Quick check: entity or attribute?
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
How to read an E-R diagram
Each box is an entity. Each line is a relationship.
The marks at the end of a line say how many. || means exactly one. o{ means zero or many.
In the diagrams below, one student makes zero or many loans. One book is in zero or many loans. This is a one-to-many relationship. The loan table holds both foreign keys.
Three levels of data model
| Level | Who it is for | What it shows | Library example |
|---|---|---|---|
| Conceptual | Customers and managers | The big picture: entities and relationships. No detail. | Student borrows Book |
| Logical | Designers | Tables, attributes and keys. Not tied to one database product. | Student(student_id, name, year) |
| Physical | Developers | The real design for one database: data types, sizes, indexes. | student_id INTEGER PRIMARY KEY |
The cells students fill in are left blank in this preview.
Read the big picture
Have a go
Read the big picture
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.