Relational database concepts in MySQL - Step-by-Step Execution

Concept Flow - Relational database concepts

Start: Define Tables

↓

Create Rows (Records)

↓

Use Columns (Fields) to store data

↓

Set Primary Key to uniquely identify rows

↓

Establish Relationships between tables

↓

Query data using SQL

↓

Retrieve, Insert, Update, Delete data

↓

End

This flow shows how relational databases organize data into tables with rows and columns, use keys to link tables, and allow data operations through queries.

Execution Sample

MySQL

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(50),
  Age INT
);

INSERT INTO Students VALUES (1, 'Alice', 20);

This code creates a table named Students with three columns and inserts one row of data.

Execution Table

Step	Action	Result	Notes
1	Create table Students	Table Students created with columns StudentID, Name, Age	Defines structure for student data
2	Insert row (1, 'Alice', 20)	Row added to Students table	Data stored in table
3	Query SELECT * FROM Students	Returns one row: (1, 'Alice', 20)	Retrieves stored data
4	Add Primary Key on StudentID	StudentID uniquely identifies each row	Ensures no duplicate StudentID
5	End	No further actions	Process complete

💡 All steps executed to create table, insert data, and query data successfully

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
Students Table	None	Created with columns StudentID, Name, Age	Contains 1 row: (1, 'Alice', 20)	Query returns 1 row	Table exists with 1 row

Key Moments - 3 Insights

Why do we need a Primary Key in a table?

The Primary Key uniquely identifies each row, preventing duplicates and helping link tables. See execution_table step 4 where Primary Key is added to StudentID.

What happens if we insert two rows with the same Primary Key?

The database will reject the second insert because Primary Keys must be unique. This is implied by the uniqueness rule in step 4.

How does a table store data?

Data is stored as rows, each with values for the columns defined. Step 2 shows inserting one row with values for StudentID, Name, and Age.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result after Step 2?

A Table Students deleted

B One row added to Students table

C Primary Key removed

D No data inserted

💡 Hint

Check the 'Result' column in execution_table row for Step 2

At which step is the Primary Key set on StudentID?

A Step 4

B Step 1

C Step 2

D Step 3

💡 Hint

Look for the action mentioning Primary Key in execution_table

If we insert another row with StudentID = 1, what will happen?

A Table structure will change

B Row will be added successfully

C Database will reject due to duplicate Primary Key

D Primary Key will be removed automatically

💡 Hint

Refer to key_moments about Primary Key uniqueness

Concept Snapshot

Relational databases store data in tables with rows and columns.
Each table has a Primary Key to uniquely identify rows.
Tables can relate to each other using keys.
Data is manipulated using SQL commands: CREATE, INSERT, SELECT, UPDATE, DELETE.
Primary Keys prevent duplicate records and help maintain data integrity.

Full Transcript

Relational databases organize data into tables made of rows and columns. Each table has a Primary Key column that uniquely identifies each row to avoid duplicates. Data is added as rows with values for each column. Relationships between tables are created using keys. SQL commands allow creating tables, inserting data, and querying data. This visual trace showed creating a Students table, inserting one student, setting a Primary Key, and querying the data. Understanding Primary Keys and table structure is key to using relational databases effectively.

Practice

1. What is the main purpose of a primary key in a relational database table?

easy

A. To speed up database backups

B. To store large text data

C. To link two tables together

D. To uniquely identify each record in the table

Solution

1. Step 1: Understand the role of primary keys

   A primary key is a column or set of columns that uniquely identifies each row in a table.

2. Step 2: Compare options with the definition

   Only To uniquely identify each record in the table describes this unique identification role correctly.

3. Final Answer:

   To uniquely identify each record in the table -> Option D

4. Quick Check:

   Primary key = unique record ID [OK]

Hint: Primary key means unique ID for each row [OK]

Common Mistakes:

• Confusing primary key with foreign key
• Thinking primary key stores large data
• Assuming primary key speeds backups

2. Which of the following is the correct syntax to declare a primary key on the column id in a MySQL table?

easy

A. PRIMARY KEY (id);

B. KEY PRIMARY id;

C. PRIMARY KEY id;

D. PRIMARY id KEY;

Solution

1. Step 1: Recall MySQL primary key syntax

   In MySQL, the primary key is declared using PRIMARY KEY (column_name).

2. Step 2: Match syntax with options

   PRIMARY KEY (id); matches the correct syntax with parentheses around the column name.

