Exam practice

Data is a collection of raw, unprocessed facts and figures, such as numbers, words, dates or images, that may have no meaning on their own; for example, 80, 85, 90.

Information is data that has been processed and organized so that it has meaning and helps in understanding and decision-making; for example, “Sita’s average mark is 85.”

A primary key is a field, or a combination of fields, that uniquely identifies each record in a table. Its values can’t be repeated and can’t be NULL (empty), and a table has only one primary key. For example, StudentID in a students table: no two students have the same StudentID.

A foreign key is a field in one table that refers to the primary key of another table. It links the two tables and keeps the link valid: only values that exist in the other table’s primary key may be entered. For example, StudentID in a Library table refers to StudentID in the Students table, so a book can be lent only to a student who exists.

A field (column) is a vertical column of a table that holds one kind of data about every entity, such as Name or Class.

A record (row) is a horizontal row of a table that holds all the data about one entity, such as one student’s ID, name, class and date of birth.

Here ID, Name and Class are fields, and the row 101, Binay Adhikari, 10 is a record.

An RDBMS (Relational Database Management System) is a DBMS that stores data in tables made of rows and columns, and links the tables to each other through common fields: a primary key in one table and a foreign key in another. Linking avoids repeating data and keeps it consistent, and the data is worked with in SQL. Examples: MySQL, Oracle Database, PostgreSQL, Microsoft SQL Server. RDBMSs are used in websites, apps, banks, schools and businesses.

• It finds any record quickly, even among millions, with a query.
• It checks every entry against rules (constraints), so wrong or duplicate data is refused.
• Many users can work on the same data at the same time, safely.
• Related tables are linked, so each fact is stored once and the copies don’t disagree (less redundancy).
• It controls who can see and change what (security), and it can back up and recover the data.

A spreadsheet becomes slow, error-prone and hard to share as the data grows.

The primary key gives every record its own identity: since its values are unique and never empty, each record can be found, changed or deleted without confusing it with another (two students named Sita Rai have different IDs).

The foreign key connects tables: it holds the primary key of a record in another table, so related data stored in separate tables can be linked and brought together, and it refuses values that don’t exist in the other table (referential integrity). Together they keep the data organized, without repetition, and correct.

In a one-to-many relationship, one record in a table is linked to many records in another table, but each of those records is linked to only one record in the first.

Example: a ward and its households. One ward of a municipality has many households, but each household is in only one ward. In the database, the Households table has a WardNo field, a foreign key to the Wards table. (Other examples: a mother and her children; a bank customer and his transactions; a bus route and its tickets.)

DDL (Data Definition Language) defines and changes the structure of the database: it creates, alters and drops databases, tables and columns (CREATE, ALTER, DROP, TRUNCATE, RENAME). Its changes are committed at once.

DML (Data Manipulation Language) works with the data inside the tables: it adds, reads, changes and removes records (INSERT, SELECT, UPDATE, DELETE), without changing the structure.

The WHERE clause filters the records: SELECT returns only the rows that meet its condition, instead of the whole table. For example,

SELECT Name FROM students WHERE Class = 10;

shows the names of only the Class 10 students. The condition can use comparisons (=, >, <, <>, >=, <=), LIKE with wildcards, and AND, OR and NOT.

Constraints are rules set on the columns of a table that control what data may be stored in them: PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK and DEFAULT.

They are important because the DBMS refuses any value that breaks them, so wrong data never gets in: no duplicate or empty IDs, no missing compulsory values, no impossible values (a mark of 130), and no links to records that don’t exist. This keeps the data accurate and consistent (data integrity) and enforces the organization’s rules.

A composite key is a primary key made of two or more fields that together identify each record uniquely. It is needed when no single field is unique on its own. For example, in a marks table (StudentID, Subject, Marks), a student appears once for each subject and a subject once for each student, so neither field alone is unique; but StudentID + Subject together is, since each student has one mark in each subject.

A database management system (DBMS) is software used to create a database and to store, retrieve, update, delete and protect its data in an organized way. It stands between the users and the data: users send requests (queries), and the DBMS does the work. Examples: MySQL, Oracle Database, Microsoft SQL Server, PostgreSQL, SQLite.

Advantages:

1. Quick storage and retrieval: any data is found in a moment with a query.
2. Less redundancy: each fact is stored once, in one place, and linked.
3. Consistency and accuracy: constraints refuse wrong data (data integrity).
4. Sharing: many users can work on the same data at once.
5. Security: each user is given only the access they need, with passwords.
6. Backup and recovery: data can be backed up and restored after a failure.

