Project Work

Overview

The DBMS project work involves creating a SQL-based mini project based on a real-world scenario. This project serves as a practical application of the database concepts learned throughout the course. It allows you to demonstrate your understanding of database design, implementation, querying, and management.

Project Objectives

Design and implement a relational database for a specific application domain
Apply normalization techniques to ensure efficient data organization
Implement database constraints to maintain data integrity
Create SQL queries to retrieve, manipulate, and analyze data
Develop a basic application interface or documentation that demonstrates database functionality

Suggested Project Domains

Here are some real-world scenarios that make excellent DBMS projects:

1. Student Information Management System

Track students, courses, enrollments, grades, faculty, and departments.

2. Inventory Management System

Manage products, categories, suppliers, purchases, and sales.

3. Hospital Management System

Organize patients, doctors, appointments, treatments, and billing.

4. Library Management System

Handle books, members, borrowing, returns, and fines.

5. E-commerce Platform

Manage products, customers, orders, payments, and shipping.

6. Banking System

Track accounts, customers, transactions, loans, and branches.

7. Employee Management System

Manage employees, departments, salaries, attendance, and performance.

8. Hotel Reservation System

Handle rooms, guests, bookings, services, and payments.

9. Railway/Airline Reservation System

Manage schedules, bookings, passengers, and payments.

10. Sports Tournament Management

Track teams, players, matches, venues, and results.

Project Development Phases

Phase 1: Requirements Analysis (15%)

Define Project Scope
- Identify the purpose and objectives of your database
- Determine the entities and relationships to be modeled
- Outline the main functionalities your database will support
Create User Stories or Use Cases
- Describe how users will interact with the database
- Identify the required queries and operations
Deliverable: Project Proposal Document (1-2 pages)
- Brief description of the domain and objectives
- List of main entities and their relationships
- Key functionalities to be implemented

Phase 2: Database Design (25%)

Conceptual Design
- Create an Entity-Relationship (ER) diagram
- Define entities, attributes, and relationships
Logical Design
- Transform the ER diagram into a relational schema
- Apply normalization (at least up to 3NF)
- Define primary keys, foreign keys, and constraints
Deliverable: Database Design Document
- ER diagram (hand-drawn or using tools like Lucidchart, draw.io)
- Relational schema with table definitions
- Normalization explanation

Phase 3: Database Implementation (30%)

Create Database Schema
- Write SQL DDL statements to create tables
- Implement constraints (primary keys, foreign keys, etc.)
- Create indexes for performance optimization
Insert Sample Data
- Populate the database with realistic sample data
- Ensure data covers various scenarios for testing
Deliverable: SQL Script Files
- Database creation script
- Data insertion script
- Any additional scripts for views, procedures, etc.

Phase 4: Query Development (20%)

Basic Queries
- Implement SELECT, INSERT, UPDATE, DELETE operations
- Create queries for filtering, sorting, and basic analysis
Advanced Queries
- Develop complex queries using joins, subqueries, and aggregation
- Create queries for generating reports and analytics
Deliverable: SQL Query Collection
- At least 15-20 queries demonstrating various SQL features
- Documentation explaining the purpose of each query

Phase 5: Documentation and Presentation (10%)

Comprehensive Documentation
- System overview
- Database schema explanation
- Query examples with expected outputs
- Instructions for setting up and using the database
Presentation Materials
- Slides or demo script
- Database snapshots or screenshots
Deliverable: Final Project Report and Presentation

Sample Project Structure: Library Management System

Entities and Relationships

Books
- ISBN, title, author, publisher, publication_date, category, price, etc.
Members
- Member_ID, name, address, phone, email, join_date, membership_type, etc.
Borrowing
- Borrowing_ID, book_ISBN, member_ID, borrow_date, due_date, return_date, etc.
Staff
- Staff_ID, name, position, salary, hire_date, etc.
Publishers
- Publisher_ID, name, address, contact_person, phone, etc.
Categories
- Category_ID, name, description, shelf_location, etc.

Sample SQL Tables

-- Create Books table
CREATE TABLE Books (
    ISBN VARCHAR(20) PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    Author VARCHAR(100) NOT NULL,
    Publisher_ID INT NOT NULL,
    Publication_Date DATE,
    Category_ID INT NOT NULL,
    Price DECIMAL(10, 2),
    Total_Copies INT NOT NULL,
    Available_Copies INT NOT NULL,
    FOREIGN KEY (Publisher_ID) REFERENCES Publishers(Publisher_ID),
    FOREIGN KEY (Category_ID) REFERENCES Categories(Category_ID)
);

