brodidit for students
Home / Notes / Class 12 / Computer Science / Databases and SQL
Databases and SQL: Class 12 Computer Science
Database and DBMS concepts, the relational model, keys, normalisation, entity relationship ideas and the main SQL commands for defining, changing and querying data.
Key points
A database is an organised collection of related data stored so that it can be retrieved and managed easily. A database management system (DBMS), such as MySQL, Oracle or Microsoft Access, is the software that creates, stores, protects and gives access to the data. It reduces redundancy, keeps data consistent, shares data between users and enforces security.
In the relational model data is stored in tables, called relations. A row is a record, or tuple, and a column is a field, or attribute. Each field has a data type, such as integer, text or date. Each table describes one kind of entity, such as students or courses.
Keys identify and link records:
- A primary key uniquely identifies each record in a table. It cannot be null and cannot repeat.
- A candidate key is any field or set of fields that could serve as the primary key.
- A foreign key is a field in one table that holds the primary key of another table, and so links the tables.
- A composite key is a primary key made of two or more fields.
The relationships between tables can be one to one, one to many or many to many. A many to many relationship, such as students and courses, is handled by a third table, called a junction or linking table, that holds the keys of both.
Redundancy is the unnecessary repeating of data. It wastes space and causes anomalies: an insertion anomaly (data cannot be added without other data), an update anomaly (the same fact must be changed in many places) and a deletion anomaly (deleting a record loses other facts).
Normalisation removes redundancy by splitting tables:
- First normal form (1NF): each field holds a single value, with no repeating groups.
- Second normal form (2NF): the table is in 1NF and every non-key field depends on the whole primary key.
- Third normal form (3NF): the table is in 2NF and no non-key field depends on another non-key field.
SQL (Structured Query Language) is the standard language of relational databases. Its statements fall into groups: DDL (data definition language: CREATE, ALTER, DROP), DML (data manipulation language: INSERT, UPDATE, DELETE), DQL (query: SELECT) and DCL (control: GRANT, REVOKE).
The most important statements:
- CREATE TABLE student (id INT PRIMARY KEY, name VARCHAR(30), marks INT);
- INSERT INTO student VALUES (1, 'Ali', 85);
- SELECT name, marks FROM student WHERE marks > 70 ORDER BY marks DESC;
- UPDATE student SET marks = 90 WHERE id = 1;
- DELETE FROM student WHERE id = 1;
- ALTER TABLE student ADD city VARCHAR(20);
- DROP TABLE student;
In a SELECT statement, WHERE filters rows, ORDER BY sorts them (ASC is the default, DESC is the reverse), and GROUP BY groups rows so that aggregate functions can be used on each group. The aggregate functions are COUNT, SUM, AVG, MIN and MAX. HAVING filters groups after GROUP BY, while WHERE filters rows before grouping.
The operators used in WHERE include =, <>, >, <, >=, <=, AND, OR, NOT, BETWEEN, IN and LIKE. In a LIKE pattern, % stands for any number of characters and _ stands for exactly one character. A JOIN combines rows from two tables using a condition on the matching keys, for example SELECT s.name, c.title FROM student s JOIN course c ON s.course_id = c.id.
Study tip: To write an SQL query, say it in English first: which columns, from which table, which rows, in what order. Then write SELECT, FROM, WHERE and ORDER BY in that order.
Definitions and flashcards
Say the meaning first, then open the card to check.
Database
An organised collection of related data.
DBMS
Software that creates and manages databases and gives access to the data.
Primary key
A field that uniquely identifies each record in a table.
Foreign key
A field that holds the primary key of another table and links the two.
Normalisation
Splitting tables to remove redundancy and avoid anomalies.
Anomaly
A fault such as an insertion, update or deletion problem that results from redundancy.
Join
An SQL operation that combines rows from two tables on matching key values.
Aggregate function
A function such as COUNT or SUM that returns one value for a group of rows.
Short questions with model answers
These are the kind of questions asked for 2, 3 and 5 marks. Try each one before opening the answer.
What is the difference between a primary key and a foreign key? (3 marks)
A primary key uniquely identifies each record in its own table and cannot be null or repeated. A foreign key is a field in another table that refers to the primary key and is used to link the two tables.
State two advantages of a DBMS over a file system. (2 marks)
It reduces redundancy and keeps data consistent, and it allows many users to share the same data with security controls.
Write an SQL statement to display the names of students with marks above 60 in descending order of marks. (3 marks)
SELECT name FROM student WHERE marks > 60 ORDER BY marks DESC;
What is the difference between WHERE and HAVING? (2 marks)
WHERE filters individual rows before grouping, and HAVING filters groups after GROUP BY, usually using an aggregate function.
Why is normalisation done? (3 marks)
To remove redundancy and avoid insertion, update and deletion anomalies, by dividing a large table into smaller related tables.
Numericals with worked steps
A table employee(id, name, salary) has rows with salaries 30000, 45000 and 50000. Give the result of SELECT AVG(salary), MAX(salary) FROM employee;
- AVG = (30000 + 45000 + 50000) / 3 = 125000 / 3 = 41666.67.
- MAX = 50000.
Answer: 41666.67 and 50000
Write a query to count how many students are in each city from student(id, name, city).
- Use GROUP BY on the city.
- Count the rows in each group with COUNT(*).
Answer: SELECT city, COUNT(*) FROM student GROUP BY city;
Write a query to find all names that start with the letter A.
- Use LIKE with a pattern.
- The pattern 'A%' matches A followed by any characters.
Answer: SELECT name FROM student WHERE name LIKE 'A%';
Common mistakes
- Using WHERE instead of HAVING with an aggregate function.
- Forgetting the semicolon, or writing text values without quotes.
- Putting a primary key value that is repeated or null.
- Confusing DELETE, which removes rows, with DROP, which removes the whole table.
- Believing that a foreign key must be unique in its own table. It may repeat.