Homework Assignment: Introduction to Database Management Systems

Overview

This assignment consists of two independent tasks designed to enhance your understanding of SQL and NoSQL database management systems. You will create a task management database using PostgreSQL and implement a Python script to interact with MongoDB.

---

Task 1: PostgreSQL Database for Task Management System

Objectives

• Design and implement a relational database schema for a task management system.
• Perform essential SQL queries to interact with the database.

Requirements

Database Schema

• users table:
  • id: Primary key, auto-increment (SERIAL).
  • fullname: Full name of the user (VARCHAR(100)).
  • email: Unique email address (VARCHAR(100)).
• status table:
  • id: Primary key, auto-increment (SERIAL).
  • name: Unique status name (VARCHAR(50)). Example statuses: new, in progress, completed.
• tasks table:
  • id: Primary key, auto-increment (SERIAL).
  • title: Task title (VARCHAR(100)).
  • description: Task description (TEXT).
  • status_id: Foreign key referencing status.id (INTEGER).
  • user_id: Foreign key referencing users.id (INTEGER).

Constraints

• Unique constraints on users.email and status.name.
• Cascade delete: Deleting a user removes all their tasks.

Deliverables

• Python scripts:
  1. Schema creation script: Generates the required tables.
  2. Data population script: Uses the Faker library to seed tables with sample data.
  3. SQL queries script: Executes the following operations:
     • Retrieve tasks by user.
     • Retrieve tasks by status.
     • Update task status.
     • Find users without tasks.
     • Add new tasks.
     • Retrieve incomplete tasks.
     • Delete tasks by ID.
     • Search users by email.
     • Update user information.
     • Count tasks by status.
     • Filter tasks by email domain.
     • List tasks without descriptions.
     • Join users and tasks with a specific status.
     • Group users and count tasks.

Results

---

Task 2: MongoDB CRUD Operations

Objectives

• Implement basic CRUD operations using MongoDB and the PyMongo library.

Requirements

Database Structure

Each document should include the following fields:

{
    "_id": ObjectId("example_id"),
    "name": "barsik",
    "age": 3,
    "features": ["plays with toys", "friendly", "orange"]
}

-	_id: Unique identifier for each document (auto-generated by MongoDB).
-	name: Name of the cat (string).
-	age: Age of the cat (integer).
-	features: List of characteristics of the cat (array of strings).

Python Script Deliverables

The Python script should include the following functionalities:

Read Operations 1. Fetch all documents from the collection: Retrieve all records currently stored in the MongoDB collection. 2. Retrieve a specific document by the name of the cat: Allow users to search for a document by providing the cat’s name.

Update Operations 1. Update the age of a cat by its name: Modify the age field of a specific document based on the cat’s name. 2. Add a new feature to a cat’s features list by its name: Append a new value to the features array of a specific document based on the cat’s name.

Results