Skip to content
Cambridge IGCSE0478

Databases

Computer Science 0478 Chapter Notes

What this chapter covers

Databases
ShareWhatsAppPost
Databases notes

Read online free. Download a watermarked copy with a free account.

Read the notes

The full Databases notes as text: skim, search, and jump between subtopics.

~10 min read

In this chapter

  1. 1Tables, fields and records
  2. 2Basic data types
  3. 3Primary keys
  4. 4Writing a query: SELECT, FROM, WHERE, ORDER BY
  5. 5Reading a query: AND, OR, SUM, COUNT

1. Tables, fields and records

A single-table database stores its data in one table. Each column is a field. Each row is a record.

The heading row names the fields. It is not a record. To count, fields are the columns and records are the rows below the heading.

Validation checks on what a field accepts are part of defining a table.

Examiner insight

“Some candidates mixed up fields and records and gave their answers in reverse to what was expected.” “Some candidates included the table heading row as part of their amount of records response.”

Worked example 12 marks

A database table NATIONAL_TREES stores details of the national trees of some countries from around the world.

State the number of fields and records in this database table.

IDCodeCountryContinentCommonNameScientificName
EU121AlbaniaEuropeOliveOlea europaea
OC235AustraliaOceaniaGolden wattleAcacia pycnantha
NO126The BahamasNorth AmericaLignum vitaeGuaiacum sanctum
AS534BangladeshAsiaMango treeMangifera indica
SO326BrazilSouth AmericaBrazilwoodCaesalpinia echinata
NO124CanadaNorth AmericaMapleAcer
EU321CyprusEuropeGolden oakQuercus alnifolia
NA456Dominican RepublicNorth AmericaWest Indian mahoganySwietenia mahagoni
EU652FinlandEuropeSilver birchBetula pendula
AS222IndiaAsiaIndian banyanFicus benghalensis
NO232JamaicaNorth AmericaBlue mahoeTalipariti elatum
AF875MadagascarAfricaBaobabAdansonia
OC123New ZealandOceaniaSilver fernCyathea dealbata
AS498PhilippinesAsiaNarraPterocarpus indicus
AF501South AfricaAfricaReal yellowwoodPodocarpus latifolius
SO522VenezuelaSouth AmericaAraguaneyTabebuia chrysantha
  1. 1

    fields: 5 (IDCode, Country, Continent, CommonName, ScientificName) [1]

  2. 2

    records: 16, not counting the heading row [1]

2. Basic data types

Each field has one data type. Choose from text (alphanumeric), character, Boolean, integer, real and date/time.

Use `integer` for whole numbers and `real` for numbers with decimal places. Use `Boolean` for a yes/no field. Use `character` for a single letter code. Use text for letters and numbers together.

Write only the types the question lists.

Examiner insight

“A common error seen was to give string instead of text.” “A few candidates did not use the data types provided and a few repeated the data types for the fields.”

Worked example 14 marks

A database table CAR_PARTS stores details of car parts that a business has in stock.

Complete the table by giving the most appropriate data types for each field.

Only the data types: text, character, Boolean, integer, real and date/time are available in this database.

fielddescription
PartIDunique code to identify the car part with letters and numbers
PartNamename of the car part, for example windscreen wiper
CarTypefour-digit number code to represent the type of car
Priceselling price to 2 decimal places
Sizespecifies the size if relevant, otherwise stores 'one size'
Coloursingle letter code for the colour of the car part
NumberInStockthe quantity of car parts in stock
InStockwhether or not the car part is in stock
  1. 1

    `PartID` text, `PartName` text [1]

  2. 2

    `CarType` integer (or text), `Price` real [1]

  3. 3

    `Size` text, `Colour` character [1]

  4. 4

    `NumberInStock` integer, `InStock` Boolean [1]

3. Primary keys

A primary key is a field that uniquely identifies each record. No two records may share a value in it.

To choose one, pick the field whose values are never repeated. A name can be repeated, so it is a poor key. An ID code is a better key.

If two fields are both unique, prefer the one that is fixed in size or shorter.

Examiner insight

“Many candidates missed that the question told them the IDCode and Country fields both gave unique data, by suggesting the IDCode would make a more suitable primary key because its data was unique.” “Some candidates understood that primary keys were more manageable if they were of a fixed size or format.”

Worked example 12 marks

