Programming Concepts: Python and MySQL Study Notes

MySQL Database Design, Schemas and Data Definition

1. From information to a relational database

Suppose a company keeps employee names, salaries and departments in separate spreadsheets. Different sheets may spell a department differently. Deleting a department may leave employees referring to a department that no longer exists. A database provides an organized place to store this information and rules for keeping it consistent.

A database management system (DBMS) is software that creates, stores, retrieves and controls access to databases. MySQL is a relational DBMS. A relational database organizes information into related tables. SQL, or Structured Query Language, is the language used to describe tables and work with their data. MySQL is the software; SQL is the language; company_db can be one database managed by that software.

A MySQL server can manage several databases. Each database can contain several tables and other objects. In MySQL, CREATE SCHEMA and CREATE DATABASE are synonyms. More generally, the word schema also means the design of a database: its tables, columns, types and relationships.

Tables, rows and columns

Consider this department table:

A row, also called a record or tuple, describes one department. A column, also called an attribute or field, describes one property shared by departments. Here the table has three rows and two columns.

The degree of a relation is its number of columns. Its cardinality, in this table-counting sense, is its number of rows. In relationship design, cardinality instead describes how many entities can be related, such as one department to many employees. Context distinguishes the meanings.

The table definition exists independently of the rows. Immediately after creation, a table can have two columns and zero rows. A table also has no guaranteed display order: a query needs ORDER BY when a particular result order matters.

2. Designing entities, attributes and relationships

An entity is something about which information is stored, such as an employee, department or course. An attribute describes an entity, such as an employee's name. An entity–relationship model, or ER model, describes these entities and how they relate before the SQL tables are created.

Choosing attributes

A simple attribute is treated as one value, such as a department code. A composite attribute can be divided into useful parts: an address can become street, city and postal code columns. A multivalued attribute can have several values for one entity, such as several phone numbers for one employee. Store these as separate related rows when each number must be searched or managed independently, rather than putting a comma-separated list in one column.

A stored attribute is kept directly, such as date of birth. A derived attribute is calculated from other information, such as age calculated from date of birth and the current date. Storing age without updating it can make it incorrect over time.

In common ER notation, rectangles represent entities, ovals represent attributes and diamonds represent relationships. An underlined identifying attribute denotes a key. The useful result is a clear statement of the data rules, not merely a diagram.

One-to-many relationship

One department can have many employees. In this design, each employee belongs to at most one department:

~~~text Department (one) ───────── Employee (many) department_id department_id refers to Department ~~~

Place the department identifier in the employee table. Do not add employee1, employee2 and employee3 columns to the department table: a department may eventually have more employees than those columns allow.

Whether an employee must belong to a department is a separate rule. If membership is optional, the employee's department column can be NULL. If membership is mandatory, add NOT NULL.

One-to-one relationship

Suppose each employee can have at most one locker and each locker can be assigned to at most one employee. A locker table can contain an employee foreign key with a UNIQUE constraint. The foreign key checks that the employee exists; uniqueness prevents the same employee from being assigned to multiple lockers. Whether unassigned lockers are permitted determines whether that column allows NULL.

Many-to-many relationship

An employee may join several projects, and a project may have several employees. Use a third table, often called a junction or bridge table:

~~~text employee ──< employee_project >── project employee_id project_id ~~~

Each bridge row represents one assignment. A combined primary key on (employee_id, project_id) prevents the same assignment from being entered twice while allowing an employee or project to appear in many different assignments.

Strong and weak entities

A strong entity has its own identifying key. A weak entity depends on an owner entity for identification. For example, an order line numbered 1 is not globally unique; (order_id, line_number) identifies it. The owner key and the line's partial identifier form a composite key. The line also refers back to its order through a foreign key.

3. Keys identify rows and connect tables

A superkey is a set of attributes sufficient to identify a row uniquely. A candidate key is a minimal such set: removing an attribute would lose that identifying ability. One candidate key is selected as the primary key; the others are alternate keys.

For example, if every employee has a unique employee ID and a mandatory unique email address, either can be a candidate key. Choosing the ID as primary does not remove the need to enforce uniqueness on email when that business rule matters.

Primary key

A primary key uniquely identifies a row and cannot contain NULL. A table has at most one primary-key constraint, but that constraint may include several columns. A primary key on two columns is one composite key, not two independent primary keys.

For (employee_id, project_id), pairs (101, 1) and (101, 2) are distinct and allowed. A second (101, 1) is rejected. Both component values must be present.

Choose stable identifiers: names can change and different people can share a name. MySQL can create a table without an explicitly declared primary key, but declaring a suitable one makes row identity clear. UNIQUE NOT NULL can enforce candidate-key properties without becoming the table's declared primary key.

Foreign key

A foreign key in a child table refers to a key in a parent table. It enforces referential integrity: a non-NULL child reference must identify an existing parent.

Many employee rows can contain department ID 10; repeated foreign-key values are expected in a one-to-many relationship. An employee cannot refer to department 99 when that department does not exist. A nullable foreign key can contain NULL, meaning that no department has been assigned.

The child and parent column names need not be identical. Their definitions must be compatible; for integer keys, use matching type and signedness. Use InnoDB for both tables and reference a primary or appropriate unique key. A foreign key is not automatically unique or mandatory.

4. Choosing column data types

