Skip to content

LABS.md

Latest commit

 

History

History
555 lines (442 loc) · 13.5 KB

LABS.md

File metadata and controls

555 lines (442 loc) · 13.5 KB

📚 Database Lab Documentation

This document outlines all the laboratory exercises and practical work completed during the Database Management Systems course.

📋 Table of Contents

🎓 Course Overview

Course: Database Management Systems
Duration: Full Semester
Database Platforms: Oracle Database, MySQL, Microsoft SQL Server
Key Topics: DDL, DML, DCL, Constraints, Joins, User Management, Security

🔬 Lab Exercises

Lab 1: Introduction to SQL and Basic DDL

Objectives:

  • Understand database creation
  • Learn CREATE, DROP, and ALTER statements
  • Practice data types and constraints

Database: STUDENTDB (Oracle)

Tasks Completed:

  1. ✅ Created STUDENT table with appropriate data types
  2. ✅ Created COURSE table with credit hour tracking
  3. ✅ Created RESULT table with composite primary key
  4. ✅ Implemented PRIMARY KEY constraints
  5. ✅ Implemented FOREIGN KEY constraints with referential integrity
  6. ✅ Added CHECK constraints for data validation

Key Concepts:

  • Data Definition Language (DDL)
  • Primary Keys and Foreign Keys
  • Composite Keys
  • Cascade Constraints

SQL Features Demonstrated:

-- Table creation with constraints
CREATE TABLE STUDENT (
    STUDENT_ID VARCHAR2(10) NOT NULL,
    STUDENT_NAME VARCHAR2(50) NOT NULL,
    PRIMARY KEY (STUDENT_ID)
);

-- Composite primary key
ALTER TABLE RESULT 
ADD CONSTRAINT RESULT_STUDENT_ID_COURSE_ID_CPK 
PRIMARY KEY(STUDENT_ID, COURSE_ID);

Lab 2: Data Manipulation Language (DML)

Objectives:

  • Master INSERT, UPDATE, DELETE operations
  • Understand transaction control
  • Practice data manipulation

Database: STUDENTDB (Oracle)

Tasks Completed:

  1. ✅ Inserted sample student records
  2. ✅ Inserted course catalog data
  3. ✅ Created student enrollment records
  4. ✅ Performed UPDATE operations
  5. ✅ Queried data using SELECT statements

Key Concepts:

  • INSERT statements
  • UPDATE with WHERE clause
  • SELECT queries
  • Data integrity during operations

Lab 3: Complex Queries and Joins

Objectives:

  • Write multi-table queries
  • Master different types of joins
  • Use aliases for readability

Database: STUDENTDB, BANKDB (Oracle)

Tasks Completed:

  1. ✅ Performed INNER JOINs across multiple tables
  2. ✅ Created queries combining STUDENT, COURSE, and RESULT
  3. ✅ Used table aliases for cleaner queries
  4. ✅ Retrieved student grades with course information

Query Example:

SELECT S.STUDENT_ID, R.COURSE_ID, C.COURSE_NAME, R.GPA 
FROM STUDENT S, COURSE C, RESULT R
WHERE S.STUDENT_ID = R.STUDENT_ID
AND R.COURSE_ID = C.COURSE_ID;

Key Concepts:

  • Table joins (implicit and explicit)
  • WHERE clause for join conditions
  • Column aliases
  • Multi-table relationships

Lab 4: Banking Database Design

Objectives:

  • Design a real-world banking system
  • Implement complex relationships
  • Handle self-referential foreign keys

Database: BANKDB (Oracle)

Tasks Completed:

  1. ✅ Designed 5-table banking schema
  2. ✅ Implemented customer and account management
  3. ✅ Created many-to-many relationship (owner table)
  4. ✅ Designed employee hierarchy with supervisor relationships
  5. ✅ Added CHECK constraint for account types
  6. ✅ Implemented CASCADE CONSTRAINTS

Tables Created:

  • customer - Customer information
  • account - Bank accounts (current, saving, deposit)
  • owner - Customer-Account relationship
  • branch - Branch details
  • employee - Employee hierarchy

Advanced Features:

-- Self-referential foreign key
ALTER TABLE employee
ADD CONSTRAINT employee_supervisor_FK 
FOREIGN KEY(supervisor) REFERENCES employee(staffNO);

-- CHECK constraint for business rules
ALTER TABLE account
ADD CONSTRAINT account_type_chk 
CHECK (type IN ('current','saving','deposit'));

Key Concepts:

  • Self-referential relationships
  • Many-to-many relationships
  • Business rule constraints
  • Hierarchical data modeling

Lab 5: User Management and Security

Objectives:

  • Create and manage database users
  • Implement role-based access control
  • Practice privilege management

Database: BANK_USERS (Oracle)

Tasks Completed:

  1. ✅ Connected as SYSDBA
  2. ✅ Created power user (BANKDBA) with DBA privileges
  3. ✅ Created departmental users (BANK_HR, MANAGER, ACCOUNTANT)
  4. ✅ Created operational users (6 clerks)
  5. ✅ Granted system privileges (CREATE SESSION, CREATE TABLE, etc.)
  6. ✅ Granted object privileges on tables
  7. ✅ Used WITH ADMIN OPTION and WITH GRANT OPTION
  8. ✅ Created PUBLIC SYNONYMS

