Introduction to MySQL — Study Notes

1. Introduction

Databases are the organised repositories where modern applications store their data. Whether it is a bank keeping customer accounts, a school keeping student records, or a shop keeping its inventory, all of them rely on database management systems. MySQL is one of the most popular open-source Relational Database Management Systems (RDBMS) in the world, widely used because it is free, fast, reliable, and based on the widely accepted Structured Query Language (SQL).

A database management system is software that allows users to create, manage, and manipulate databases. In a relational database, data is stored in the form of tables, which are made up of rows and columns. Each row represents a record, and each column represents a field or attribute of that record. The relationship between tables is what makes the system "relational".

This chapter introduces the fundamental concepts of databases and MySQL. We will learn about data, databases, DBMS, the characteristics of tables (records and fields), keys such as primary keys and foreign keys, and the common data types used in MySQL. We will also see how to connect to the MySQL server, create and use a database, and create and view a table. These fundamentals prepare the student for writing actual queries in the next chapter.

2. What is Data and Information

Data is the raw, unprocessed facts and figures collected from the environment, such as a student's roll number, name, and marks. Information is the meaningful result obtained after processing data, such as the average marks of a class. A database stores data in a structured way so that it can be easily processed to produce information.

3. Database and DBMS

A database is an organised collection of interrelated data stored together so that it can be easily accessed, managed, and updated. A Database Management System (DBMS) is the software that provides tools to create and maintain databases and to perform operations such as inserting, updating, deleting, and retrieving data.

Some examples of DBMS are MySQL, Oracle, Microsoft SQL Server, and PostgreSQL. The language used to interact with a relational DBMS is SQL, the Structured Query Language.

Characteristics of a DBMS

• Data is stored in tables with rows and columns.
• Data redundancy is reduced through normalisation.
• Data integrity is maintained using constraints and keys.
• Multiple users can access data simultaneously.
• Data security is provided through user permissions.
• Backup and recovery mechanisms protect data.

4. Relational Model

In the relational model, data is organised into tables (also called relations). Each table has a name and contains: - Records (Rows/Tuples): Each row represents one complete data entry. - Fields (Columns/Attributes): Each column represents one attribute of the records.

In this table, each row is a record (one student) and each column is a field (Roll, Name, Marks, City). The table's cardinality is the number of rows (3), and its degree is the number of columns (4).

5. Keys in a Relational Database

5.1 Primary Key

A primary key is a column (or a combination of columns) whose values uniquely identify each row in a table. A primary key cannot have NULL values and cannot contain duplicates.

5.2 Candidate Key

A candidate key is a column that qualifies to become a primary key because its values are unique and not NULL. A table may have several candidate keys, from which one is chosen as the primary key.

5.3 Alternate Key

The candidate keys that are not chosen as the primary key are called alternate keys.

5.4 Foreign Key

A foreign key is a column in one table that refers to the primary key of another table. It is used to establish a relationship between the two tables and ensures referential integrity.

5.5 Composite Key

A composite key is a primary key made up of two or more columns that together uniquely identify a row.

6. MySQL Data Types

MySQL supports many data types. The important ones for Class 11 are:

Numeric Types

• INT: Whole numbers from about -2 billion to +2 billion.
• SMALLINT: Smaller range integers.
• DECIMAL(p, s): Exact decimal values with precision p and scale s.
• FLOAT / DOUBLE: Approximate floating-point numbers.
• BOOL / BOOLEAN: Stores TRUE (1) or FALSE (0).

Date and Time Types

• DATE: Stores date in YYYY-MM-DD format.
• TIME: Stores time in HH:MM:SS format.
• DATETIME: Stores both date and time.
• TIMESTAMP: Stores date and time for records of transactions.
• YEAR: Stores a year.

String Types

• CHAR(n): Fixed-length string of n characters.
• VARCHAR(n): Variable-length string up to n characters.
• TEXT: Large text values.

7. Getting Started with MySQL

7.1 Connecting to the Server

On a typical installation, the MySQL server is accessed through the MySQL command-line client:

mysql -u root -p

After entering the password, the prompt changes to mysql>.

7.2 Viewing Databases

SHOW DATABASES;

7.3 Creating a Database

CREATE DATABASE school;

7.4 Using a Database

USE school;

7.5 Creating a Table

CREATE TABLE student (
    roll INT PRIMARY KEY,
    name VARCHAR(30),
    marks DECIMAL(5,2),
    city VARCHAR(20)
);

7.6 Viewing the Table Structure

DESC student;

The DESC command displays the structure of the table, showing each column's name, data type, and whether NULL is allowed.

7.7 Viewing All Tables

SHOW TABLES;

Quick Revision Tables

Table 1: DBMS Terminology

Table 2: Common MySQL Data Types

Mind Map

Important Diagrams (SVG)

Diagram 1: Structure of a Relational Table

Diagram 2: Foreign Key Relationship

Common Mistakes

1. Confusing a primary key with a foreign key; the primary key is unique in its own table while a foreign key refers to another table's primary key.
2. Allowing NULL or duplicate values in a primary key column.
3. Mixing up CHAR and VARCHAR; CHAR is fixed-length while VARCHAR is variable-length.
4. Forgetting to run USE database; before creating tables, so tables are created in the wrong database.
5. Not terminating SQL statements with a semicolon ;.
6. Confusing SHOW DATABASES; with SHOW TABLES;; one lists databases and the other lists tables in the current database.
7. Using DESC for something other than viewing table structure.
8. Entering dates in the wrong format; MySQL expects YYYY-MM-DD for the DATE type.

Exam Tips

1. Memorise the definitions of record, field, cardinality, and degree; these terms are frequently tested.
2. Learn the difference between CHAR and VARCHAR with a simple example.
3. Remember that SQL statements end with a semicolon.
4. Practise writing CREATE TABLE statements with appropriate data types.
5. Be able to explain why a primary key cannot be NULL or duplicate.
6. Know the default date format of MySQL: YYYY-MM-DD.

Conclusion

A database provides an organised and reliable way to store and manage data, and MySQL is the most popular open-source RDBMS for learning relational databases. The relational model stores data in tables made of records and fields, and keys such as the primary key and foreign key guarantee uniqueness and maintain relationships between tables. Choosing correct data types and mastering basic commands like CREATE DATABASE, USE, CREATE TABLE, and DESC build the foundation for writing powerful queries. The next chapter takes these fundamentals forward to retrieving, filtering, and manipulating data using SQL.