A data type states what kind of value a column stores. Choose it from the meaning and required range of the information.

DECIMAL(10,2) has eight digits available before the decimal point. It does not mean ten digits before and two after. Use an exact decimal type for money when exact decimal arithmetic is required; floating-point types represent approximate numeric values. In strict SQL mode (the default in MySQL 8 and later), 99999999.99 fits a DECIMAL(10,2) column, while 999999999.99 is rejected with Out of range value.

CHAR versus VARCHAR

Both store text, but they handle length differently:

The following was executed on MySQL 9.3 in strict mode:

~~~sql CREATE TABLE char_demo (c CHAR(5), v VARCHAR(5)); INSERT INTO char_demo VALUES ('ab ', 'ab '); SELECT CONCAT('[', c, ']') AS c_val, CONCAT('[', v, ']') AS v_val, LENGTH(c) AS c_len, LENGTH(v) AS v_len FROM char_demo; ~~~

The CHAR value loses its trailing spaces on retrieval; the VARCHAR value keeps them. In both cases n is a maximum: inserting 'abcdefg' into CHAR(5) fails in strict mode with Data too long for column. In older non-strict configurations MySQL could silently truncate the value and only issue a warning, which is one reason strict mode is recommended.

A phone number is usually text, not an arithmetic quantity. Text preserves leading zeros and permits a country prefix. VARCHAR(20) can be more suitable than INT for it. Maximum character length is not a universal byte-storage estimate because character sets can use multiple bytes per character.

NULL is missing information

NULL represents missing, unknown or inapplicable information. It is not the number zero and is not the empty string ''. An unknown salary and a known salary of zero communicate different facts.

Identifiers and values

An identifier names a database object, such as employee_name. Use simple names with letters, digits and underscores, and use a consistent lowercase convention. Avoid reserved SQL words as names. MySQL uses backticks to quote an identifier when necessary; single quotes delimit string values.

SQL keywords such as CREATE are case-insensitive. Database and table name case behaviour can depend on the operating system and MySQL configuration. Do not assume that Employee and employee will always refer to the same table on every server.

5. Constraints enforce data rules

A constraint makes the database check a rule rather than relying only on a form or application to check it.

In MySQL, a nullable UNIQUE column can contain multiple NULL entries. Add NOT NULL if the value must always be supplied. A unique department name prevents duplicate names but does not itself prohibit missing names.

DEFAULT supplies a value when a column is omitted or the SQL keyword DEFAULT is explicitly used. It does not mean “replace any invalid input with this value.” A nullable column explicitly assigned NULL does not become its default simply because a default exists.

MySQL versions before 8.0.16 parsed certain CHECK declarations without enforcing them. The examples here assume MySQL 8.0.16 or later with enforced CHECK constraints and InnoDB tables. CHECK (salary >= 0) rejects negative salary. A NULL salary produces an unknown result, which does not violate this check; add NOT NULL separately when unknown salaries must also be prohibited.

6. Creating a database and its tables

Data Definition Language (DDL) describes database structure. CREATE, ALTER and DROP are DDL operations. They differ from changing a salary value or adding an employee row, which are data-manipulation operations.

~~~sql CREATE DATABASE company_db; USE company_db; SELECT DATABASE(); ~~~

The first statement creates a database. USE selects it as the default database for subsequent unqualified table names in this connection. SELECT DATABASE() returns company_db. Creating a database alone does not necessarily select it.

Creating, inspecting and modifying a database safely

Running CREATE DATABASE company_db; a second time fails with an error such as ERROR 1007: Can't create database 'company_db'; database exists. Scripts that may run more than once use IF NOT EXISTS:

~~~sql CREATE DATABASE IF NOT EXISTS company_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; SHOW WARNINGS; SHOW CREATE DATABASE company_db; ~~~

If the database already exists, the statement completes with a note-level warning (code 1007) instead of an error, and the existing database and its tables are left unchanged. IF NOT EXISTS does not compare or update settings: an existing database keeps its current character set even if the statement requests another.

SHOW CREATE DATABASE displays the statement that would recreate the database definition, including its default character set and collation. To change those defaults later, modify the database itself:

~~~sql ALTER DATABASE company_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ~~~

ALTER DATABASE changes database-level defaults used by tables created afterwards. It does not rename the database, and it does not convert columns of existing tables. MySQL has no supported RENAME DATABASE statement; renaming is done by creating a new database and moving or reloading tables.

Now create the parent table first:

~~~sql CREATE TABLE department ( department_id INT PRIMARY KEY, department_name VARCHAR(50) NOT NULL, CONSTRAINT uq_department_name UNIQUE (department_name) ) ENGINE = InnoDB;

CREATE TABLE employee ( employee_id INT PRIMARY KEY, employee_name VARCHAR(80) NOT NULL, salary DECIMAL(10,2), department_id INT, employment_status VARCHAR(12) NOT NULL DEFAULT 'active', CONSTRAINT chk_employee_salary CHECK (salary >= 0), CONSTRAINT fk_employee_department FOREIGN KEY (department_id) REFERENCES department (department_id) ON DELETE RESTRICT ) ENGINE = InnoDB; ~~~

Each comma separates a column definition or table constraint. Constraint names make later inspection and modification easier. The department table must exist before this foreign key can reference it.

