Course 9.1Lesson 2 of 3
Each field stores one kind of data, so each field gets a data type: text, character, Boolean, integer, real or date/time. Each table also needs a primary key, a field whose value is different in every record. You choose suitable data types and pick a suitable primary key for a table.
Try the code and the simulations beside the lesson.
Look at a bus ticket. It has the route name, the price, the date and the time. A price is a number that you can add up. A route name is words. A date is a date. A database stores each field with a data type that suits it.
The syllabus names six basic data types.
M01 or a phone number.B, or a size such as M.For the Members table of the last lesson: MemberID, FirstName, Surname and Town are text, Age is an integer and Paid is a Boolean.
Ask two questions about each field. What do the values look like? What will we do with them?
M01 is text, because it has a letter in it.S, M or L.In SQLite. The database inside Python names the types TEXT, INTEGER and REAL. It stores a Boolean as 1 for TRUE and 0 for FALSE, and it stores a date as text such as 2024-03-14. Written year first, dates sort in the right order. An examination paper may write a date in another form, such as 14/03/2024.
How does the club tell its two members called Patel apart? It uses MemberID. No two members have the same ID.
A primary key is a field whose value is unique: different in every record. It identifies exactly one record. It must never be left empty.
To pick a primary key, look down each field and ask: could two records ever have the same value here?
Surname: no good. Asha and Ravi are both Patel.Town and Age: no good. Riverside and appear twice.FirstName: no repeats today, but a second Tom could join next week.MemberID: made to be unique. This is the primary key.Often no natural field is unique, so we add an ID field, as the vet did with PetID. A field such as a car's registration number is already unique, so it can be the key of a table of cars.
The program builds a small table of cars with Registration as the primary key. Then it tries to add a car with a registration that is already in the table. SQLite refuses it, because a primary key must be unique.
Try each one on your own first. If you are stuck, open a hint. Then check with Show the answer.
ShoeSize that holds values such as and ?HasPet?Postcode holds values such as RV2 7QT. Which data type suits it?FirstName, Surname, DateOfBirth and StudentNumber. Which is the most suitable primary key?Every lesson is open to read. Enrolling also lets you:
Log in to join the discussion.
Loading…
This lesson is free to read. Enroll free to keep your place and take the tests.