User Hierarchy Implemented:

SYS (SYSDBA)
└── BANKDBA (DBA)
    ├── BANK_HR
    │   ├── CLERK_A (Full access to employee)
    │   └── CLERK_B (Read-only employee)
    ├── MANAGER
    │   ├── CLERK_C (Customer operations)
    │   └── CLERK_D (Branch operations)
    └── ACCOUNTANT
        ├── CLERK_E (Account operations)
        └── CLERK_F (Customer financial ops)

Security Concepts:

-- System privileges
GRANT CONNECT, CREATE SESSION, CREATE TABLE TO BANK_HR 
WITH ADMIN OPTION;

-- Object privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO BANK_HR 
WITH GRANT OPTION;

-- Public synonyms for transparency
CREATE PUBLIC SYNONYM customer FOR BANKDBA.customer;

Key Concepts:

  • Data Control Language (DCL)
  • User creation and authentication
  • System vs. Object privileges
  • WITH ADMIN OPTION vs. WITH GRANT OPTION
  • Role delegation
  • Public synonyms

Lab 6: MySQL E-Commerce Database

Objectives:

  • Work with MySQL-specific features
  • Design comprehensive e-commerce system
  • Implement BLOB storage

Database: ESTOREDB (MySQL)

Tasks Completed:

  1. ✅ Created 19-table e-commerce platform
  2. ✅ Implemented AUTO_INCREMENT primary keys
  3. ✅ Designed product catalog with images
  4. ✅ Created order management system with discounts
  5. ✅ Implemented AI chatbot integration
  6. ✅ Added analytics tables for business intelligence
  7. ✅ Created wishlist and shopping cart functionality
  8. ✅ Designed multi-image product support

Advanced Features:

  • BLOB storage for images
  • InnoDB engine for ACID compliance
  • ENUM types for status fields
  • Composite indexes for performance
  • CASCADE operations
  • DEFAULT values with functions (CURRENT_TIMESTAMP)

MySQL-Specific Syntax:

-- AUTO_INCREMENT
CREATE TABLE product (
    product_id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (product_id)
) ENGINE=InnoDB;

-- ENUM type
session_type ENUM('customer','admin') DEFAULT 'customer'

-- Composite index
KEY idx_session_created (session_id, created_at)

Key Concepts:

  • MySQL storage engines
  • AUTO_INCREMENT vs. IDENTITY
  • BLOB vs. filesystem storage
  • UTF8MB4 character set
  • Indexing strategies

Lab 7: MS SQL Server - Library System

Objectives:

  • Learn SQL Server syntax and features
  • Practice with identity columns
  • Understand SQL Server data types

Database: LibraryDB (MS SQL Server)

Tasks Completed:

  1. ✅ Created database with SQL Server-specific settings
  2. ✅ Implemented IDENTITY columns
  3. ✅ Used NVARCHAR for Unicode support
  4. ✅ Created foreign keys with constraint names
  5. ✅ Set default values using GETDATE()
  6. ✅ Configured clustered indexes

SQL Server Features:

-- IDENTITY column
CREATE TABLE Books (
    Id INT IDENTITY(1,1) NOT NULL,
    PRIMARY KEY CLUSTERED (Id)
);

-- Default constraint
[LoanDate] DATETIME DEFAULT (GETDATE())

-- Unicode data types
[Title] NVARCHAR(200) NOT NULL

Key Concepts:

  • IDENTITY vs. AUTO_INCREMENT
  • NVARCHAR for international characters
  • Clustered vs. Non-clustered indexes
  • SQL Server system functions
  • Database compatibility levels

Lab 8: Advanced E-Commerce Platform (MS SQL Server)

Objectives:

  • Build enterprise-grade e-commerce system
  • Implement computed columns
  • Create comprehensive indexing strategy

Database: MyShopDB (MS SQL Server)

Tasks Completed:

  1. ✅ Created 14-table e-commerce system
  2. ✅ Implemented admin and customer authentication
  3. ✅ Designed product catalog with reviews and ratings
  4. ✅ Created order workflow with status tracking
  5. ✅ Implemented shopping cart and wishlist
  6. ✅ Added contact messaging system
  7. ✅ Created multiple address support
  8. ✅ Implemented computed columns
  9. ✅ Added extensive indexing (20+ indexes)

Advanced Features:

-- Computed column
[TotalPrice] AS ([Quantity] * [UnitPrice]) PERSISTED

-- CHECK constraint with range
CHECK (([Rating] >= 1 AND [Rating] <= 5))

-- Composite unique constraint
CONSTRAINT UK_Cart_Customer_Product UNIQUE (CustomerId, ProductId)

-- Conditional default
[Status] NVARCHAR(50) DEFAULT 'New'

Indexing Strategy:

  • Clustered indexes on PKs
  • Non-clustered on FKs
  • Indexes on frequently queried columns (Email, Username)
  • Composite indexes for common queries
  • Unique indexes for business rules