This design permits an unknown salary and an unassigned department, but it requires an employee ID and name. An omitted employment status becomes 'active'. The foreign key blocks department values that have no parent row.

Following successful and rejected inserts

The following statements demonstrate the table rules; INSERT adds rows:

~~~sql INSERT INTO department (department_id, department_name) VALUES (10, 'Engineering'), (20, 'Support'), (30, 'HR');

INSERT INTO employee (employee_id, employee_name, salary, department_id) VALUES (101, 'Asha', 60000.00, 10), (102, 'Ravi', 45000.00, 20), (103, 'Meera', 70000.00, 10), (104, 'Kabir', NULL, NULL), (105, 'Neha', 45000.00, 20); ~~~

There are now three departments and five employees. Each employee's status is active because that column was omitted. Kabir's row succeeds because both salary and department are nullable.

Consider these independent attempts against that data:

Mapping a many-to-many relationship into DDL

~~~sql CREATE TABLE project ( project_id INT PRIMARY KEY, project_name VARCHAR(80) NOT NULL ) ENGINE = InnoDB;

CREATE TABLE employee_project ( employee_id INT, project_id INT, PRIMARY KEY (employee_id, project_id), FOREIGN KEY (employee_id) REFERENCES employee (employee_id), FOREIGN KEY (project_id) REFERENCES project (project_id) ) ENGINE = InnoDB; ~~~

The bridge has two foreign keys and one composite primary key. The primary key makes both columns non-NULL. Having an employee and a project separately in their parent tables does not automatically create an assignment; the bridge row records that relationship.

7. What happens when a parent row is deleted?

The foreign-key declaration can specify a referential action:

If a foreign key is declared without any ON DELETE or ON UPDATE clause, MySQL records the action as NO ACTION. For InnoDB, NO ACTION behaves the same as RESTRICT: the parent change is rejected while matching child rows exist. On MySQL 9.3, a foreign key declared with no clauses was reported in information_schema.REFERENTIAL_CONSTRAINTS as DELETE_RULE = NO ACTION, and deleting a referenced parent failed with error 1451 (Cannot delete or update a parent row).

SET NULL needs nullable child columns. CASCADE does not delete every child row: it affects those referencing the deleted parent. Deleting a child does not, by this rule, delete its parent. Select an action from the business meaning; deleting a department should not automatically imply deleting all employment records.

ON UPDATE controls a change to the referenced parent key. An ON UPDATE CASCADE rule propagates the changed key to matching children. Therefore a primary key is not inherently unchangeable; references and the configured action determine whether an update is permitted.

8. Inspecting the schema

~~~sql SHOW DATABASES; SHOW TABLES; DESCRIBE employee; SHOW CREATE TABLE employee; ~~~

SHOW DATABASES lists databases visible to the account. SHOW TABLES lists tables in the selected database. DESCRIBE summarizes columns, types, nullability, key indicators and defaults. SHOW CREATE TABLE shows the actual definition, including named constraints and storage-engine information. Use it when checking whether a foreign key or check constraint is really present.

In DESCRIBE, PRI indicates a primary-key column, UNI a qualifying unique-key indication, and MUL an index permitting repeated values. These indicators summarize indexing; MUL alone is not proof that a foreign-key constraint exists.

Copying a table definition

~~~sql CREATE TABLE employee_copy LIKE employee; ~~~

This creates an empty table using the source column definitions and indexes. It does not copy the employee rows, and MySQL does not copy foreign-key definitions with LIKE. On MySQL 8.0.16 and later, CHECK constraints are copied but receive generated names such as employee_copy_chk_1; the index that supported the foreign key is copied as an ordinary index without the foreign-key rule. It is therefore not a complete backup of the original table and its relationships.

To copy the rows as well, a second statement is needed, for example INSERT INTO employee_copy SELECT * FROM employee;. Data manipulation is covered in the next chapter.

9. Changing a table with ALTER TABLE

Suppose employee email addresses must now be recorded:

~~~sql ALTER TABLE employee ADD COLUMN email VARCHAR(100); ALTER TABLE employee ADD CONSTRAINT uq_employee_email UNIQUE (email); ~~~

The first statement adds a nullable column; existing rows initially have NULL there. The second prevents duplicate non-NULL email values. Multiple existing NULL values do not violate MySQL's unique constraint.

To increase the permitted name length:

~~~sql ALTER TABLE employee MODIFY COLUMN employee_name VARCHAR(120) NOT NULL; ~~~

MODIFY changes the definition, not one employee's name. Restate attributes such as NOT NULL that must remain: omitted column attributes are not all automatically retained during redefinition. Changing the stored name of employee 101 instead requires UPDATE.

Other structural examples, treated independently, are:

~~~sql ALTER TABLE employee RENAME COLUMN email TO work_email; ALTER TABLE employee DROP INDEX uq_employee_email; ALTER TABLE employee DROP COLUMN work_email; ~~~

Renaming changes the identifier. Dropping the unique index removes its uniqueness rule. Dropping the column removes that column's stored values, not the employee rows. MySQL also supports column placement with FIRST or AFTER in appropriate ADD/MODIFY operations; changing display position is not the same as sorting rows.

RENAME COLUMN is available from MySQL 8.0. Older servers, and many existing scripts, use CHANGE COLUMN, which renames and redefines the column in one statement, so the full definition must be restated:

