UK K12 GCSE/A-Level: Year 12 A-Level Lower Sixth Computer Science - Databases, Advanced SQL and ER Diagrams

Learning Objectives

By the end of this topic, students will be able to:

• Design and create Entity-Relationship (ER) diagrams to model complex database systems
• Write advanced SQL queries using subqueries, joins, and aggregations
• Apply normalization techniques to optimize database performance and minimize data redundancy
• Evaluate the trade-offs between different database design approaches
• Implement database security measures to protect sensitive data

Core Concepts

Entity-Relationship Diagrams

An Entity-Relationship (ER) diagram is a visual representation of a database schema, showing the relationships between entities (tables) and their attributes (columns). Entities are represented as rectangles, and relationships are shown as lines connecting the entities. ER diagrams help to identify data redundancy and ensure data consistency.

For example, consider a database for a university's student records. The ER diagram might include entities for Students, Courses, and Grades, with relationships between them.

Advanced SQL Queries

SQL (Structured Query Language) is a programming language used to manage and manipulate data in relational databases. Advanced SQL queries involve using subqueries, joins, and aggregations to retrieve and manipulate data.

• Subqueries: A subquery is a query nested inside another query. It can be used to retrieve data that meets a specific condition or to perform calculations.
• Joins: A join combines data from two or more tables based on a common column. There are three types of joins: INNER JOIN, LEFT JOIN, and RIGHT JOIN.
• Aggregations: Aggregations involve calculating summary statistics, such as SUM, AVG, and COUNT, to retrieve data.

Normalization

Normalization is the process of organizing data in a database to minimize data redundancy and improve data integrity. There are several normalization rules, including:

• First Normal Form (1NF): Each table cell must contain a single value.
• Second Normal Form (2NF): Each non-key attribute must depend on the entire primary key.
• Third Normal Form (3NF): If a table is in 2NF, and a non-key attribute depends on another non-key attribute, then it should be moved to a separate table.

Database Security

Database security involves protecting sensitive data from unauthorized access, modification, or destruction. Common security measures include:

• Access control: Restricting access to sensitive data based on user roles and permissions.
• Data encryption: Encrypting data to prevent unauthorized access.
• Backup and recovery: Regularly backing up data and having a recovery plan in place in case of a disaster.

Worked Examples

Example 1: ER Diagram

Suppose we want to design an ER diagram for a database that stores information about books and their authors. The entities might include:

• Books: Book title, author ID, publication date
• Authors: Author name, email address
• Book-Author: Relationship between books and authors (one book can have multiple authors, and one author can write multiple books)

The ER diagram might look like this:

Example 2: Advanced SQL Query

Suppose we want to retrieve the average salary of employees who work in the sales department. We might use the following SQL query:

SELECT AVG(salary)
FROM employees
WHERE department = 'Sales';

This query uses the AVG aggregation function to calculate the average salary, and the WHERE clause to filter the results to only include employees who work in the sales department.

Example 3: Normalization

Suppose we have a table called orders that stores information about customer orders, including the customer ID, order date, and product ID. However, the table also includes the customer name and address, which are not necessary for the order data.

To normalize the table, we might split it into two tables: orders and customers. The orders table would include the order ID, customer ID, order date, and product ID, while the customers table would include the customer ID, name, and address.

Common Misconceptions

• Misconception 1: ER diagrams are only used for simple databases.
• Why it fails: ER diagrams can be used to model complex databases with multiple entities and relationships.
• Misconception 2: SQL queries can only be used to retrieve data.
• Why it fails: SQL queries can be used to insert, update, and delete data, as well as retrieve it.
• Misconception 3: Normalization is only used to improve data integrity.
• Why it fails: Normalization is also used to minimize data redundancy and improve data performance.

Exam Tips

• Tip 1: Make sure to read the question carefully and understand what is being asked.
• Tip 2: Use ER diagrams to visualize the database schema and identify relationships between entities.
• Tip 3: Use SQL queries to retrieve and manipulate data, and make sure to use the correct syntax and semantics.
• Tip 4: Use normalization to minimize data redundancy and improve data integrity.
• Tip 5: Use database security measures to protect sensitive data from unauthorized access.

MCQs

MCQ 1: ER Diagrams [F]

What is the purpose of an Entity-Relationship (ER) diagram?

A) To retrieve data from a database B) To design a database schema C) To perform a database query D) To encrypt data

Correct answer: B) To design a database schema

Why the distractors fail:

• A) ER diagrams are not used for retrieving data.
• C) ER diagrams are not used for performing database queries.
• D) ER diagrams are not used for encrypting data.

MCQ 2: Advanced SQL Queries [H]

What is the purpose of a subquery in a SQL query?

A) To retrieve data from a single table B) To combine data from multiple tables C) To perform a calculation on data D) To filter data based on a condition

Correct answer: D) To filter data based on a condition

Why the distractors fail:

• A) Subqueries can be used to retrieve data from multiple tables.
• B) Subqueries can be used to perform calculations on data.
• C) Subqueries can be used to filter data based on a condition, but this is not their primary purpose.

MCQ 3: Normalization [F]

What is the first normalization rule?

A) Each table cell must contain a single value B) Each non-key attribute must depend on the entire primary key C) Each table must have a primary key D) Each table must have a foreign key

Correct answer: A) Each table cell must contain a single value

Why the distractors fail:

• B) This is the second normalization rule.
• C) This is not a normalization rule.
• D) This is not a normalization rule.

MCQ 4: Database Security [H]

What is the purpose of access control in database security?

A) To encrypt data B) To backup data C) To restrict access to sensitive data based on user roles and permissions D) To perform a database query

Correct answer: C) To restrict access to sensitive data based on user roles and permissions

Why the distractors fail:

• A) Access control is not used for encrypting data.
• B) Access control is not used for backing up data.
• D) Access control is not used for performing database queries.

MCQ 5: ER Diagrams [H]

What is the purpose of a relationship between entities in an ER diagram?

A) To show the attributes of an entity B) To show the relationships between entities C) To show the primary key of an entity D) To show the foreign key of an entity

Correct answer: B) To show the relationships between entities

Why the distractors fail:

• A) Attributes are shown in the entity boxes, not in the relationships.
• C) The primary key is shown in the entity box, not in the relationships.
• D) The foreign key is shown in the relationship, but this is not its primary purpose.

Short-Answer Questions

Question 1

Describe the purpose of an Entity-Relationship (ER) diagram in database design. (10 marks)

Question 2

Explain the difference between a subquery and a join in a SQL query. (10 marks)

Question 3

Describe the first normalization rule and provide an example of how it is applied. (10 marks)

Question 4

Explain the purpose of access control in database security. (10 marks)

Question 5

Describe the purpose of a relationship between entities in an ER diagram. (10 marks)