Relational Database Design: ER Modeling, Normalization, Storage Engine, Character Set, Data Types, and Index Design

This article discusses relational database design using MySQL, covering the concepts of Entity‑Relationship (E‑R) modeling, normalization forms such as Boyce‑Codd (BCNF) and Third Normal Form (3NF), and how to translate an E‑R diagram of a student‑course selection scenario into relational schemas.

It explains the purpose of normalization, presents functional dependencies for Student, Course, and Enrollment relations, and shows how BCNF and 3NF are applied, including a counter‑example with the Banker schema.

The article then examines MySQL storage engine choices, comparing InnoDB and MyISAM, and provides criteria for selecting an engine based on transaction needs and backup considerations.

Guidelines for choosing character sets, data types (CHAR vs VARCHAR, TEXT/BLOB, numeric types), and best practices for column definitions are presented, with a sample MySQL command illustrating the difference between CHAR and VARCHAR values:

CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
INSERT INTO vc VALUES ('ab  ', 'ab  ');
SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;

Index design principles such as selecting columns used in WHERE clauses, using unique and short indexes, leveraging left‑most prefixes, and avoiding over‑indexing are outlined, followed by a concrete example of the student‑course schema with tables Student, Course, and SC (enrollment) and their design rationales.

References to standard textbooks and MySQL documentation are listed at the end.