~~~sql ALTER TABLE employee CHANGE COLUMN email work_email VARCHAR(100); ~~~

Renaming and emptying whole tables

A table is renamed with either statement below; both keep the rows, indexes and constraints:

~~~sql RENAME TABLE employee_copy TO employee_archive; ALTER TABLE employee_archive RENAME TO employee_copy; ~~~

TRUNCATE TABLE is also classified as DDL in MySQL. It removes all rows at once, keeps the table definition and resets an AUTO_INCREMENT counter. It has no WHERE clause, causes an implicit commit, and fails on a parent table that is referenced by another table's foreign key. The comparison with DELETE and DROP is developed in the data-manipulation chapter.

A constraint can be added to an existing table only if its data satisfies that constraint. Existing duplicate IDs must be resolved before adding a primary key. Adding a foreign key can fail if existing child rows refer to missing parents.

10. Removing objects with DROP

~~~sql DROP TABLE IF EXISTS employee_copy; ~~~

DROP TABLE removes the table definition and its rows. IF EXISTS prevents an error merely because that table is absent; it does not preserve data or provide recovery. SQL execution does not inherently display an interactive confirmation prompt.

When removing related tables, remove child tables before the parents they reference, unless the dependencies are deliberately changed first. For the design above, employee_project must be handled before employee or project.

~~~sql DROP DATABASE company_db; DROP DATABASE IF EXISTS company_db; SHOW DATABASES LIKE 'company_db'; ~~~

The first statement removes the database and its contained tables. Run against a database that no longer exists, plain DROP DATABASE fails with ERROR 1008: Can't drop database 'company_db'; database doesn't exist, whereas DROP DATABASE IF EXISTS completes without an error. The final SHOW DATABASES LIKE returns an empty result, confirming the removal. DROP SCHEMA is a synonym for DROP DATABASE. Ordinary MySQL DDL such as these CREATE, ALTER and DROP operations causes implicit transaction commits; it cannot be treated like an ordinary row edit that can simply be undone with ROLLBACK.

The correct command depends on whether the requirement changes a value, a set of rows, a column rule or the existence of the table itself.

11. A disciplined database-design process

Good table definitions are the result of decisions, not of typing CREATE TABLE first. Start with a short description of the information system. For the running example, imagine that a training company needs to record students, courses, batches, instructors and enrolments. It wants to answer questions such as:

• Which students are enrolled in a particular batch?
• Which course does a batch deliver?
• Which instructor is assigned to that batch?
• Has one student been enrolled in the same batch twice?
• Can a course be removed while batches still refer to it?

A useful design process has the following stages.

1. Identify the facts that must be stored. A student has an identifier, name, email and joining date. A course has a code, title and duration. A batch belongs to a course and has a start date. An enrolment connects a student and a batch.
2. Identify entities. A student, course and batch have their own identity, so each becomes a candidate entity. An enrolment is also important: it represents an event or association and may later need attributes such as enrolment date or result.
3. List attributes and data rules. Ask whether each value is required, whether it can repeat, its valid form and whether it changes often. For example, a student's email may be required and unique; a course title may be required but not necessarily globally unique.
4. Decide relationships and cardinalities. One course can have many batches. One batch can have many enrolments. One student can have many enrolments. A student and a batch therefore have a many-to-many relationship, resolved by the enrolment table.
5. Choose keys. Give every main entity a stable primary key. Use a composite key or a surrogate key for the enrolment according to the business rule. Add foreign keys to preserve the relationships.
6. Choose types and constraints. A date needs a date type, an amount needs an exact decimal type, and an identifier needs a type/range that supports its planned values. Constraints express the rules discovered earlier.
7. Test ordinary and bad data. Insert valid data, then deliberately try duplicate, missing and invalid relationships. A design that only works for the first happy-path row is not finished.
8. Inspect and document the result. Use SHOW CREATE TABLE, diagrams and meaningful names so another administrator can understand the design.

From requirements to relationships

The following diagram is not SQL. It is a compact way to reason about the planned data before creating it.

~~~text Course (one) ─────< Batch (many)

v Student (one) ────< Enrolment >──── Batch (one) many many ~~~

Read the lower part in both directions: one student can occur in many enrolment rows, and one batch can occur in many enrolment rows. The enrolment table is not an accidental technical table. It represents the real fact “this student joined this batch.”

A relationship has two separate questions:

• Maximum cardinality: can one occurrence be associated with one, many, or no occurrences on the other side?
• Optionality: must the association exist now?

For example, a batch must normally refer to one course, so its course_id is NOT NULL. A course can exist before its first batch is planned, so it can have zero batch rows. Making every foreign key NOT NULL merely because foreign keys are important is a design error; optionality comes from the business rule.

Repeating groups are a design warning

Suppose a first attempt creates the following table:

~~~text student(student_id, student_name, course_1, course_2, course_3) ~~~

This design creates several problems. There is an artificial limit of three courses, a query must inspect several columns to find a course, and deleting one course leaves a confusing empty position. The repeated course columns reveal a one-to-many or many-to-many relationship that should be represented by rows.

Similarly, do not store a comma-separated value such as "Python, Linux, Networking" in one skills column when individual skills must be searched, validated or related to courses. A separate table makes each meaningful fact addressable.

Avoiding update anomalies

