Computer Science GCSE
Databases & SQL
Databases are everywhere.
Websites, stores, businesses, schools - databases are one of the key applications in modern computing. You could argue that they form the absolute basis of much of what we consider as modern ICT.
Major online retailers, such as Amazon or eBay, organise their entire catalogues as databases. That's one reason why it's so easy to search and find products you're interested in. iTunes and Spotify organise music as databases so that they can be searched. On a different level, the databases of players in games such as Fifa or Football Manager are crucial to the success of the games.
And just imagine: how would your cell phone work without databases?
How databases work
A database is a structured collection of data. It is easiest to imagine them as being organised in tables. The rows and columns of the table provide the structure for the database and help keep the data organised effectively.
Here's a database of animals that we have in the Blue Square Zoo:
| Name | Species | Age | Gender | Height (m) |
|---|---|---|---|---|
| Marg | Giraffe | 12 | F | 4.7 |
| Molly | Giraffe | 6 | F | 4.4 |
| Mike | Giraffe | 14 | M | 5.1 |
| Sammy | Giraffe | 1 | F | 2.4 |
| Rex | Lion | 4 | M | 1.8 |
| Leah | Lion | 5 | F | 1.3 |
| Luna | Lion | 2 | F | 0.8 |
| Andrea | Aardvark | 7 | F | 0.54 |
| Jawa | Python | 3 | M | 3.2 |
| Petra | Python | 2 | F | 2.8 |
Note: height refers to height or length, whichever is most appropriate
- Each row of a database table contains data about an individual thing - an object of some kind (sometimes called an entity). In my example database, each row has data about an animal in it. Each animal is a separate thing - a separate object.
- In database terms we call each object (each row) a record. A record simply stores data about an object.
- Each column in the database stores a specific piece of data for each record. One column in my database stores the name, another the species of animal, another the age and so on.
- In database terms we call each column a field. A field simply stores a particular piece of data.
Each field needs to be a specified data type. So, the name of each of my animals is a string (i.e. characters of some kind), the age is an integer, the height a floating point number. The gender is stored as a single character - a string of fixed length (that we could easily validate).
Things to do with databases
Other than store data in an organised way, databases can be used to:
- amend data in specific fields - I could change the age of Molly the Giraffe
- add records - I could add a new animal to my database (Clive the Hippo, say)
- delete records - if one of my animals "leaves the zoo" I can delete it
- search the database
Searching is one of the most useful features of databases. I can search my database in all sorts of interesting ways...
- to find out how many giraffes I have
- to make a list of all the animals aged over 10
- to find all the female animals
- to find all the animals whose names start with an M
- to find all the giraffes who are female and aged over 10 years
The last search is super-powerful and a simple example of the sorts of things that databases can be really useful for - think about the searches you might do on the Fifa database to find left backs from Denmark aged under 21 who have good speed and can tackle well...
What's really hard to do in a database is to add a field - to add a specific piece of data to each record. In a large database this would require you to add data to every single record, which would be very time consuming. This is one reason why designing databases well is a really important job.
More Information
Mr DBMS explains the basics of databases in this YouTube video
.
The BBC Bitesize website has a pretty good introduction to databases on it. There's a quiz you can do as well, although not all of the content in it is stuff you necessarily need to know and some of it is covered on some of the other pages in this unit.