Skip to content

README.md

Latest commit

 

History

History
282 lines (207 loc) · 7.55 KB

README.md

File metadata and controls

282 lines (207 loc) · 7.55 KB

🗄️ SQL Database Scripts Repository

A comprehensive collection of SQL database scripts covering Oracle, MySQL, and MS SQL Server implementations. This repository contains complete database schemas with tables, relationships, constraints, and sample data for various real-world applications.

📋 Table of Contents

🎯 Overview

This repository contains SQL scripts developed during database management coursework and projects. Each script represents a complete, production-ready database schema for different application domains including banking, e-commerce, library management, and student information systems.

Total Scripts: 7 complete database implementations
Supported Platforms: Oracle Database, MySQL, MS SQL Server
Purpose: Educational, Reference, and Practical Implementation

💾 Database Systems

Oracle Database

  • BANKDB - Banking System Database
  • BANK_USERS - User Management & Security for Bank System
  • STUDENTDB - Student-Course Management System

MySQL

  • ESTOREDB - E-Commerce Shop Platform

MS SQL Server

  • LibraryDB - Library Management System
  • MyShopDB - Comprehensive E-Commerce Platform

📁 Repository Structure

SQL/
├── oracle/
│   ├── BANKDB.sql
│   ├── BANK_USERS.sql
│   ├── STUDENTDB.sql
│   ├── README.md
│   └── bank_queries.sql
│
├── mysql/
│   ├── ESTOREDB.sql
│   ├── README.md
│   └── estore_queries.sql
│
├── mssql/
│   ├── Library.sql
│   ├── myshopdb.sql
│   ├── README.md
│   └── shop_queries.sql
│
├── docs/
│   ├── LABS.md
│   ├── ER_DIAGRAMS.md
│   └── SETUP_GUIDES.md
│
├── README.md
├── .gitignore
└── LICENSE

🗃️ Databases Included

1️⃣ Banking System (Oracle)

Files: BANKDB.sql, BANK_USERS.sql

A complete banking database with user management, role-based access control, and comprehensive security implementation.

Features:

  • Customer management
  • Account management (Current, Saving, Deposit)
  • Branch management
  • Employee hierarchy with supervisor relationships
  • User roles and privileges (BANKDBA, HR, Manager, Accountant, Clerks)
  • Public synonyms for easy access
  • Referential integrity constraints

Tables: customer, account, owner, branch, employee
Security: 7 user roles with granular permissions

2️⃣ Student Management System (Oracle)

File: STUDENTDB.sql

Academic management system for tracking students, courses, and results.

Features:

  • Student information management
  • Course catalog with credit hours
  • Grade Point Average (GPA) tracking
  • Composite primary keys for result tracking
  • Sample queries with joins

Tables: STUDENT, COURSE, RESULT

3️⃣ E-Shop Platform (MySQL)

File: ESTOREDB.sql

Modern e-commerce platform with advanced features including AI chatbot integration and analytics.

Features:

  • Customer registration and authentication
  • Product catalog with images and sizes
  • Shopping cart and wishlist
  • Order management with discount system
  • Payment processing
  • Shipping and billing
  • AI-powered chat support with analytics
  • Newsletter subscription
  • Contact messaging system

Tables: 19 tables including customer, product, order, payment, chat_sessions, inventory, etc.

4️⃣ Library Management System (MS SQL Server)

File: Library.sql

Simple and elegant library management system.

Features:

  • Book catalog management
  • Member registration
  • Loan tracking
  • Automated timestamps

Tables: Books, Members, Loans

5️⃣ MyShop E-Commerce (MS SQL Server)

File: myshopdb.sql

Enterprise-level e-commerce platform with comprehensive features.

Features:

  • Admin and customer authentication
  • Product management with reviews and images
  • Multi-address support for customers
  • Order workflow with status tracking
  • Shopping cart and wishlist
  • Contact message system with priority and status
  • Extensive indexing for performance

Tables: 14 tables including Customers, Products, Orders, OrderDetails, ProductReviews, etc.

🚀 Getting Started

Prerequisites

Depending on which database you want to use:

  • Oracle Database (11g or higher) with SQL*Plus
  • MySQL Server (5.7 or higher) or MariaDB
  • MS SQL Server (2016 or higher) or SQL Server Express

Installation

  1. Clone the repository:
git clone https://github.com/yourusername/sql-database-scripts.git
cd sql-database-scripts
  1. Choose your database system and navigate to the appropriate folder:
cd oracle/   # For Oracle scripts
cd mysql/    # For MySQL scripts
cd mssql/    # For MS SQL Server scripts
  1. Execute the SQL scripts using your database client

📖 Usage

Oracle Database

-- Connect as SYSDBA
sqlplus sys/password as sysdba

-- Run the bank system scripts
@oracle/BANK_USERS.sql
@oracle/BANKDB.sql

-- Or run student database
@oracle/STUDENTDB.sql

MySQL

# Login to MySQL
mysql -u root -p

# Execute the script
source mysql/ESTOREDB.sql

# Or from command line
mysql -u root -p < mysql/ESTOREDB.sql

MS SQL Server

-- Using SQL Server Management Studio (SSMS)
-- Open the .sql file and execute

-- Or using sqlcmd
sqlcmd -S localhost -U sa -P password -i mssql/Library.sql
sqlcmd -S localhost -U sa -P password -i mssql/myshopdb.sql

📚 Documentation

  • LABS.md - Detailed documentation of all lab exercises and coursework
  • ER_DIAGRAMS.md - Entity-Relationship diagrams for each database
  • SETUP_GUIDES.md - Platform-specific setup instructions

🎓 Learning Resources

Each database folder contains:

  • Schema creation scripts
  • Sample data insertions
  • Example queries (SELECT, JOIN, subqueries)
  • Stored procedures and triggers (where applicable)
  • Views and indexes

🔍 Key Concepts Demonstrated

  • Data Definition Language (DDL): CREATE, ALTER, DROP statements
  • Data Manipulation Language (DML): INSERT, UPDATE, DELETE, SELECT
  • Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL
  • Relationships: One-to-One, One-to-Many, Many-to-Many
  • Referential Integrity: CASCADE operations
  • Security: User management, roles, and privileges
  • Advanced Features: Triggers, stored procedures, views, indexes
  • Transactions: COMMIT, ROLLBACK operations

🤝 Contributing

Contributions are welcome! If you'd like to add more database scripts or improve existing ones:

  1. Fork the repository
  2. Create a new branch (git checkout -b feature/new-database)
  3. Commit your changes (git commit -m 'Add new database schema')
  4. Push to the branch (git push origin feature/new-database)
  5. Open a Pull Request

📝 License

This project is licensed under the MIT License - see the LICENSE file for details.

👤 Author

Your Name

🙏 Acknowledgments

  • Database course instructors and lab materials
  • SQL community for best practices and patterns
  • Contributors to this repository

⭐ If you find this repository helpful, please consider giving it a star!

Last Updated: November 23, 2025