A shoebox of paper stamp cards
The Kettle Lane shop gives each customer a paper card. A stamp goes on it for every pound spent. Ten stamps win a free drink.
The owner loses cards, forgets stamps and can not tell who her best customers are. She wants a better way to keep the data.
Key words
| Word | What it means | Example |
|---|---|---|
| Decomposition | breaking a big problem into smaller parts you can solve one at a time | |
| Entity | a thing the system keeps facts about | a customer |
| Attribute | one fact about an entity | a customer's name |
| Primary key | an attribute whose value is different for every row | |
| Foreign key | an attribute that holds the primary key of another entity, to link them | |
| Relationship | a link between two entities | a customer makes purchases |
| Database | an organised collection of data, kept in linked tables |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can pick the entities, attributes and keys out of a brief, and say how a database improves on the current way.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
This is practice
Important
The real task comes from the exam board. This lesson uses an invented organisation and its own made-up data. Your teacher tells you how the real tasks are set and what you may bring.
Four steps through a brief
- Read the brief and list the things it talks about. Those are the entities.
- For each, list the facts the owner needs to keep. Those are the attributes.
- Choose what tells one from another. That is the key.
- Look at the verbs. They are the relationships.
This is decomposition: one big job becomes four small ones.
Worked example: Kettle Lane
| Entity | Attributes | Key | Relationship |
|---|---|---|---|
| Customer | Name, town, date joined | customer_id | A customer makes many purchases |
| Purchase | Date, total spent | purchase_id | A purchase belongs to one customer |
| Item | Name, price | item_code | An item appears on many purchase lines |
| Reward | Name, stamps needed | reward_id | A customer wins many rewards |
The cells students fill in are left blank in this preview.
Say how it is better
The task asks for a design that improves on the current way. Name what is wrong now, then say what the design does about it.