Computer Science
Databases — Paper 2
- 1.
Explain one advantage of a relational database over a single flat-file database for storing a school's student and course records.
[2 marks] · no calculator - 2.
In a database table storing student records, which field would be most suitable as a primary key? A Student name B Date of birth C Student ID number D Class name
[1 mark] · no calculator - 3.
A Students table has primary key StudentID. An Enrolments table records each student-course pairing and contains StudentID referencing Students.StudentID. Identify the role of StudentID in Enrolments and explain its purpose.
[2 marks] · no calculator - 4.
Write an SQL statement to select the Name and Age fields from a table called Students, for records where Age is greater than 16.
[3 marks] · no calculator - 5.
Write an SQL statement to select all fields from a table called Books, ordered by PublishedYear in descending order.
[3 marks] · no calculator - 6.
Explain one problem that can occur if a single flat-file table stores both a student's personal details and a separate repeated row for every course they take.
[2 marks] · no calculator - 7.
Which data type would be most suitable for storing a student's exact date of birth in a database? A Text B Boolean C Date D Integer
[1 mark] · no calculator - 8.
Explain why a database field storing a student's 'Year Group' might be restricted to a predefined list of values rather than allowing free text entry.
[2 marks] · no calculator - 9.
Write an SQL statement to insert a new record into a table called Students, with StudentID 101, Name 'Amira', and Age 15.
[3 marks] · no calculator - 10.
Write an SQL statement to update the Age field to 16 for the student with StudentID 101 in the Students table.
[3 marks] · no calculator - 11.
Write an SQL statement to delete the record for the student with StudentID 101 from the Students table.
[2 marks] · no calculator - 12.
Write an SQL statement to select the Name field from a table called Students, for records where Age is greater than 14 AND the Grade field equals 'A'.
[2 marks] · no calculator - 13.
Write an SQL statement to count how many records are in a table called Students.
[2 marks] · no calculator - 14.
Explain referential integrity using a Students table with primary key StudentID and an Enrolments table whose non-NULL StudentID foreign key references it.
[2 marks] · no calculator - 15.
Explain what it means for a database field to contain a NULL value.
[1 mark] · no calculator - 16.
Explain, using students and clubs as an example, what is meant by a many-to-many relationship in a database.
[2 marks] · no calculator - 17.
Explain one function performed by a Database Management System (DBMS).
[2 marks] · no calculator - 18.
Explain the purpose of a data dictionary when designing a database.
[2 marks] · no calculator - 19.
Explain how an index on a frequently searched database field can speed up suitable lookups, and state one trade-off.
[2 marks] · no calculator - 20.
Explain, in simple terms, what an SQL JOIN is used for when working with two related tables.
[2 marks] · no calculator - 21.
Explain the purpose of drawing an entity-relationship diagram before building a database.
[2 marks] · no calculator - 22.
Explain why a field recording whether a student has paid a school trip deposit would be best stored as a Boolean data type.
[2 marks] · no calculator - 23.
Explain why a school should regularly back up its student records database.
[2 marks] · no calculator - 24.
Write an SQL statement to select all fields from a table called Employees, ordered first by Department and then by Surname, both in ascending order.
[2 marks] · no calculator