Relational Algebra: Schema Design

Analysis and Relational Schemas

Query 1:

π_name (σ_city='Chittagong'(Students))

Analysis: The Students relation has attributes: student_id, name, city.

Relational Schema:

Students(student_id, name, city, ...)

Primary Key: student_id

Query 2:

π_product_name (σ_price>500(Products))

Analysis: The Products relation has attributes: product_id, product_name, price.

Relational Schema:

Products(product_id, product_name, price, ...)

Primary Key: product_id

Query 3:

π_name (σ_specialization='Cardiology' ∨ experience<3 (Doctors))

Analysis: The Doctors relation has attributes: doctor_id, name, specialization, experience.

Relational Schema:

Doctors(doctor_id, name, specialization, experience, ...)

Primary Key: doctor_id

Query 4:

π_title (σ_release_year≥2020(Movies))

Analysis: The Movies relation has attributes: movie_id, title, release_year.

Relational Schema:

Movies(movie_id, title, release_year, ...)

Primary Key: movie_id

Query 5:

π_employee_name (σ_department='IT' ∧ salary>80000 (Employees))

Analysis: The Employees relation has attributes: employee_id, employee_name, department, salary.

Relational Schema:

Employees(employee_id, employee_name, department, salary, ...)

Primary Key: employee_id

Summary of Relational Schemas

1️⃣ Students(student_id, name, city, ...)
   Primary Key: student_id

2️⃣ Products(product_id, product_name, price, ...)
   Primary Key: product_id

3️⃣ Doctors(doctor_id, name, specialization, experience, ...)
   Primary Key: doctor_id

4️⃣ Movies(movie_id, title, release_year, ...)
   Primary Key: movie_id

5️⃣ Employees(employee_id, employee_name, department, salary, ...)
   Primary Key: employee_id

Purpose of Each Relation

• Students: Stores information about students and their location.
• Products: Stores product catalog details.
• Doctors: Stores doctor details, specialization, and experience.
• Movies: Stores movie details including title and release year.
• Employees: Stores employee details, department, and salary.