-- Create Members table
CREATE TABLE Members (
    Member_ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Address VARCHAR(200),
    Phone VARCHAR(15),
    Email VARCHAR(100) UNIQUE,
    Join_Date DATE NOT NULL,
    Membership_Type VARCHAR(20) NOT NULL,
    Expiry_Date DATE NOT NULL
);

-- Create Borrowing table
CREATE TABLE Borrowing (
    Borrowing_ID INT PRIMARY KEY,
    ISBN VARCHAR(20) NOT NULL,
    Member_ID INT NOT NULL,
    Borrow_Date DATE NOT NULL,
    Due_Date DATE NOT NULL,
    Return_Date DATE,
    Fine_Amount DECIMAL(10, 2) DEFAULT 0,
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN),
    FOREIGN KEY (Member_ID) REFERENCES Members(Member_ID)
);

Sample Queries

-- Find all overdue books
SELECT b.Borrowing_ID, m.Name AS Member_Name, bk.Title, 
       b.Borrow_Date, b.Due_Date, 
       DATEDIFF(CURRENT_DATE, b.Due_Date) AS Days_Overdue
FROM Borrowing b
JOIN Members m ON b.Member_ID = m.Member_ID
JOIN Books bk ON b.ISBN = bk.ISBN
WHERE b.Return_Date IS NULL AND b.Due_Date < CURRENT_DATE
ORDER BY Days_Overdue DESC;

-- Calculate fine amount for overdue books
UPDATE Borrowing
SET Fine_Amount = DATEDIFF(CURRENT_DATE, Due_Date) * 1.50
WHERE Return_Date IS NULL AND Due_Date < CURRENT_DATE;

-- Find the most popular books (most borrowed)
SELECT b.Title, COUNT(br.Borrowing_ID) AS Borrow_Count
FROM Books b
JOIN Borrowing br ON b.ISBN = br.ISBN
GROUP BY b.ISBN, b.Title
ORDER BY Borrow_Count DESC
LIMIT 10;

-- View member borrowing history
CREATE VIEW Member_Borrowing_History AS
SELECT m.Member_ID, m.Name, b.Title, br.Borrow_Date, 
       br.Return_Date, 
       CASE 
           WHEN br.Return_Date IS NULL AND br.Due_Date < CURRENT_DATE 
           THEN 'Overdue'
           WHEN br.Return_Date IS NULL 
           THEN 'Borrowed'
           ELSE 'Returned'
       END AS Status
FROM Members m
JOIN Borrowing br ON m.Member_ID = br.Member_ID
JOIN Books b ON br.ISBN = b.ISBN
ORDER BY m.Member_ID, br.Borrow_Date DESC;

Evaluation Criteria

Your project will be evaluated based on the following criteria:

Database Design (25%)
- Proper entity identification and relationship modeling
- Normalization to at least 3NF
- Appropriate use of constraints and data types
Implementation (30%)
- Correctness of SQL statements
- Data integrity enforcement
- Sample data quality and comprehensiveness
Query Complexity and Correctness (25%)
- Variety of SQL features demonstrated
- Query efficiency and appropriateness
- Correct implementation of complex operations
Documentation and Presentation (15%)
- Clarity and completeness of documentation
- Organization and presentation of project materials
- Adherence to specified deliverables
Creativity and Real-world Applicability (5%)
- Innovative features or solutions
- Practical application of concepts to a real-world problem

Project Submission Guidelines

Format
- SQL script files (.sql)
- Documentation in PDF format
- ER diagrams in PDF or image format
Deliverables
- Project proposal document
- Database design document with ER diagram
- SQL script for database creation
- SQL script for sample data insertion
- SQL script containing all queries
- Comprehensive project report
- Presentation slides (if required)
Submission Method
- As specified by the instructor (typically via a learning management system)

Best Practices

Version Control
- Use a version control system like Git to track changes
- Commit regularly with meaningful commit messages
SQL Style
- Follow consistent naming conventions
- Use indentation and formatting for readability
- Add comments to explain complex queries
Testing
- Test your database with edge cases
- Verify constraints are working as expected
- Confirm queries return the expected results
Security Considerations
- Implement appropriate user privileges
- Protect sensitive data through views or access control
- Consider input validation (if building an interface)

Tips for Success

Start with a clear and well-defined scope
Spend adequate time on the design phase before implementation
Create a realistic project timeline with milestones
Regularly back up your work
Seek feedback early and often
Focus on database correctness before adding complex features
Test your database with realistic scenarios
Document your work throughout the project, not just at the end

Conclusion

The DBMS project provides an opportunity to apply theoretical knowledge to a practical scenario. By developing a comprehensive database solution for a real-world problem, you’ll strengthen your understanding of database concepts and gain valuable skills in database design, implementation, and management.