MYP Design Databases and Data Handling Notes

A Database Is Records and Fields in a Grid

1. A record is everything stored about one thing, such as one student or one library book.
2. A field is one piece of that record, such as surname, year group or date borrowed.
3. A table holds all the records of one kind, drawn as a grid with rows for records and columns for fields.
4. Every field is given a data type, so date_borrowed holds a date and copies_held holds an integer.
5. Deciding your fields before you type in any data saves a rebuild, because adding a field later means going back through every record.

A Primary Key Names One Record and Only One

1. A primary key is the field whose value is different in every single record.
2. Names make bad keys, because two students called Sofia Martinez will eventually turn up in the same year group.
3. Schools use an admission number and shops use a product code, both invented for no other reason than being unique.
4. A primary key can never be left blank, so a record without one cannot be stored at all.
5. Keys are also what let two tables point at each other, which is the whole basis of a relational database.

Flat File or Relational: Where the Duplication Goes

1. A flat file database is a single table, which is exactly what one spreadsheet is.
2. It works well while the data stays simple, such as 30 rows of name, form and locker number.
3. It falls apart as soon as something repeats, since storing a teacher's room number against each of their 120 students stores that room 120 times.
4. Move that teacher to a different room and you have 120 edits, and missing one leaves the database contradicting itself.
5. A relational database splits the data into linked tables, so the room number is stored once in a Teachers table.
6. You trade a little more setup at the start for far less duplication and far fewer contradictions later.

Two Tables, One Relationship

1. Take a school clubs system, which needs one table for Members and one for Clubs.
2. Members holds member_id as its primary key, plus name and year group.
3. Clubs holds club_id as its primary key, plus the club name and the day it meets.
4. Adding a club_id field to Members creates a foreign key, which is a field pointing at the primary key of another table.
5. That is a one to many relationship, since one club has many members while each member here sits in one club.
6. Changing chess club from Tuesday to Thursday is then a single edit in Clubs, and every member sees the new day.

Queries and Sorting Pull Out What You Need

1. A query asks the database for the records matching a condition and hands back a smaller table.
2. Every query answers three things: which fields you want, which table they are in, and what the condition is.
3. Sorting puts the answer in order, so ORDER BY year DESC lists the oldest year groups first.
4. Conditions join with AND and OR in exactly the way the conditions in your code do.
5. A query never changes the stored data, so you can run one as often as you like while you are testing.
6. Saving your useful queries is worth doing, since a club register is the same query run every week.

Validation Stops Rubbish Getting In

1. Validation is a rule the database checks before it will accept a value.
2. A range check rejects anything outside sensible limits, such as a year group below 1 or above 13.
3. A type check refuses letters in a field that is meant to hold a number.
4. A presence check insists a field is not left empty, which every primary key needs.
5. A format check tests the shape of the entry, such as an email address containing an @ or a postcode matching a pattern.
6. Validation cannot tell whether data is true, so a year group of 9 passes every check even when the student is in year 10.

Storing People's Data Responsibly

1. Collect only the fields your design genuinely uses.
2. Say what you are collecting and why on the form itself, before anyone types anything.
3. Ask permission for anything that identifies a person, and treat photographs of students as identifying data.
4. Keep the data only as long as the project needs it, then delete it and record in your folder that you did.
5. Use made up test data for screenshots, so your ePortfolio shows member 1042 rather than a classmate's real name.
6. These decisions belong in Evaluating, where you judge your solution against a specification point you wrote about privacy.