Mastering ER Modeling: A Step-by-Step Guide with Real-World Examples (Part 2 of 5)

Refine the Conceptual ERD to Logical ERD

The logical ERD includes a more formal, detailed notation emphasizing tables, columns, keys, and relationships. Specifically, the data types of the columns are presented. Besides, labels are added to the relationships (“rents,” “rented by,” and “requires”) to indicate the nature of each relationship.

Refine the Logical ERD to Physical ERD

The physical ERD is a ‘database schema diagram’ that provides details enough to implement the database in a specific DBMS. The naming of entities and columns is revised to:

• Support DBMS-specific syntax.
• Ensure the use of the non-reserved words (e.g. order -> purchase_order, desc -> description)
• Fit organizational-specific naming convention

The column ‘desc’ in the entity Maintenance is renamed to ‘description’.

Database Schema Generation: Transforming ERD into a Physical Schema

The database schema is a visual representation of the database structure that defines the organization of data in a relational database management system. The schema is created based on the entity-relationship diagram (ERD) that is used to model the relationships between different entities in a system.

In this case, the ERD was first refined into a logical ERD that defined the relationships between entities such as Cars, Customers, Rentals, and Maintenance, and their respective attributes. The logical ERD helped to establish the relationships and cardinality between the entities.

After refining the logical ERD, the next step was to generate a physical ERD that defines the actual database schema with all the necessary data types, primary and foreign keys, and any constraints. The physical ERD reflects the logical ERD but with more detail on how the data is stored in the database.

Based on the physical ERD, the SQL code was written to create the database schema with the appropriate tables, columns, and relationships between them. Each table in the schema represents an entity in the system, and the columns represent the attributes of that entity. The primary keys were defined to uniquely identify each record in the table, and foreign keys were used to establish relationships between tables.

CREATE TABLE Car (
car_id INT PRIMARY KEY,
model VARCHAR(255),
make VARCHAR(255),
year INT,
daily_rate DECIMAL(10, 2),
fuel_type VARCHAR(255),
is_available BOOLEAN
);

CREATE TABLE Customer (
customer_id INT PRIMARY KEY,
first_name VARCHAR(255),
last_name VARCHAR(255),
email VARCHAR(255),
phone_number VARCHAR(255),
address VARCHAR(255)
);

CREATE TABLE Rental (
rental_id INT PRIMARY KEY,
start_date DATE,
end_date DATE,
rental_rate DECIMAL(10, 2),
insurance_charge DECIMAL(10, 2),
fuel_charge DECIMAL(10, 2),
car_id INT,
customer_id INT,
FOREIGN KEY (car_id) REFERENCES Car(car_id),
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);

CREATE TABLE Maintenance (
maintenance_id INT PRIMARY KEY,
type VARCHAR(255),
description TEXT,
cost DECIMAL(10, 2),
maintenance_date DATE,
car_id INT,
FOREIGN KEY (car_id) REFERENCES Car(car_id)
);

Summary