Creating a Relational Database

Sql tutorial · PySpark.in

6.2 Creating a Relational Database

After designing the ER Model, the next step is converting it into a Relational Database.

A relational database stores data in the form of:

• Tables
• Rows
• Columns

Each table represents an entity, and relationships are created using keys.

ER Model to Relational Database Mapping

Example Mapping

ER Model

Entity Type → Customer
Attributes → id, name, age
Key Attribute → id

Relational Table

Here:

• Customer → Table
• id, name, age → Columns
• id → Primary Key

Primary Key

A Primary Key is a column (or combination of columns) that uniquely identifies each row in a table.

Characteristics of Primary Key

A primary key:

• Must contain unique values
• Cannot contain NULL values
• Identifies each row uniquely

Example

Customer Table

Here:

id

is the primary key because every customer has a unique ID.

Primary Key Syntax

```

hide

CREATE TABLE table_name (
column_name data_type NOT NULL PRIMARY KEY
);

```

Example

```

sql

CREATE TABLE customer (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(250),
age INTEGER
);

```

Foreign Key

A Foreign Key is a column in one table that refers to the primary key of another table.

Foreign keys are used to create relationships between tables.

Why Foreign Keys Are Important

Foreign keys help us:

• Maintain relationships between tables
• Prevent invalid data
• Maintain referential integrity

Example

Consider two tables:

Customer Table

Address Table

Here:

customer_id

in the address table refers to:

customer.id

Therefore:

customer_id → Foreign Key

Foreign Key Syntax

```

hide

CREATE TABLE table2 (
c1 t1 NOT NULL PRIMARY KEY,
c2 t2,
FOREIGN KEY(c2)
REFERENCES table1(c3)
ON DELETE CASCADE
);

```

Understanding Foreign Key Syntax

FOREIGN KEY(c2)

Defines c2 as a foreign key.

REFERENCES table1(c3)

Means:

c2 values must exist in table1.c3

This prevents invalid references.

Example

If customer IDs are:

1
2
3

Then:

customer_id = 5

is invalid because customer 5 does not exist.

ON DELETE CASCADE

ON DELETE CASCADE automatically deletes child rows when the parent row is deleted.

Example

Suppose:

Customer Table

Address Table

If customer 1 is deleted:

```

hide

DELETE FROM customer
WHERE id = 1;

```

Then all related addresses are also deleted automatically.

Why CASCADE Is Useful

It helps avoid:

• Orphan records
• Invalid references
• Manual cleanup

Enabling Foreign Keys in SQLite

In SQLite, foreign key constraints can be enabled using:

PRAGMA foreign_keys = ON;

On many platforms, this setting is already enabled by default.

Creating Tables in Relational Database

Let us now create tables for the E-Commerce application discussed earlier.

1. Customer Table

Stores customer information.

Query

```

sql

CREATE TABLE customer (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(250),
age INTEGER
);

```

Table Structure

2. Product Table

Stores product details.

Query

```

sql

CREATE TABLE product (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(250),
price INTEGER,
brand VARCHAR(250),
category VARCHAR(150)
);

```

Table Structure

3. Address Table

Stores customer addresses.

A customer can have multiple addresses.

This creates a:

One-to-Many Relationship

between:

Customer → Address

Query

```

sql

CREATE TABLE address (
id INTEGER NOT NULL PRIMARY KEY,
pin_code INTEGER,
door_no VARCHAR(250),
city VARCHAR(250),
customer_id INTEGER,
FOREIGN KEY(customer_id)
REFERENCES customer(id)
ON DELETE CASCADE
);

```

Relationship Representation

customer.id
↓
address.customer_id

4. Cart Table

Each customer has one cart.

This creates a:

One-to-One Relationship

between:

Customer → Cart

Query

```

sql

CREATE TABLE cart (
id INTEGER NOT NULL PRIMARY KEY,
customer_id INTEGER NOT NULL,
total_price INTEGER,
FOREIGN KEY(customer_id)
REFERENCES customer(id)
ON DELETE CASCADE
);

```

5. Cart Product Table (Junction Table)

A cart can contain multiple products.

A product can exist in multiple carts.

This creates a:

Many-to-Many Relationship

between:

Cart ↔ Product

Why We Need a Junction Table

Relational databases cannot directly store many-to-many relationships.

Therefore, we create an intermediate table called a:

Junction Table

Cart Product Table

Query

```

sql

CREATE TABLE cart_product (
id INTEGER NOT NULL PRIMARY KEY,
cart_id INTEGER,
product_id INTEGER,
quantity INTEGER,
FOREIGN KEY(cart_id)
REFERENCES cart(id)
ON DELETE CASCADE,

FOREIGN KEY(product_id)
REFERENCES product(id)
ON DELETE CASCADE
);

```

Relationship Representation

cart.id
↓
cart_product.cart_id

product.id
↓
cart_product.product_id

Why Quantity Is Stored in Junction Table

Quantity belongs to the relationship between:

Cart ↔ Product

Example:

The quantity is specific to:

• Which cart
• Which product

Therefore it is stored inside the junction table.

Complete Database Structure

Visual Relationship Overview

Customer 1 -------- 1 Cart
Customer 1 -------- M Address
Cart M -------- N Product

More Sql tutorials

• Introduction to Databases
• Introduction To DBMS
• Introduction To SQL
• Natural Join
• INNER JOIN
• LEFT JOIN

All tutorials · Try the free PySpark compiler · Practice challenges