Key Concepts:

  • Computed columns (PERSISTED)
  • DATETIME2 for precision
  • Extensive indexing for performance
  • Audit fields (CreatedAt, ModifiedAt)
  • Soft deletes with IsActive flags
  • Priority and status enumerations

💻 Database Systems Covered

Oracle Database

Labs: 1, 2, 3, 4, 5
Scripts: STUDENTDB.sql, BANKDB.sql, BANK_USERS.sql

Key Features Learned:

  • SQL*Plus command line
  • VARCHAR2 data type
  • CASCADE CONSTRAINTS syntax
  • User management with GRANT/REVOKE
  • Public synonyms
  • / for statement termination

MySQL

Labs: 6
Scripts: ESTOREDB.sql

Key Features Learned:

  • AUTO_INCREMENT
  • InnoDB storage engine
  • ENUM and SET types
  • BLOB storage
  • Backtick identifiers
  • IF NOT EXISTS syntax
  • UTF8MB4 character set

Microsoft SQL Server

Labs: 7, 8
Scripts: Library.sql, myshopdb.sql

Key Features Learned:

  • IDENTITY columns
  • NVARCHAR for Unicode
  • DATETIME2 precision
  • Computed columns
  • Query Store
  • Clustered indexes
  • GO batch separator
  • Square bracket identifiers

🎯 Learning Outcomes

By completing these labs, students have learned:

1. Database Design

  • ✅ Entity-Relationship modeling
  • ✅ Normalization (1NF, 2NF, 3NF)
  • ✅ Primary and foreign key selection
  • ✅ Relationship types (1:1, 1:N, M:N)
  • ✅ Self-referential relationships
  • ✅ Composite keys

2. SQL Proficiency

  • ✅ DDL (CREATE, ALTER, DROP)
  • ✅ DML (SELECT, INSERT, UPDATE, DELETE)
  • ✅ DCL (GRANT, REVOKE)
  • ✅ Constraints (PK, FK, CHECK, UNIQUE, NOT NULL)
  • ✅ Joins (INNER, LEFT, RIGHT, FULL)
  • ✅ Aggregate functions
  • ✅ Subqueries
  • ✅ Indexes

3. Database Security

  • ✅ User creation and management
  • ✅ Privilege assignment
  • ✅ Role-based access control
  • ✅ WITH GRANT OPTION
  • ✅ Public synonyms
  • ✅ Schema separation

4. Platform-Specific Skills

  • ✅ Oracle SQL*Plus
  • ✅ MySQL command line and Workbench
  • ✅ SQL Server Management Studio (SSMS)
  • ✅ Platform syntax differences
  • ✅ Data type variations
  • ✅ Auto-increment mechanisms

5. Real-World Applications

  • ✅ Banking systems
  • ✅ E-commerce platforms
  • ✅ Library management
  • ✅ Student information systems
  • ✅ Inventory tracking
  • ✅ Order processing
  • ✅ User authentication

6. Best Practices

  • ✅ Naming conventions
  • ✅ Referential integrity
  • ✅ Data validation
  • ✅ Performance optimization
  • ✅ Index strategy
  • ✅ Backup and recovery concepts
  • ✅ Documentation

📊 Lab Statistics

Metric Count
Total Labs 8
Databases Created 6
Total Tables 50+
Database Platforms 3
Users Created 9
Constraints Implemented 100+
Sample Queries Written 50+
Lines of SQL Code 2000+

🏆 Advanced Topics Covered

Transaction Management

  • Implicit vs. explicit transactions
  • COMMIT and ROLLBACK operations
  • Transaction isolation levels

Performance Optimization

  • Index selection and creation
  • Query execution plans
  • Avoiding full table scans
  • Statistics management

Data Integrity

  • Entity integrity (Primary Keys)
  • Referential integrity (Foreign Keys)
  • Domain integrity (CHECK, data types)
  • User-defined integrity (triggers, procedures)

Modern Features

  • BLOB storage for images
  • JSON support (modern versions)
  • Full-text search
  • Computed columns
  • Analytics and BI integration

📝 Additional Resources

Recommended Reading

  • Oracle Database Concepts Guide
  • MySQL Reference Manual
  • SQL Server Books Online
  • SQL Fundamentals textbook

Practice Exercises

  • Additional queries in *_queries.sql files
  • ERD practice in /docs/ER_DIAGRAMS.md
  • Setup guides in /docs/SETUP_GUIDES.md

Next Steps

  • Stored procedures and functions
  • Triggers for automated actions
  • Views for data abstraction
  • Database administration
  • Performance tuning
  • Replication and high availability

✅ Skills Checklist

  • Create databases and tables
  • Define constraints and relationships
  • Insert and manipulate data
  • Write complex queries with joins
  • Implement user security
  • Design normalized schemas
  • Work with three major DBMS platforms
  • Optimize queries with indexes
  • Handle hierarchical data
  • Implement business logic in database
  • Work with BLOBs and large objects
  • Create and manage transactions
  • Design real-world applications

Course Completed: November 2025
Total Hours: 120+ hours of practical work
Proficiency Level: Intermediate to Advanced

This documentation serves as a comprehensive record of all practical database work completed during the course.