Lab Manual – Week 1

Course: Advanced Database Management Systems (Practical)

Topic: Advanced Data Models (ERD, Object-Relational Mapping)

Objective

The goal of this lab is to introduce advanced data modeling concepts, focusing on Entity-
Relationship Diagrams (ERD) and Object-Relational Mapping (ORM). Students will learn how to
design structured databases using ERD and implement mappings to relational databases using
ORM concepts.

Introduction to Advanced Data Models

Entity-Relationship Diagrams (ERD)

A database is a structured collection of data that helps store, organize, and manage
information e iciently. Before designing a database, it is essential to create a conceptual
model that visually represents how di erent pieces of data relate to each other. This is where
Entity-Relationship Diagrams (ERD) come into play.

An ERD is a diagram that illustrates the structure of a database, showing entities (objects)
and their relationships. It is widely used in database design to ensure a clear understanding
of how data is organized before implementing it in a Database Management System (DBMS).

Key Components of ERD

1. Entities: Objects or things in the database (e.g., Student, Course, Teacher).

2. Attributes: Characteristics or properties of an entity (e.g., Student has a Name, Email, and
ID).

3. Primary Key (PK): A unique identifier for each record in an entity (e.g., Student_ID).

4. Foreign Key (FK): A reference to another table’s primary key, establishing relationships
between entities (e.g., Course_ID in an Enrollment table).

5. Relationships: Connections between entities (e.g., A Student enrolls in a Course).

Types of Relationships in ERD

 One-to-One (1:1): One entity is linked to only one other entity. Example: Each passport
belongs to only one person.

 One-to-Many (1:M): One entity is linked to multiple records in another entity. Example: A
teacher teaches multiple students.

 Many-to-Many (M:M): Multiple entities are associated with multiple other entities.
Example: A student enrolls in multiple courses, and a course has multiple students.
Benefits of Using ERD

 Clear Visualization: Helps in understanding how data is structured.

 Reduces Redundancy: Avoids duplicate data and ensures e icient storage.

 Ensures Data Integrity: Defines strong relationships between tables.

 Acts as a Blueprint: Provides a guide before implementing the database.

Object-Relational Mapping (ORM)

When working with databases in programming languages like Python, Java, or C#, developers
often need to write SQL queries to interact with the database. However, manually writing SQL
queries can be time-consuming and complex.

Object-Relational Mapping (ORM) is a technique that simplifies database interactions by

representing database tables as objects in a programming language. This allows developers
to use object-oriented programming principles instead of writing raw SQL queries.

How ORM Works

1. Classes Represent Tables: Each database table is represented as a class in the

programming language.

2. Class Attributes Represent Columns: The fields in a table become class attributes.

3. Relationships Are Mapped: Foreign key relationships are defined using object references.

4. CRUD Operations Are Simplified: ORM allows Create, Read, Update, and Delete (CRUD)
operations using programming language syntax instead of SQL.