3. Final Answer:

   PRIMARY KEY (id); -> Option A

4. Quick Check:

   Primary key syntax uses parentheses [OK]

Hint: Primary key syntax uses parentheses around column [OK]

Common Mistakes:

• Omitting parentheses around column name
• Swapping order of keywords
• Using incorrect keywords like KEY PRIMARY

3. Given two tables: Customers(id, name) and Orders(order_id, customer_id), which SQL query correctly retrieves all orders with the customer names?

medium

A. SELECT Orders.order_id, Customers.name FROM Orders JOIN Customers ON Orders.id = Customers.customer_id;

B. SELECT Orders.order_id, Customers.name FROM Orders JOIN Customers ON Orders.customer_id = Customers.id;

C. SELECT order_id, name FROM Orders, Customers WHERE Orders.id = Customers.id;

D. SELECT order_id, name FROM Orders LEFT JOIN Customers ON Orders.customer_id = Customers.order_id;

Solution

1. Step 1: Identify the correct join condition

   The foreign key customer_id in Orders links to id in Customers.

2. Step 2: Check each option's join condition

   SELECT Orders.order_id, Customers.name FROM Orders JOIN Customers ON Orders.customer_id = Customers.id; correctly joins on Orders.customer_id = Customers.id. Others use wrong columns or join types.

3. Final Answer:

   SELECT Orders.order_id, Customers.name FROM Orders JOIN Customers ON Orders.customer_id = Customers.id; -> Option B

4. Quick Check:

   Join on foreign key = primary key [OK]

Hint: Join tables on foreign key = primary key [OK]

Common Mistakes:

• Swapping join columns incorrectly
• Using WHERE instead of JOIN condition properly
• Mixing up LEFT JOIN with INNER JOIN

4. You wrote this SQL to create a foreign key: FOREIGN KEY (customer_id) REFERENCES Customers(customer_id). But it gives an error. What is the likely cause?

medium

A. You must declare foreign keys before primary keys

B. Foreign keys cannot reference columns in the same table

C. The referenced column customer_id does not exist or is not a primary key in Customers

D. Foreign keys must reference columns named 'id' only

Solution

1. Step 1: Understand foreign key requirements

   A foreign key must reference a column that exists and is a primary or unique key in the referenced table.

2. Step 2: Analyze the error cause

   If Customers has no customer_id column or it is not a key, the foreign key declaration fails.

3. Final Answer:

   The referenced column customer_id does not exist or is not a primary key in Customers -> Option C

4. Quick Check:

   Foreign key must reference existing primary key [OK]

Hint: Foreign key must reference existing primary key column [OK]

Common Mistakes:

• Referencing non-existent columns
• Assuming foreign keys must reference 'id' column
• Incorrect order of key declarations

5. You have two tables: Employees(emp_id, name) and Departments(dept_id, dept_name, manager_id). The manager_id in Departments references emp_id in Employees. Which of these statements is TRUE about this relationship?

hard

A. An employee can manage multiple departments, but a department can have only one manager

B. Each department must have exactly one manager who is an employee

C. A department can have multiple managers if multiple employees share the same emp_id

D. The manager_id column in Departments can be NULL only if emp_id is NULL in Employees

Solution

1. Step 1: Understand foreign key relationship

   The manager_id in Departments points to emp_id in Employees, linking each department to one employee as manager.

2. Step 2: Analyze relationship cardinality

   One department has one manager, but an employee can manage many departments (one-to-many).

3. Step 3: Evaluate options

   An employee can manage multiple departments, but a department can have only one manager correctly states this one-to-many relationship. Each department must have exactly one manager who is an employee is wrong because departments can have one manager, but not necessarily exactly one if NULL allowed. A department can have multiple managers if multiple employees share the same emp_id is wrong because emp_id is unique. The manager_id column in Departments can be NULL only if emp_id is NULL in Employees is incorrect because NULL in manager_id is independent of emp_id NULL.

4. Final Answer:

   An employee can manage multiple departments, but a department can have only one manager -> Option A

5. Quick Check:

   One-to-many foreign key = An employee can manage multiple departments, but a department can have only one manager [OK]

Hint: Foreign key one-to-many means one employee, many departments [OK]

Common Mistakes:

• Assuming multiple managers per department
• Confusing NULL constraints
• Thinking emp_id can be duplicated