A swimming club wants to set up a new database table to store details of the members of the club and the maximum distance each member swam in metres.

The table is called SWIMMERS and will contain these fields:

• Distance – distance the member swam, for example 1500

• Date – date the member swam the distance

• Time – time taken to swim the distance

• MemberID – the code to identify a member, for example TH749

• MemberName – name of the member.

(a)(i) State why MemberName would not be appropriate for a primary key.

(ii) Give the name of the most appropriate field for the primary key.

  1. 1

    (i) MemberName may be repeated // it is not unique [1]

  2. 2

    (ii) `MemberID` [1]

4. Writing a query: SELECT, FROM, WHERE, ORDER BY

A query has up to four lines. `SELECT` lists the correct fields and no others. `FROM` names the table. `WHERE` gives the condition. `ORDER BY` sorts the output.

``` SELECT Name, Age FROM PETS WHERE Species = "Dog" ORDER BY Age DESC; ```

Text in a condition goes in quotation marks. Numbers do not. `ASC` sorts lowest first and `DESC` sorts highest first. End with a semicolon.

Examiner insight

“A common mistake seen was candidates missing off the quotation marks around the search criteria ‘Asia’, which was a string.” “Some candidates missed out the correct ORDER BY statement or used incorrect SQL code within that statement.”

Worked example 15 marks

A database table OPTION_SUBJECTS stores some data about a number of subjects taken by students in a college.

(c) Complete the structured query language (SQL) statement to list only the subject code, subject name and level for the minimum class size of less than 10. The results should be listed in order of subject code, from the lowest to the highest.

fielddescription
SubjectCodea code such as CSIG01 to represent the subject and level
SubjectNamethe name of the subject, for example computer science
Levelthe level of the course, for example IGCSE or A Level
CourseWorkwhether or not coursework is included as part of the assessment
PracticalExamsthe number of practical exams taken during the course
TheoryExamsthe number of theory exams taken during the course
CourseLengthlength of the course, for example 1 or 2, representing number of years
MinimumClassSizethe minimum number of students required for the course to run, for example 10
MaximumClassSizethe maximum number of students admitted to each class, for example 30
  1. 1

    `SELECT SubjectCode, SubjectName, Level` (two correct fields 1, the third 1) [2]

  2. 2

    `FROM OPTION_SUBJECTS` [1]

  3. 3

    `WHERE MinimumClassSize < 10` [1]

  4. 4

    `ORDER BY SubjectCode ASC;` [1]

5. Reading a query: AND, OR, SUM, COUNT

To give the output, test each record against the `WHERE` condition. `AND` needs both conditions true. `OR` needs at least one of them true. Then apply `ORDER BY`.

`SUM(Field)` adds up the values of a field. `COUNT(Field)` counts the records that match. Each gives one value.

Write one row for each record that passes, with only the fields in `SELECT` and no extra punctuation.

Examiner insight

“Punctuation marks should not be in the output from an SQL statement.” “Some candidates included unnecessary extra punctuation, such as commas, in their responses.”

Worked example 14 marks

A database table ITEMS stores stock in a shop. Give the output of the SQL statement.

``` SELECT Name, Stock FROM ITEMS WHERE Type = "Sport" OR Price < 4 ORDER BY Stock DESC; ```

ItemCodeNameTypeStockPrice
B101BatSport1215.50
B102BallSport404.00
G201ChessGame812.00
G202CardsGame253.50
S301PenStationery1001.20
  1. 1

    `WHERE` keeps a record if its type is Sport or its price is below 4: Bat, Ball, Cards, Pen.

  2. 2

    Pen 100 [1]

  3. 3

    Ball 40 [1]

  4. 4

    Cards 25 [1]

  5. 5

    Bat 12 [1]

Recap

  • A table has fields (columns) and records (rows). The heading row is not a record.
  • Data types: text, character, Boolean, integer, real, date/time. Say text, not string.
  • A primary key uniquely identifies each record. Choose a field that is never repeated.
  • SELECT lists fields, FROM names the table, WHERE sets the condition, ORDER BY sorts.
  • Text in a condition needs quotation marks. Output has no extra punctuation.
  • AND needs both conditions true, OR needs at least one. SUM adds, COUNT counts.

End-of-chapter exercise

