Generally, relational databases are optimized for retrievals, and NOT for modifications. However, they deplete the performance in INSERT, UPDATE and DELETE. An INDEX can be built on unique-value column ( UNIQUE KEY or UNIQUE INDEX) or non-unique-value column ( KEY or INDEX). UNIQUE KEY (dept_name) - Build INDEX on this unique-value column
PRIMARY KEY (dept_no), - Index built automatically Table ' departments' CREATE TABLE departments (ĭept_no CHAR(4) NOT NULL, - in the form of 'dxxx' There are 300,024 records for this table. PRIMARY KEY (emp_no) - Index built automatically on primary-key column - INDEX (first_name) Gender ENUM ('M','F') NOT NULL, - Enumeration of either 'M' or 'F' There are 6 tables as follows: Table ' employees' CREATE TABLE employees (Įmp_no INT NOT NULL, - UNSIGNED AUTO_INCREMENT? This is a rather simple database with 6 tables but with millions of records. Reference: MySQL's Sample Employees Database. There are many excellent and interesting sample databases available, that you can use as a template (or pattern) to design your own databases.