An anomaly is an unwanted side effect caused by storing one fact in several places.

This is the practical reason for separating related entities. The immediate syllabus goal is sound table design, not memorizing normal-form labels. A good question is: “Where should this fact be stored so that it is stored once at the right level?”

12. A complete training database: design, DDL and inspection

The following schema develops the design above. It is deliberately small enough to read, while showing parent tables, a one-to-many relationship and a many-to-many relationship.

Step 1: create and select the database

~~~sql CREATE DATABASE training_portal CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

USE training_portal; SELECT DATABASE(); ~~~

The character set describes how text is encoded. utf8mb4 is a Unicode-capable MySQL character set. Collation supplies comparison and sorting rules for text; exact available collation names depend on the MySQL release. For a first design, the important point is to choose text handling deliberately rather than assuming every user name can be represented by a narrow legacy encoding.

Expected result of SELECT DATABASE():

The selected database is a connection setting. A second client connection must select a database for itself or use qualified names such as training_portal.student.

Step 2: create independent parent tables

Create parent tables before tables that refer to them.

~~~sql CREATE TABLE course ( course_id INT UNSIGNED AUTO_INCREMENT, course_code VARCHAR(20) NOT NULL, course_title VARCHAR(120) NOT NULL, duration_days SMALLINT UNSIGNED NOT NULL, active BOOLEAN NOT NULL DEFAULT TRUE, CONSTRAINT pk_course PRIMARY KEY (course_id), CONSTRAINT uq_course_code UNIQUE (course_code), CONSTRAINT chk_course_duration CHECK (duration_days > 0) ) ENGINE = InnoDB;

CREATE TABLE student ( student_id INT UNSIGNED AUTO_INCREMENT, full_name VARCHAR(100) NOT NULL, email VARCHAR(254) NOT NULL, joined_on DATE NOT NULL, phone VARCHAR(20), CONSTRAINT pk_student PRIMARY KEY (student_id), CONSTRAINT uq_student_email UNIQUE (email) ) ENGINE = InnoDB; ~~~

Read one declaration at a time.

• INT UNSIGNED AUTO_INCREMENT creates a non-negative integer identifier whose next value MySQL can generate when the column is omitted from an insert. It is an implementation choice for a surrogate key, not proof that a business identifier is unnecessary.
• course_code is a candidate key because a valid course code is intended to be unique. It is declared UNIQUE while the numeric course_id is selected as the primary key.
• SMALLINT UNSIGNED expresses a limited, non-negative whole-number duration. The CHECK adds the stronger rule that zero is also invalid.
• active BOOLEAN is a readable declaration for a Boolean-style value in MySQL. MySQL treats BOOLEAN as a synonym for TINYINT(1), so the declaration does not by itself limit values to 0 and 1. If that rule matters, state it with a check constraint.
• VARCHAR(254) limits the stored email text length; it does not verify that an address belongs to the person who supplied it.

Step 3: create a one-to-many child table

A batch cannot exist without its course in this design.

~~~sql CREATE TABLE batch ( batch_id INT UNSIGNED AUTO_INCREMENT, course_id INT UNSIGNED NOT NULL, batch_code VARCHAR(30) NOT NULL, start_date DATE NOT NULL, capacity SMALLINT UNSIGNED NOT NULL, CONSTRAINT pk_batch PRIMARY KEY (batch_id), CONSTRAINT uq_batch_code UNIQUE (batch_code), CONSTRAINT chk_batch_capacity CHECK (capacity BETWEEN 1 AND 200), CONSTRAINT fk_batch_course FOREIGN KEY (course_id) REFERENCES course (course_id) ON UPDATE RESTRICT ON DELETE RESTRICT ) ENGINE = InnoDB; ~~~

The foreign key belongs in batch because each batch stores the identity of its one course. The parent course table does not need one column for every batch. RESTRICT is chosen because deleting a course that still has planned batches should require a deliberate business process, such as cancelling or moving those batches first.

Step 4: map the many-to-many relationship

~~~sql CREATE TABLE enrolment ( student_id INT UNSIGNED NOT NULL, batch_id INT UNSIGNED NOT NULL, enrolled_on DATE NOT NULL, enrolment_status VARCHAR(15) NOT NULL DEFAULT 'active', CONSTRAINT pk_enrolment PRIMARY KEY (student_id, batch_id), CONSTRAINT chk_enrolment_status CHECK (enrolment_status IN ('active', 'cancelled', 'completed')), CONSTRAINT fk_enrolment_student FOREIGN KEY (student_id) REFERENCES student (student_id) ON DELETE RESTRICT, CONSTRAINT fk_enrolment_batch FOREIGN KEY (batch_id) REFERENCES batch (batch_id) ON DELETE RESTRICT ) ENGINE = InnoDB; ~~~

The composite primary key means that one student can be enrolled in several batches and one batch can contain several students, but the same student/batch pair cannot occur twice. enrolled_on and enrolment_status belong to this table because they describe the relationship, not the student alone and not the batch alone.

A different design could introduce enrolment_id as an auto-increment primary key and add UNIQUE (student_id, batch_id). That design is useful if other tables must refer to one enrolment row by a single-column key. The uniqueness rule is still needed; a surrogate key alone does not prevent duplicate associations.

Step 5: inspect what MySQL stored

