Convenience store sales records, school grades, social media posts — modern society is built on enormous amounts of data. A database is a system for organizing and storing large amounts of data and quickly retrieving what is needed. In this lecture, you will learn the basics of relational databases and SQL, which are widely used.
Databases and DBMS
A database is a collection of data organized according to set rules. Software that manages databases is called a DBMS (Database Management System). Its roles include ①ensuring that no inconsistencies occur when multiple people use it at the same time (concurrency control), ②protecting data from failures, and ③managing access rights. Unlike when individuals manage files separately, its strength is that data can be centrally managed and shared.
Relational Databases (RDBs)
The most widely used type today is the relational database. It manages data in the form of tables consisting of rows and columns. One horizontal row of a table is called a record (one data entry), and one vertical column is called a field (an item).
A column used to uniquely identify each record is called a primary key. A primary key must satisfy the conditions that its values must not be duplicated and must not be empty (NULL). Student numbers and member IDs are typical examples. A column that refers to the primary key of another table is called a foreign key, allowing multiple tables to be related.
Normalization — Separating Tables Clearly
As in the example above, the process of dividing and organizing tables to eliminate duplicate data is called normalization. If everything is packed into one table, the same student’s name may be recorded repeatedly in many places, making inconsistencies (update anomalies) caused by missed corrections more likely. A basic principle of database design is to use normalization to approach the state in which “each fact is recorded in only one place.”
SQL — A Query Language for Databases
The language called SQL is used to operate RDBs. The basic form for retrieving data is as follows.
SELECT column_name FROM table_name WHERE condition;
For example, SELECT name FROM student_table WHERE class = '2A'; means “retrieve the names of students whose class is 2A from the student table.” Other commands include INSERT for adding data, UPDATE for updating data, and DELETE for deleting data.
- A DBMS handles centralized data management, concurrency control, failure protection, and access-rights management.
- An RDB uses tables. A row = a record; a column = a field.
- A primary key must be “non-duplicated” and “not NULL.” Foreign keys relate tables to one another.
- Normalization means separating tables to eliminate duplication. It prevents inconsistencies during updates.
- SELECT column FROM table WHERE condition is the basic form of SQL.
Practice Problems
- State the 2 conditions that a primary key must satisfy.
- Explain in English the meaning of the SQL statement
SELECT name FROM student_table WHERE grade = 2;. - Explain the purpose of normalizing a database.
Answers and Explanations
- Answer: ①The values must not be duplicated (they must be unique), and ②they must not be empty (NULL)
Explanation: These 2 conditions ensure that every record can always be identified as exactly 1 entry by its primary-key value. - Answer: Retrieve all the names of students whose grade is 2 from the student table.
Explanation: SELECT specifies the “column to retrieve,” FROM specifies the “target table,” and WHERE specifies the “filtering condition.” - Answer: To eliminate duplicate data and prevent inconsistencies when updating or deleting data (for example, correcting only part of the same information and creating discrepancies).
Explanation: The principle is “each fact belongs in one place.” Reducing duplication also helps save storage capacity.