Test yourself on the whole chapter. Work through these before moving on.

  1. A table of club members is printed with 41 rows, including the heading row, and 6 columns. State the number of fields and the number of records.2 marks
  2. A table STUDENTS has the fields StudentID (a unique code), FirstName, Surname and Form. State the best primary key and give a reason.2 marks
  3. The data types available are text, character, Boolean, integer, real and date/time. Give the most appropriate data type for each field. (a) goals scored in a match (b) whether a player is injured (yes or no) (c) date of birth (d) average goals per match, for example 1.754 marks
  4. A library table has the fields BookID (for example LB4521), Title, Pages and OnLoan (yes or no). Using only text, character, Boolean, integer and real: (a) give a data type for each of BookID, Pages and OnLoan (b) name the most appropriate primary key4 marks
  5. A table PETS has the fields PetID, Name, Species and Age. Write an SQL statement to list only the name and age of all pets whose species is Cat.3 marks
  6. A table FILMS stores film data. Give the output of this SQL statement. ``` SELECT Title, Rating FROM FILMS WHERE Year >= 2015 ORDER BY Title ASC; ``` | FilmID | Title | Year | Rating | | --- | --- | --- | --- | | F1 | Orbit | 2015 | 4 | | F2 | Harbour | 2009 | 5 | | F3 | Lantern | 2018 | 3 | | F4 | Cobalt | 2015 | 5 | | F5 | Meadow | 2012 | 2 |3 marks
  7. Using the table FILMS from question 6, give the output of this SQL statement. ``` SELECT Title FROM FILMS WHERE Year < 2013 OR Rating = 5; ```3 marks
  8. Using the table FILMS from question 6, give the output of each SQL statement. (a) `SELECT COUNT(FilmID) FROM FILMS WHERE Year >= 2015 AND Rating >= 4;` (b) `SELECT SUM(Rating) FROM FILMS WHERE Year >= 2015;`2 marks
  9. A ferry company stores its crossings in the table FERRIES. A student wants to list only the route and fare of every SeaLink crossing with a fare of 20 or less, highest fare first. The student writes this SQL statement. ``` SELECT Route, Operator, Fare FROM FERRIES WHERE Operator = SeaLink OR Fare < 20 ORDER BY Route DESC; ``` (a) Identify the five errors in the statement and write the correct statement. (b) Give the output of the correct statement. | FerryID | Route | Operator | Seats | Fare | | --- | --- | --- | --- | --- | | F1 | Dover | SeaLink | 400 | 20 | | F2 | Calais | BlueWave | 250 | 18 | | F3 | Larne | SeaLink | 300 | 35 | | F4 | Oban | SeaLink | 120 | 12 | | F5 | Hull | BlueWave | 500 | 20 |7 marks
  10. A diving club stores its dives in the table DIVES. (a) A student says the table has 8 records and that Diver is the best primary key. State the correct number of records. Explain why Diver is not suitable and name the field that is. (b) Give the output of `SELECT SUM(Minutes) FROM DIVES WHERE Site = "Wreck" OR Depth >= 22;` (c) Give the output of `SELECT COUNT(DiveID) FROM DIVES WHERE Diver = "Asha" AND Minutes > 42;` (d) A statement gives the output Keiko 50, then Asha 45, then Asha 42, each on its own row. Complete the statement. ``` SELECT Diver, Minutes FROM DIVES WHERE .................... ORDER BY ....................; ``` | DiveID | Diver | Site | Depth | Minutes | | --- | --- | --- | --- | --- | | D01 | Asha | Reef | 18 | 42 | | D02 | Tomas | Wreck | 30 | 25 | | D03 | Asha | Wreck | 27 | 31 | | D04 | Keiko | Reef | 12 | 50 | | D05 | Tomas | Cave | 22 | 38 | | D06 | Keiko | Wreck | 30 | 28 | | D07 | Asha | Cave | 15 | 45 |9 marks

Go deeper

Practise and revise with member-only material for this chapter.

Workbook

Exam-style practice questions with full worked solutions.

Unlock with a plan

Chapter at a Glance

A single-page summary of every key idea for fast revision.

Unlock with a plan

Free notes are just the start.

Unlock every Workbook and Chapter at a Glance, and generate your own worksheets and predicted papers.

Explore plans

Related chapters

Number systemsTypes and methods of data transmissionComputer architectureTypes of software and interruptsThe internet and the world wide webAutomated systems