~~~sql SHOW TABLES; DESCRIBE course; SHOW CREATE TABLE enrolment; ~~~

A typical SHOW TABLES result contains the four table names:

DESCRIBE course gives a compact column view. Its Null field indicates whether a column accepts NULL, Key may display PRI or UNI, and Default shows configured defaults. Use SHOW CREATE TABLE when you need the full truth about constraints, engine, index names and foreign-key actions. A GUI diagram can be useful, but the database definition is authoritative.

13. Inserting test data and proving the rules

DDL is not finished when it parses. Add a small, meaningful data set and observe the constraints.

~~~sql INSERT INTO course (course_code, course_title, duration_days) VALUES ('PGCP-ITISS', 'Post Graduate Certificate Programme in IT Infrastructure', 180), ('PY-BASICS', 'Python Foundations', 30);

INSERT INTO student (full_name, email, joined_on, phone) VALUES ('Asha Kulkarni', 'asha@example.test', '2026-02-01', '9876543210'), ('Ravi Shah', 'ravi@example.test', '2026-02-03', NULL), ('Meera Iyer', 'meera@example.test', '2026-02-05', '9123456789');

INSERT INTO batch (course_id, batch_code, start_date, capacity) VALUES (1, 'ITISS-FEB-26-A', '2026-02-15', 40), (1, 'ITISS-FEB-26-B', '2026-03-01', 35);

INSERT INTO enrolment (student_id, batch_id, enrolled_on) VALUES (1, 1, '2026-02-10'), (2, 1, '2026-02-10'), (1, 2, '2026-02-20'); ~~~

The generated IDs in this example assume a new empty database. In a real database, do not hard-code a generated ID merely because it happened to be 1 in an earlier session; retrieve or query the appropriate key according to the application workflow.

Now verify the stored relationships.

~~~sql SELECT s.full_name, c.course_code, b.batch_code, e.enrolled_on, e.enrolment_status FROM enrolment AS e JOIN student AS s ON s.student_id = e.student_id JOIN batch AS b ON b.batch_id = e.batch_id JOIN course AS c ON c.course_id = b.course_id ORDER BY s.full_name, b.start_date; ~~~

Expected logical result:

The query appears here only to verify the design. Detailed join reasoning belongs to the joins chapter. The key lesson is that one fact from each normalized table can be combined when needed without duplicating the course or student definition in every enrolment row.

Deliberately invalid operations

Run invalid examples one at a time in a lab. The exact error wording varies by MySQL version and client, so learn the violated rule rather than memorizing an error number.

~~~sql -- Duplicate candidate key: rejected. INSERT INTO student (full_name, email, joined_on) VALUES ('Another Asha', 'asha@example.test', '2026-02-06');

-- Missing parent course: rejected by the foreign key. INSERT INTO batch (course_id, batch_code, start_date, capacity) VALUES (99, 'UNKNOWN-01', '2026-03-10', 20);

-- Invalid capacity: rejected by the CHECK constraint. INSERT INTO batch (course_id, batch_code, start_date, capacity) VALUES (1, 'ITISS-INVALID', '2026-03-10', 0);

-- Duplicate association: rejected by the composite primary key. INSERT INTO enrolment (student_id, batch_id, enrolled_on) VALUES (1, 1, '2026-02-11'); ~~~

When these statements were executed on MySQL 9.3, the four errors were, in order: 1062 Duplicate entry 'asha@example.test' for key 'student.uq_student_email', 1452 Cannot add or update a child row: a foreign key constraint fails, 3819 Check constraint 'chk_batch_capacity' is violated and 1062 Duplicate entry '1-1' for key 'enrolment.PRIMARY'.

Notice a side effect: the rejected student insert still consumed an AUTO_INCREMENT value. Afterwards SHOW CREATE TABLE student reported AUTO_INCREMENT=5, although only three students exist, so the next student receives ID 5, not 4. Gaps in generated IDs are normal and are another reason not to treat surrogate keys as a count of rows.

A failed insert should not be “fixed” by deleting the constraint. First identify whether the proposed data is wrong, the business rule changed, or the model is missing a legitimate state. For example, if a learner must be permitted to cancel and re-enrol in the same batch with a complete history, the model may need an enrolment event table rather than a simple pair-unique association.

14. Schema change is an engineering change

Real systems evolve. A new requirement should be translated into a data rule before writing ALTER TABLE.

Example: add an optional emergency contact

The requirement is: “Record one emergency contact number when supplied. Existing students may not have one.”

~~~sql ALTER TABLE student ADD COLUMN emergency_phone VARCHAR(20) NULL AFTER phone; ~~~

The NULL choice follows the phrase “when supplied.” Existing rows remain valid because the new column accepts NULL. AFTER phone changes display order in the table definition; it does not change the order in which query results are returned.

Check the effect:

~~~sql DESCRIBE student; SHOW CREATE TABLE student; ~~~

Then populate a value using a data-manipulation statement, not another ALTER:

~~~sql UPDATE student SET emergency_phone = '9000000000' WHERE student_id = 1; ~~~

Example: strengthen a rule in stages

Suppose the organization decides that every student must have an email. A direct MODIFY ... NOT NULL can fail if old data contains NULL. Use a staged approach:

