Database Design Essentials: ER Diagrams and SQL Mastery

Posted on Jan 28, 2026 in Computers

ER Diagram Notations: Chen’s vs. Crow’s Foot

When designing an ER (Entity-Relationship) Diagram, there are two primary sets of symbolic notations: Chen’s Notation (traditional/academic) and Crow’s Foot Notation (modern/industry standard).

1. Chen’s Notation (Conceptual Focus)

Peter Chen’s original 1976 notation is highly detailed and uses distinct geometric shapes for every component. It is the gold standard for learning the theoretical foundations of databases.

Core Symbols in Chen’s Notation

Rectangle: Represents a Strong Entity (e.g., STUDENT).
Double Rectangle: Represents a Weak Entity (e.g., DEPENDENT).
Diamond: Represents a Relationship between entities (e.g., “Enrolls”).
Double Diamond: Represents an Identifying Relationship for weak entities.
Oval: Represents an Attribute.
Underlined Oval: Represents the Primary Key.
Double Oval: Represents a Multi-valued Attribute (e.g., Phone_Numbers).
Dashed Oval: Represents a Derived Attribute (e.g., Age calculated from DOB).

Participation and Cardinality in Chen’s

Cardinality: Written as letters/numbers (1, N, M) above the lines connecting entities to relationships.
Total Participation: Represented by a double line (an entity must participate).
Partial Participation: Represented by a single line (participation is optional).

2. Crow’s Foot Notation (Implementation Focus)

Also known as IE (Information Engineering) notation, this is the most common style used in modern software (like MySQL Workbench or Lucidchart) because it is compact and easier to read for large systems.

Core Symbols in Crow’s Foot Notation

Box: Represents an entity. Attributes are usually listed inside the box rather than in separate ovals.
Lines: Relationships are shown as direct lines between boxes. The symbols at the end of the lines indicate cardinality.

Cardinality Symbols (The “End Caps”)

Crow’s Foot uses four specific combinations at the end of relationship lines:

Symbol	Meaning	Logic
—O—<	Zero or Many	Minimum 0, Maximum Many (Optional)
—I—<	One or Many	Minimum 1, Maximum Many (Mandatory)
—I—I	One and Only One	Minimum 1, Maximum 1 (Mandatory)
—O—I	Zero or One	Minimum 0, Maximum 1 (Optional)

Choosing the Right Notation

Use Chen’s Notation if you are in an academic setting or need to emphasize the meaning of complex attributes (like derived or multi-valued ones).
Use Crow’s Foot if you are designing a database for production, as it mirrors the actual table structure and saves space.

Structured Query Language (SQL) Fundamentals

SQL (Structured Query Language) is the standard programming language specifically designed for managing and manipulating data held in a Relational Database Management System (RDBMS). Think of it as the universal language that allows a human or an application to “talk” to databases like MySQL, PostgreSQL, Oracle, or SQL Server.

1. Purpose and Need for SQL

Before SQL, accessing data required complex programming knowledge of how data was physically stored. SQL changed this by being declarative—you tell the system what you want, not how to get it.

Data Retrieval: Efficiently fetching specific records from millions of rows using the SELECT command.
Data Integrity: Defining rules (constraints) to ensure data stays accurate and consistent.
Standardization: Since it is a standardized language, skills learned on one database (like SQL Server) are largely transferable to others (like MySQL).
Security: SQL allows administrators to set permissions, ensuring only authorized users can see or modify sensitive data.
Scalability: SQL is designed to handle massive amounts of data and complex relationships that simple spreadsheets (like Excel) cannot manage.

2. Common Data Types in SQL

When you create a table, you must define what “type” of data each column will hold. This optimizes storage and prevents errors (like trying to save a name in a “Price” column).

Numeric Types

INT / INTEGER: For whole numbers (e.g., Age, Quantity).
DECIMAL(p,s) / NUMERIC: For exact fixed-point numbers. Used for money (e.g., Price).
FLOAT / REAL: For approximate floating-point numbers used in scientific calculations.

Character/String Types

CHAR(n): Fixed-length string. If you define CHAR(10) and type “Hi”, it stores “Hi” plus 8 spaces.
VARCHAR(n): Variable-length string. If you define VARCHAR(10) and type “Hi”, it only stores 2 characters. This is the most common type for names and emails.
TEXT: For very long strings of data (e.g., product descriptions).

