GCSE Computer Science · Topic 5
Databases and impacts of technology
Databases and the impacts of technology covers how data is organised, and how technology affects people and the planet.
We will use relational databases, keys and S Q L queries.
Then we look at data protection and computer misuse law, copyright and licences, privacy, and environmental impacts.
- 2 subtopics
- 29 questions
- Free: 5.1 notes and 10 questions
In this topic
- 5.1Relational databases and SQLFree below · 13 questions
- 5.2Ethical, legal and environmental impactsOpen in Full Marks · 16 questions
5.1 · Free notes
Relational databases and SQL
Key points
- A relational database stores data in linked tables of records (rows) and fields (columns).
- A primary key uniquely identifies each record; a foreign key links a record to a primary key in another table.
- Relational databases reduce data duplication and inconsistency compared with a single flat file.
- SQL commands: SELECT (choose fields), FROM (choose tables), WHERE (filter), ORDER BY (sort), INSERT INTO, UPDATE and DELETE.
- Example: SELECT name FROM students WHERE year = 11 ORDER BY name.
In more detail
- Worked query: SELECT name, form FROM Students WHERE year = 11 AND form = 'B' ORDER BY name ASC.
- Worked wildcard: WHERE surname LIKE 'Sm%' finds Smith and Smart.
- Worked keys: Students(StudentID primary key) and Loans(LoanID primary key, StudentID foreign key, BookID foreign key) link students to the books they borrow.
- Worked update: UPDATE Students SET form = 'C' WHERE StudentID = 1042.
- Why separate tables: storing a teacher's phone number in every student record means many records to change if it changes; a separate Teachers table avoids inconsistency.
Practice
Relational databases and SQL: 10 questions
Pick an answer to check it, or open the answer for the explanation.
1. Which SQL command retrieves data?
- INSERT
- UPDATE
- SELECT
- DELETE
Show answer
C: SELECT. SELECT ... FROM ... WHERE ... is the standard query form.
2. What is a primary key?
- A password for the database
- A field that uniquely identifies each record
- The first field in a table
- A link to a website
Show answer
B: A field that uniquely identifies each record. No two records may share the same primary key value.
3. What does a foreign key do?
- Encrypts a table
- Links a record to the primary key of another table
- Sorts records
- Deletes a record
Show answer
B: Links a record to the primary key of another table. This creates relationships between tables.
4. Which SQL keyword filters records?
- ORDER BY
- WHERE
- FROM
- SELECT
Show answer
B: WHERE. WHERE applies a condition to select matching records.
5. What is an advantage of a relational database over a flat file?
- It stores less data
- It reduces data duplication and inconsistency
- It never needs keys
- It cannot be queried
Show answer
B: It reduces data duplication and inconsistency. Data is stored once and linked rather than repeated.
6. Which SQL command adds a new record?
- SELECT
- INSERT INTO
- UPDATE
- WHERE
Show answer
B: INSERT INTO. INSERT INTO adds rows to a table.
7. What does ORDER BY do in SQL?
- Filters records
- Sorts the results
- Deletes records
- Creates a table
Show answer
B: Sorts the results. It can sort ascending or descending.
8. In a relational database, what is a record?
- A column
- A row containing data about one entity
- A table
- A key
Show answer
B: A row containing data about one entity. Fields are the columns within each record.
9. A table named pupils has fields name, year and score. Which query lists the names of pupils with a score above 70, from highest score to lowest?
- SELECT name FROM pupils ORDER BY score DESC WHERE score > 70
- SELECT name FROM pupils WHERE score < 70 ORDER BY score ASC
- SELECT * WHERE score > 70 FROM pupils ORDER BY score
- SELECT name FROM pupils WHERE score > 70 ORDER BY score DESC
Show answer
D: SELECT name FROM pupils WHERE score > 70 ORDER BY score DESC. The order is SELECT, FROM, WHERE, then ORDER BY. DESC sorts highest first, and > 70 selects scores above 70. The other queries have the clauses in the wrong order or use the wrong comparison or sort direction.
10. A shop's Orders table has OrderID, CustomerID and Date. Customers has CustomerID, Name and Address. Which statement about CustomerID is correct?
- It is a primary key in both tables so they cannot be linked
- It is a primary key in Orders and a foreign key in Customers
- It is a foreign key in Orders and the primary key in Customers
- It is a foreign key in both tables and so uniquely identifies no records
Show answer
C: It is a foreign key in Orders and the primary key in Customers. Each customer appears once in Customers, so CustomerID is its primary key. In Orders a customer can appear many times, so CustomerID links to Customers and is a foreign key.
Keep going in Full Marks
Full Marks has 13 questions on Relational databases and SQL (3 more than here), set out in practice levels. You also get notes and 3D models for all 2 subtopics of Databases and impacts of technology, plus games, timed exams and past papers.