1. Inspect existing values and find rows with missing email.
2. Obtain or correct legitimate values according to the business process.
3. Verify there are no remaining NULL values.
4. Change the column definition.
5. Test a new insert without email and verify rejection.

~~~sql SELECT student_id, full_name FROM student WHERE email IS NULL;

ALTER TABLE student MODIFY COLUMN email VARCHAR(254) NOT NULL; ~~~

The MODIFY statement restates the desired type and nullability. Structural changes must be tested against existing data, dependent indexes, foreign keys and applications that expect the old shape.

Add, rename and remove with care

Before a destructive change, identify dependencies. A column may be used by a view, stored program, report, application, index or foreign key. A table may be the parent of a child foreign key. A successful command is not automatically a safe change.

15. Choosing types and constraints from meaning

Type choice is a modeling decision. The following cases occur frequently in beginner database design.

Identifiers

An internal numeric key is often INT UNSIGNED or BIGINT UNSIGNED depending on expected scale. A code such as "ITISS-FEB-26-A" contains letters and punctuation, so it is text. Do not store it as a number merely because it contains digits.

An auto-generated numeric key is convenient, but it does not validate a real-world code. Keep a separate unique constraint when the real code must also be unique.

Names, text and case rules

Use VARCHAR for bounded text such as a name, code or email. Use TEXT for longer unbounded-style content such as a description. Length is a business limit, not decoration: VARCHAR(2) is plausible for a state code but not for a full name.

Text equality and ordering use collation rules. A unique email column under a case-insensitive collation can treat two differently cased spellings as equal. Decide the intended behavior and test the actual server configuration; never assume text comparisons behave identically across all collations.

Numbers and money

Use an integer type for counted whole units. Use DECIMAL(p,s) when exact decimal places matter. In DECIMAL(10,2), ten is the total number of digits and two is the number after the decimal point, leaving up to eight digits before it. Use FLOAT or DOUBLE only when approximate binary floating-point behavior is appropriate.

A telephone number, PIN-like code, account number with leading zeros or formatted identifier is normally text. Arithmetic is not performed on it, and numeric storage can discard meaningful formatting.

Dates and times

Use DATE for a calendar date with no time of day. Use DATETIME or TIMESTAMP only when the requirement includes a time. A text value that looks like a date may sort incorrectly or accept invalid formats; a date type lets MySQL validate and compare it as a date.

Time-zone meaning is an application decision. A recorded local class start time and an audit event instant can need different treatment. Do not add timestamps everywhere without knowing why the time is stored.

NULL, empty and default are different

These three values are often confused:

A default does not repair every invalid value. For example, inserting a negative capacity does not cause MySQL to choose the default capacity; a check constraint should reject it. Explicitly inserting NULL into a nullable column stores NULL rather than the default.

16. Common design and DDL mistakes

Mistake: treating column names as relationships

Having columns named student_id and batch_id does not create referential integrity. The FOREIGN KEY ... REFERENCES ... declaration is what asks the database to enforce the parent-child rule. Conversely, a foreign key does not automatically create a join result; a query must state how to combine tables.

Mistake: using a name as the primary key without examining change

A name may look unique in a tiny sample, then collide or change. Use a stable identifier for row identity and enforce a separate unique business value when necessary. The right design depends on whether the value is truly stable and governed.

Mistake: confusing an index with a constraint

A primary-key or unique constraint usually has supporting index structures, but an ordinary index permits duplicates. DESCRIBE showing MUL means a non-unique indexed column; it does not prove the column is a foreign key. Use SHOW CREATE TABLE to inspect the actual relationship definition.

Mistake: creating child tables first

A child foreign key cannot reference a parent table that does not exist. Create independent parents first, then dependent children, and reverse the dependency direction when deliberately dropping objects.

Mistake: assuming every DDL error is syntax

A statement can have valid syntax but fail because existing data violates a new constraint, a referenced key is incompatible, the storage engine does not support the requested behavior, the account lacks privilege, or another object depends on the change. Read the full diagnostic, inspect the existing definition and test on a non-production copy.

Mistake: treating DROP as an ordinary undoable edit

CREATE, ALTER and DROP commonly cause implicit commits in MySQL. A transaction can protect selected InnoDB row changes, but it is not a backup or a universal reversal mechanism for schema operations. Plan a tested backup and change procedure before dropping a production object.

17. A practical verification checklist

After creating or changing a schema, verify more than “the command returned no error.”

1. Connect using the intended account and confirm the selected database.
2. List objects with SHOW DATABASES and SHOW TABLES as appropriate for that account.
3. Inspect each key table with DESCRIBE and SHOW CREATE TABLE.
4. Insert a small set of valid parent and child rows.
5. Test every intended constraint with one controlled invalid operation.
6. Query relationships to confirm that foreign-key values point to the expected parents.
7. Confirm defaults, generated IDs and NULL behavior with deliberate inserts.
8. Test a proposed ALTER on a disposable copy before changing a shared environment.
9. Record the schema version, commands and expected verification result.
10. Keep a recovery plan for destructive changes.

A well-designed database combines three protections: application validation provides a good user experience, database constraints preserve rules even when an application has a bug, and operational backup/recovery protects against broader loss. None of these protections replaces the others.

18. MySQL server, clients and first-account context

A MySQL server is the database service that stores data, enforces constraints and processes SQL statements. A client is a program that connects to that server and sends statements. MySQL Workbench, the mysql command-line client and an application using a database driver are different clients; they can all work with the same server.