A primary key gives each record a unique identity. Without it, two records with the same values (two students called Sita Rai in Class 10) couldn’t be told apart, so the database couldn’t be sure which one to show, change or delete. It also lets other tables refer to a record exactly: a foreign key always points to a primary key. And the DBMS finds a record by its primary key very quickly.

Features:

• a.It uniquely identifies each record.
• b.It can’t contain duplicate values.
• c.It can’t be NULL (empty).
• d.A table has only one primary key, which may be made of one field or of several (a composite key).

A relationship is a link between two tables of a relational database, made through a common field: the primary key of one table appears as a foreign key in the other. It lets data be kept in separate tables without repetition and brought together when needed. There are three types:

1. One-to-one (1 : 1): each record in one table is linked to exactly one record in the other, and the other way round. Example: a person and his or her passport.
2. One-to-many (1 : M): one record in the first table is linked to many records in the second, but each record in the second to only one in the first. Example: a teacher teaches many classes; each class has one teacher. This is the most common type.
3. Many-to-many (M : M): many records in one table are linked to many in the other. Example: a student takes many courses, and a course has many students. It needs a third table, a junction table (such as Enrollments, with StudentID and CourseID), each of whose rows links one student and one course.

A query is a request made to a database to retrieve, insert, update or delete data, written in SQL. For example, SELECT Name, DOB FROM students WHERE Class = 10; asks for the names and dates of birth of the Class 10 students.

Importance:

1. It retrieves exactly the data needed from large tables, quickly.
2. It filters and sorts data by conditions (WHERE, ORDER BY), so nobody searches by hand.
3. It adds, updates and deletes records without changing the structure.
4. It makes reports: counts, totals and averages for analysis and decisions.
5. It is the main way users and programs communicate with the DBMS.

A report in MySQL is data retrieved from one or more tables by SQL queries and presented in an organized, readable and meaningful form, such as a list of the students with 80 or more marks, or the number of students in each class. MySQL itself gives the data as a result table; for charts and printed layouts it is connected to reporting tools (Power BI, Tableau, Looker Studio) or a spreadsheet.

Its purpose is to turn stored data into information: to help people understand and analyze the data, compare and spot trends, plan, and make decisions (which students need extra classes, which crop earned the most).

A report is the organized presentation of data taken from a database by queries. It can be a summary report (totals: the number of students), a detail report (the records: every student with their marks), a grouped report (by category: students in each class), a filtered report (only records meeting a condition: students above 80) or an aggregate report (with COUNT, SUM, AVG, MAX, MIN).

Features:

1. Organized presentation: data in a clear, readable order.
2. Filtering: only the records that matter (WHERE).
3. Summarizing: totals, averages and counts.
4. Combining tables: data from several related tables in one report.
5. Timely access: made from the current data whenever it is run.
6. Easy export: saved as CSV or Excel, or sent to reporting tools.

CREATE DATABASE CompanyData;USE CompanyData;

USE selects the new database, so that the tables of the next questions are made in it.

ALTER TABLE EmployeesALTER COLUMN Salary SET DEFAULT 25000;

or, with MODIFY (the whole column definition again, with the default):

ALTER TABLE EmployeesMODIFY Salary DECIMAL(10, 2) DEFAULT 25000;

Both work in MySQL. From now on, a new employee added without a salary gets 25000.00. (SQLite can’t change a column’s default once the table exists; there, the table would be made again with Salary DECIMAL(10, 2) DEFAULT 25000.)

RENAME TABLE Employees TO StaffMembers;

or

ALTER TABLE Employees RENAME TO StaffMembers;

The first works in MySQL; the second works in MySQL and SQLite.

INSERT INTO StaffMembers (EmployeeID, FirstName, LastName, Salary)VALUES (101, 'Sampada', 'Bhattarai', 30000.50);

Email isn’t given, so it is NULL.

SELECT * FROM StaffMembers;

* means every column; with no WHERE, every row is returned.

SELECT FirstName, Salary FROM StaffMembers;

The table is called StaffMembers since question e. (Before the rename, it would be SELECT FirstName, Salary FROM Employees;.)

UPDATE StaffMembersSET Salary = Salary * 1.10;

Each new salary is the old salary × 1.10, the old one plus a tenth of it; Sampada’s 30000.50 becomes 33000.55. There is no WHERE because every employee gets the raise. (MySQL Workbench’s safe-update mode refuses an UPDATE without a WHERE on a key; turn it off as in 2.8.3, or add WHERE EmployeeID > 0.)

TRUNCATE TABLE StaffMembers;

or, as DML, DELETE FROM StaffMembers;. Both leave the table, with its columns and constraints, empty. (SQLite has no TRUNCATE; use DELETE there.)