Hercules Motor Database Design

OVERVIEW

This project focused on building a foundational database system to manage various entities related to a motor vehicle business, including product lines, products, offices, employees, customers, orders, order details, and payments. The project aimed to demonstrate proficiency in SQL database design, data manipulation, advanced database objects (views, stored procedures, functions, triggers), and robust security implementation. 

OBJECTIVES

The primary objectives of this project were to:

• Design and implement a normalized relational database schema for a motor certification business.

• Populate the database with sample data across all defined tables.

• Demonstrate data manipulation capabilities, including insertion and deletion of columns.

• Create database views for simplified data access and reporting.

• Develop stored procedures for encapsulating complex queries and improving performance.

• Implement user-defined functions for reusable logic.

• Design and implement triggers to automate actions based on data modifications.

• Establish a comprehensive security model by creating logins, users, and roles with granular permissions (Admin, HR, Employee).

• Verify data integrity and functionality of all created database objects.

KEY COLUMNS

The project involved managing data across several interconnected tables, with key columns including:

ProductLines Table:

• productLine (Primary Key)

• textDescription

• htmlDescription (later dropped)

• image (later dropped)

Products Table:

• productCode (Primary Key)

• productName

• productLine (Foreign Key to ProductLines)

• productScale

• productVendor

• productDescription

• quantityInStock

• buyPrice

• MSRP

Offices Table:

• officeCode (Primary Key)

• city

• phone

• addressLine1, addressLine2

• state

• country

• postalCode

• territory

Employees Table:

• employeeNumber (Primary Key)

• lastName, firstName

• extension, email

• officeCode (Foreign Key to Offices)

• reportsTo (Foreign Key to Employees for hierarchical structure)

• jobTitle

Customers Table:

• customerNumber (Primary Key)

• customerName

• contactLastName, contactFirstName

• phone

• addressLine1, addressLine2

• city, state, postalCode, country

• salesRepEmployeeNumber (Foreign Key to Employees)

• creditLimit

Orders Table:

• orderNumber (Primary Key)

• orderDate, requiredDate, shippedDate

• status

• comments

• customerNumber (Foreign Key to Customers)

OrderDetails Table:

• orderNumber (Composite Primary Key, Foreign Key to Orders)

• productCode (Composite Primary Key, Foreign Key to Products)

• quantityOrdered

• priceEach

• orderLineNumber (Composite Primary Key)

Payments Table:

• customerNumber (Composite Primary Key, Foreign Key to Customers)

• checkNumber (Composite Primary Key)

• paymentDate

• amount

TOOLS

The primary tool utilized for this SQL database project was:

• SQL Server / SQL Server Management Studio (SSMS): Used for database creation, table design, data insertion, querying, creating database objects (views, stored procedures, functions, triggers), and managing user security.

APPROACH

The project followed a systematic database development and management approach:

Phase 1: Database and Table Design (DDL)

• Designed a relational database schema, creating tables (ProductLines, Products, Offices, Employees, Customers, Orders, OrderDetails, Payments) with appropriate data types, primary keys, and foreign key constraints to ensure data integrity.

• Ensured ProductLines was created first due to dependencies.

Phase 2: Data Insertion (DML)

• Populated all designed tables with sample data using INSERT statements.

• Verified data insertion using SELECT statements for each table.

Phase 3: Data Modification and Basic Queries

• Performed data modification tasks, such as dropping unnecessary columns (htmlDescription, image) from the ProductLines table using ALTER TABLE.

• Executed basic queries to find the highest and lowest payment amounts and the unique count of customer names.

Phase 4: Advanced Database Objects Development

• Views: Created a view cust_payment to join Customers and Payments tables, displaying customer name, amount, and contact details for paid transactions.

• Stored Procedures: Developed a stored procedure GetClassicCarsProductLines to retrieve all products belonging to the 'Classic Cars' product line.

• Functions: Created a user-defined function get_credit_limit_below_threshold() to return a table of customer numbers and credit limits for customers with a credit limit less than 96800.

• Triggers:

  • Implemented trg_after_employee_insert to automatically log new employee insertions (employeeNumber, lastName, firstName, officeCode) into an employee_audit table.

  • Created trgr_check_payment_amount to display the customer number for newly inserted payments where the amount exceeds 10,000.

Phase 5: Database Security Implementation

• Designed and implemented a robust security model with three distinct roles:

  • AdminRole: Full access and control over the entire database.

  • HRRole: Access (SELECT, INSERT, UPDATE, DELETE) limited to Employees and Offices tables.

  • EmployeeRole: Read-only (SELECT) access to all tables in the database.

• Created corresponding logins and database users for each role (AdminLogin, HRLogin, EmployeeLogin and AdminUser, HRUser, EmployeeUser).

• Assigned users to their respective roles and granted specific permissions.

Phase 6: Testing and Verification

• Thoroughly tested all created database objects (views, stored procedures, functions, triggers) to ensure they functioned as intended.

• Verified data integrity after modifications and insertions.

KEY INSIGHTS

This project provided practical insights into database management and security:

• Relational Database Design: Demonstrated the importance of normalized table design and proper foreign key relationships for data integrity and efficient querying.

• Data Manipulation Efficiency: Showcased how DDL and DML commands are essential for managing database schema and data effectively.

• Power of Database Objects: Highlighted how views simplify complex queries for reporting, stored procedures enhance performance and reusability, and functions allow for modular, reusable logic.

• Automated Data Integrity with Triggers: Illustrated how triggers can automate auditing (e.g., employee_audit) and enforce business rules (e.g., checking payment amounts), improving data consistency and event logging.

• Granular Security Control: Emphasized the critical need for Row-Level Security (RLS) and role-based access control to protect sensitive data and ensure users only access authorized information. This is crucial for compliance and data governance.

• Database Administration Fundamentals: Covered essential administrative tasks like user, login, and role management, which are fundamental for maintaining a secure and operational database environment.

IMPACTS

This project delivered significant impacts related to database management:

• Structured Data Management: Established a well-organized and relational database for efficient storage and retrieval of business-critical information.

• Improved Query Performance & Reusability: The use of stored procedures and functions optimized query execution and promoted code reusability across the system.

• Enhanced Data Integrity & Auditing: Triggers ensured automatic logging of important data changes and helped enforce business rules, leading to more reliable data.

• Robust Security Posture: The implementation of user roles and granular permissions significantly strengthened the database's security, protecting sensitive information from unauthorized access.

• Streamlined Reporting: Views simplified data access for reporting purposes, making it easier for business users to extract necessary information without complex joins.

• Foundation for Application Development: Provided a solid and secure database backend that could support various business applications.

DELIVERABLES

The key deliverables for this project included:

• SQL Script (MotorsCertification.sql): A comprehensive SQL script containing:

  • DDL statements for creating all tables with primary and foreign key constraints.

  • DML statements for inserting sample data into each table.

  • SQL queries for basic data verification and analysis (e.g., MAX, MIN, DISTINCT).

  • Definitions for the cust_payment view, GetClassicCarsProductLines stored procedure, and get_credit_limit_below_threshold function.

  • Definitions for the employee_audit table and the trg_after_employee_insert and trgr_check_payment_amount triggers.

  • SQL commands for creating logins, database users, and roles (AdminRole, HRRole, EmployeeRole) and assigning specific permissions.

• Database Schema: The implemented relational database schema.

• Functional Database Objects: Working views, stored procedures, functions, and triggers.

• Security Model: A defined and implemented role-based access control system.