Advanced Database Systems – Project
Requirement Document
Overview
As a core component of the Advanced Database Systems course, students are required to design and develop a
complete information system using Microsoft SQL Server as the backend database. The system must be integrated
with a custom-built application developed using any programming language and platform (e.g., C#, Java, Python,
JavaScript, etc.) — be it desktop, mobile, or web-based.
This project is intended to demonstrate practical application of advanced database concepts covered throughout
the semester.
Project Scope and Requirements
1. Database Design
- At least 15 relational tables, normalized and relationally connected using primary and foreign key constraints.
- Thoughtful use of data types, constraints, default values, and relationships.
- A clearly documented Entity-Relationship Diagram (ERD) must be submitted.
2. Advanced SQL Server Features
Each of the following database objects must be used at least 10 times in your system:
- Views: Used for abstraction, security, reporting, or simplification.
- Stored Procedures: For encapsulating complex operations or business logic.
- Triggers: Used to enforce rules automatically (e.g., logging, cascading logic).
- User-Defined Functions (UDFs): Scalar or table-valued; used for reusable logic.
- Indexes: A mix of clustered, non-clustered, composite, and unique indexes.
- Full-Text Search: Must be implemented on applicable text-based fields and demonstrated in queries.
3. Database Optimization
- Demonstrate query performance analysis using execution plans.
- Apply proper indexing strategies and normalization.
- Implement concurrency control with transaction isolation levels or explicit locking.
- At least two optimization techniques must be explained in your documentation.
4. Security & Data Integrity
- Implement Role-Based Access Control (RBAC) with proper user-role-permission logic.
- Apply Row-Level Security (RLS) for restricting access to records based on login/user role.
- Use encryption for at least one sensitive column (e.g., salary, personal info).
5. Automation
- Use SQL Server Agent to schedule and run at least one recurring task, such as:
• Database backup
• Data archiving
• Auto-generated email alerts
• Periodic data import/export
6. Application Integration
- Build a functional application (web, desktop, or mobile) that integrates with your database.
- All data operations must be routed through stored procedures or APIs — no direct table access.
- Application must include:
• User authentication (login)
• Role-based views or restricted functionality
• Clean and functional UI
• Data visualizations or reports are encouraged
Evaluation Criteria
Criterion Weight Description
System Functionality & 40% Covers business
Completeness requirements, completeness
of database objects,
usability.
Database Efficiency & 20% Use of indexes, optimized
Optimization queries, performance tools,
and normalization.
Security & Data Integrity 15% Effective implementation of
RBAC, RLS, and encryption.
Application Implementation 15% Code organization,
& Code Quality integration logic, security,
UI quality.
Presentation & 10% Clarity, completeness, and
Documentation technical depth of final
documentation.
Final Deliverables
- SQL Scripts:
• Schema creation scripts (tables, keys, constraints)
• Scripts for all 10+ views, SPs, triggers, UDFs
• Indexing and full-text search setup
• RLS and encryption implementation
• SQL Server Agent job configuration
- Application Source Code:
• All frontend/backend files
• Clear instructions for setup and execution
- Documentation (well-structured PDF or Word file):
• ER Diagram and schema explanation
• Business rules implemented
• List and role of each SQL object (views, SPs, triggers, etc.)
• Optimization techniques applied
• Security & automation details
• User manual or sample usage
- Demo Video (optional but recommended)