Popular ORM Libraries

 SQLAlchemy (Python) – Used with Flask and Django frameworks.

 Hibernate (Java) – A widely used ORM framework in Java applications.

 Entity Framework (C#) – Used in .NET applications for database management.

Benefits of Using ORM

 Simplifies Development: No need to manually write complex SQL queries.

 Increases Productivity: Speeds up database operations by using object-oriented code.

 Reduces Errors: Prevents common SQL mistakes such as syntax errors.

 Improves Security: Helps protect against SQL injection attacks.

Importance of ERD and ORM in Modern Databases

Both ERD and ORM play a crucial role in modern database development:

1. ERD ensures well-structured database design, making data management e icient and
organized.

2. ORM allows seamless interaction between applications and databases, reducing

development complexity.

3. Modern frameworks like Django, Spring Boot, and [Link] heavily rely on ORM to
manage databases.

Practical Implementation of Advanced Data Models

In this section, we will implement a Student Course Management System using:

 Entity-Relationship Diagram (ERD) to design the database schema

 SQL queries to create tables and define relationships

 Object-Relational Mapping (ORM) with SQLAlchemy (Python) to interact with the

database

Required Software:

1. Python (3.x)

o Required for running SQLAlchemy ORM.

2. SQLite (or any other DBMS like MySQL/PostgreSQL)

o SQLite is lightweight and easy to use for this lab.

o No separate installation is needed for SQLite; it comes with Python.

o If using MySQL/PostgreSQL, install their respective database servers.

3. SQLAlchemy (Python ORM)

o Install via pip:

pip install sqlalchemy

4. A Code Editor (Choose One)

o VS Code

o PyCharm

o Jupyter Notebook

5. Git (Optional, for version control)

1. Project Setup:
File Directory Structure

We will organize our project as follows:

student_course_management/

│── models/

│ ├── [Link] # Database connection setup

│ ├── [Link] # ORM Model for Student

│ ├── [Link] # ORM Model for Course

│ ├── [Link] # ORM Model for Enrollment

│── [Link] # Main script to test ORM

│── [Link] # Dependencies (SQLAlchemy, SQLite)

2. Database Schema Design (ERD to SQL)

We will first design an Entity-Relationship Diagram (ERD) for our Student Course Management
System. The system consists of three main entities:

1. Student (Student_ID, Name, Email, Contact, Address)

2. Course (Course_ID, Course_Name, Credit_Hours, Instructor)

3. Enrollment (Enrollment_ID, Student_ID, Course_ID, Enrollment_Date, Grade)

SQL Schema (Manual Table Creation)

CREATE TABLE Student (

Student_ID INT PRIMARY KEY,

Name VARCHAR(100),

Email VARCHAR(100) UNIQUE,

Contact VARCHAR(15),

Address TEXT

);

CREATE TABLE Course (

 Course_ID INT PRIMARY KEY,

Course_Name VARCHAR(100),

Credit_Hours INT,

Instructor VARCHAR(100)

);

CREATE TABLE Enrollment (

Enrollment_ID INT PRIMARY KEY,

Student_ID INT,

Course_ID INT,

Enrollment_Date DATE,

Grade CHAR(2),

FOREIGN KEY (Student_ID) REFERENCES Student(Student_ID),

FOREIGN KEY (Course_ID) REFERENCES Course(Course_ID)

);

3. ORM Implementation Using SQLAlchemy (Python)

Instead of manually handling database queries, we will use Object-Relational Mapping (ORM)
with SQLAlchemy to interact with the database.

Step 1: Install Dependencies

First, install the required library:

pip install sqlalchemy

Step 2: Database Connection Setup

Create a file models/[Link] to define the database connection.

from sqlalchemy import create_engine

from [Link] import sessionmaker, declarative_base

 # SQLite database (can be changed to MySQL/PostgreSQL)

DATABASE_URL = "sqlite:///[Link]"

# Create engine

engine = create_engine(DATABASE_URL, echo=True)

# Create session

SessionLocal = sessionmaker(bind=engine)

# Base class for ORM models

Base = declarative_base()

Step 3: Define ORM Models

Student Model (models/[Link])

from sqlalchemy import Column, Integer, String

from [Link] import Base

class Student(Base):

__tablename__ = 'students'

student_id = Column(Integer, primary_key=True)

name = Column(String(100), nullable=False)

email = Column(String(100), unique=True, nullable=False)

contact = Column(String(15))

address = Column(String(255))

Course Model (models/[Link])

from sqlalchemy import Column, Integer, String

from [Link] import Base

 class Course(Base):

__tablename__ = 'courses'

course_id = Column(Integer, primary_key=True)

course_name = Column(String(100), nullable=False)

credit_hours = Column(Integer, nullable=False)

instructor = Column(String(100), nullable=False)

Enrollment Model (models/[Link])

from sqlalchemy import Column, Integer, Date, ForeignKey

from [Link] import Base

class Enrollment(Base):

__tablename__ = 'enrollments'

enrollment_id = Column(Integer, primary_key=True)

student_id = Column(Integer, ForeignKey('students.student_id'))

course_id = Column(Integer, ForeignKey('courses.course_id'))

enrollment_date = Column(Date)

grade = Column(String(2))

Step 4: Create Tables in Database

In [Link], we will create the database and generate tables using our ORM models.

from [Link] import Base, engine

# Create all tables

[Link].create_all(engine)

print("Database tables created successfully.")

Run the script:

python [Link]

This will create the SQLite database [Link] with the Student, Course, and Enrollment
tables.

4. Performing CRUD Operations Using ORM:

We will now implement Create, Read, Update, and Delete (CRUD) operations in our database
using ORM.

Insert Data (Create Operation)

In [Link], we can insert records into the database:

from [Link] import SessionLocal

from [Link] import Student

from [Link] import Course

from [Link] import Enrollment

from datetime import date

# Start session

session = SessionLocal()

# Insert Student

student1 = Student(name="John Doe", email="johndoe@[Link]",

contact="123456789", address="Islamabad")

[Link](student1)

# Insert Course

course1 = Course(course_name="Advanced Databases", credit_hours=3, instructor="Mr.

Umar Naseer")

[Link](course1)

# Commit changes
 [Link]()

print("Student and Course added successfully.")

Retrieve Data (Read Operation)

We can fetch records from the database using ORM queries.

# Fetch all students

students = [Link](Student).all()

for student in students:

print(f"ID: {student.student_id}, Name: {[Link]}, Email: {[Link]}")

Update Data (Update Operation)

Updating a student’s contact information:

# Fetch student by ID

student = [Link](Student).filter_by(student_id=1).first()

if student:

[Link] = "987654321"

[Link]()

print("Student contact updated successfully.")

Delete Data (Delete Operation)

Deleting a record from the database:

# Delete a student by ID

student_to_delete = [Link](Student).filter_by(student_id=1).first()

if student_to_delete:

[Link](student_to_delete)

[Link]()

print("Student deleted successfully.")

 Lab Task: Employee & Department Management System
Scenario:

You are tasked with designing a simple database model using SQLAlchemy for managing employees
and departments in a company. Follow the steps below to create and manage the database.

Task 1: Define Models

Create two models:

1. Department

o department_id (Primary Key)

o department_name (Unique, String)

2. Employee

o employee_id (Primary Key)

o name (String)

o email (Unique, String)

o department_id (Foreign Key → Department)

Task 2: Create the Database Tables

 Use SQLAlchemy to create the tables.

Task 3: Insert Sample Data

 Add two departments:

o IT

o HR

 Add three employees, assigning them to departments.

Task 4: Retrieve & Display Data

 Print all employees along with their department names.

Task 5: Update an Employee's Department

  Change an employee's department from HR to IT.

Task 6: Delete a Department

 Delete a department only if it has no employees.

Bonus Challenge:

 Add a salary field to the Employee model and update one employee’s salary.