~~~text Workbench / mysql client / application

v MySQL Server

v databases → tables → rows, constraints and indexes ~~~

The client is not the database. Closing Workbench does not remove a database, and a table created by one authorized client can be queried by another authorized client. This distinction helps diagnose common beginner problems:

• A client cannot connect: check server availability, host/port, account credentials and network policy before checking table syntax.
• A client connects but SHOW DATABASES does not list the expected database: check which account is connected and what privileges it has.
• A statement says “No database selected”: issue USE database_name or qualify the table name.
• An account can select rows but cannot create a table: authentication succeeded, but authorization is insufficient.

An account is commonly identified by a user name and a host pattern. The host part matters because an account allowed from localhost need not be permitted from another computer. Exact authentication plugins and account administration details depend on the MySQL version and deployment policy.

For a controlled lab, an administrator can create a limited user and give it access only to the training database:

~~~sql CREATE USER 'training_editor'@'localhost' IDENTIFIED BY 'choose-a-strong-lab-password';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP ON training_portal.* TO 'training_editor'@'localhost';

SHOW GRANTS FOR 'training_editor'@'localhost'; ~~~

Expected SHOW GRANTS output:

USAGE means "no privileges": every account has this line, and it only confirms that the account exists. The second line holds the actual database-level privileges.

training_portal.* means all tables in that one database. It does not mean every database on the server. The example is intentionally limited to the database needed for the exercise. A real application account should normally receive only the privileges it actually needs; it may need SELECT, INSERT and UPDATE but not DROP or user-administration privileges.

CREATE USER creates an account definition. GRANT assigns privileges. They are related operations but not synonyms. For example, creating an account does not automatically let it use a database, and a database/table privilege does not automatically let an account create other accounts.

To remove a lab account when it is no longer needed:

~~~sql DROP USER 'training_editor'@'localhost'; ~~~

Removing an account changes access, not the tables or rows that account previously created. Use this command carefully: active applications that use the account will no longer be able to connect.

Database names, object names and qualification

A fully qualified name identifies an object with its database:

~~~sql SELECT * FROM training_portal.course; ~~~

This statement can run without USE training_portal if the account has access. Qualification is useful in administration scripts because it makes the target explicit. Within a selected database, the shorter course refers to the same table.

Choose names that reveal meaning and remain consistent. For example, use singular table names such as student and course, or plural names such as students and courses, but avoid mixing conventions without reason. Use lower-case letters, digits and underscores rather than spaces, punctuation or ambiguous abbreviations. A good identifier does not replace documentation, but it reduces avoidable errors in SQL and application code.

19. Reading a DDL statement as a database contract

A table definition is a contract between people, applications and the database server. Each part answers a question:

When a requirement changes, identify which question changed. “Students can now have an emergency number” changes stored properties. “A student may now be unassigned to a batch until approval” changes relationship optionality. “Two departments may share a display name but must have different codes” changes a uniqueness rule. This reasoning prevents using a familiar SQL command for the wrong kind of problem.

A schema is successful when its table definitions make invalid states difficult or impossible to store while allowing every legitimate state required by the system.

20. Worked mini-case: library loans

Consider a library that lends physical copies of books. The requirement is not simply “store books and members.” The library must know which particular copy was borrowed, by whom, when it is due and when it was returned.

Identify the entities:

• A member has a membership number, name and email.
• A book describes a title, ISBN and publisher.
• A book_copy represents one physical copy with its own barcode. Several copies can refer to the same book.
• A loan represents one lending event between one member and one copy.

The relationships are:

~~~text Book (one) ─────< Book_Copy (many) Member (one) ───< Loan >──── Book_Copy (one) ~~~

A first DDL outline is:

~~~sql CREATE TABLE member ( member_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, member_name VARCHAR(100) NOT NULL, email VARCHAR(254) NOT NULL UNIQUE ) ENGINE = InnoDB;

CREATE TABLE book ( book_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, isbn VARCHAR(20) NOT NULL UNIQUE, title VARCHAR(200) NOT NULL ) ENGINE = InnoDB;

CREATE TABLE book_copy ( copy_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, book_id INT UNSIGNED NOT NULL, barcode VARCHAR(40) NOT NULL UNIQUE, FOREIGN KEY (book_id) REFERENCES book(book_id) ) ENGINE = InnoDB;

CREATE TABLE loan ( loan_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, member_id INT UNSIGNED NOT NULL, copy_id INT UNSIGNED NOT NULL, borrowed_on DATE NOT NULL, due_on DATE NOT NULL, returned_on DATE NULL, CHECK (due_on >= borrowed_on), FOREIGN KEY (member_id) REFERENCES member(member_id), FOREIGN KEY (copy_id) REFERENCES book_copy(copy_id) ) ENGINE = InnoDB; ~~~

The book title is stored once in book, while the barcode is stored once per physical copy. Loan dates belong to loan because they describe an event, not the member or book copy alone. A real library may also need a rule preventing two active loans for the same copy; that is a more specific business rule that must be designed and tested deliberately rather than assumed from the basic foreign keys.

This case illustrates a central design habit: identify the level at which each fact is true. A title is true of a book, a barcode is true of one copy, and a due date is true of one borrowing event.