Date and Time Types

DATE: Stores YYYY-MM-DD.
TIME: Stores HH:MM:SS.
DATETIME / TIMESTAMP: Stores both date and time (e.g., Created_At logs).

Miscellaneous Types

BOOLEAN: Stores TRUE or FALSE.
BLOB (Binary Large Object): For storing images, videos, or compiled code.

3. Detailed SQL Sub-Language Definitions

SQL is categorized into four main sub-languages based on their specific functionality. While they all use the same basic syntax, they serve different purposes—from defining the structure to controlling security.

1. DDL (Data Definition Language)

Purpose: Used to define, modify, and manage the structure (schema) of database objects like tables, indexes, and views. Changes made via DDL are usually auto-committed (saved permanently immediately).

CREATE: To create a new database or table.
ALTER: To modify an existing database object (e.g., adding a column).
DROP: To delete an entire table or database structure.
TRUNCATE: To remove all records from a table while keeping the structure intact.
RENAME: To rename an existing object.

2. DML (Data Manipulation Language)

Purpose: Used to manage and manipulate the data stored within the tables. Unlike DDL, these changes are often not auto-committed and can be rolled back if a transaction is used.

INSERT: To add new rows of data into a table.
UPDATE: To modify existing data within a table.
DELETE: To remove specific rows from a table (usually used with a WHERE clause).

3. DQL (Data Query Language)

Purpose: Dedicated strictly to retrieving data. It is the most frequently used component of SQL. While technically sometimes grouped under DML, it is distinct because it does not change the data—it only “reads” it.

SELECT: To fetch specific data from one or more tables.
Example: SELECT name, age FROM students WHERE grade = 'A';

4. DCL (Data Control Language)

Purpose: Used by Database Administrators (DBAs) to manage security and permissions. It controls who can see or modify the data.

GRANT: To give specific users permission to perform tasks (like SELECT or UPDATE).
REVOKE: To take away permissions previously granted to a user.

SQL Component Comparison Table

Component	Full Name	Primary Focus	Key Commands
DDL	Data Definition Language	Structure (The “Box”)	CREATE, ALTER, DROP
DML	Data Manipulation Language	Content (The “Items”)	INSERT, UPDATE, DELETE
DQL	Data Query Language	Retrieval (The “View”)	SELECT
DCL	Data Control Language	Security (The “Lock”)	GRANT, REVOKE

Note: You might also encounter TCL (Transaction Control Language), which includes commands like COMMIT and ROLLBACK to manage the changes made by DML statements.

Advanced SQL Operations and Performance

Moving from database design into implementation requires mastering the core operations of SQL. This involves retrieving data, combining tables, and optimizing performance.

1. Basic Query Components

The foundation of SQL is the SELECT statement. It allows you to pull specific data from your tables.

SELECT: Specifies which columns to see.
FROM: Specifies the table.
WHERE: Filters rows based on a condition.
ORDER BY: Sorts the results (ASC or DESC).
GROUP BY: Aggregates data (e.g., counting students per class).

2. Join Operations

Since relational databases split data into multiple tables to avoid redundancy, Joins are used to link them back together using common columns (usually Primary and Foreign Keys).

INNER JOIN: Returns records that have matching values in both tables.
LEFT (OUTER) JOIN: Returns all records from the left table, and matched records from the right; if no match, the result is NULL.
RIGHT (OUTER) JOIN: Returns all records from the right table, and matched records from the left.
FULL (OUTER) JOIN: Returns all records when there is a match in either table.

3. Sub-queries (Nested Queries)

A Sub-query is a query nested inside another query. The inner query runs first and passes its result to the outer query.

Example: Finding all employees whose salary is higher than the average salary.

SELECT name FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);

4. Views (Virtual Tables)

A View is a “virtual table.” It doesn’t store data itself but displays the results of a saved SQL query.

Purpose: To simplify complex queries or hide sensitive columns (like salaries) from certain users while allowing them to see other employee data.
Syntax: CREATE VIEW view_name AS SELECT ...

5. Specifying Indexes for Performance

Indexes are used to speed up data retrieval. Think of an index like the index at the back of a textbook; instead of scanning every page (a “Table Scan”), the database jumps straight to the data.

Trade-off: Indexes make SELECT faster but make INSERT and UPDATE slower because the index must be updated every time data changes.
Syntax: CREATE INDEX index_name ON table_name (column_name);