Hospital Management & Login Audit (PL/SQL)

Overview

This repository contains two modules:

• Hospital Management: A PL/SQL package for bulk patient operations, listing, admissions, and counts.
• Login Audit: DDL and a trigger to track login attempts and raise alerts for suspicious failed logins.

What the question was about

• Build PL/SQL structures to manage hospital data with bulk processing.
• Implement a login-audit workflow that stores attempts and raises security alerts after multiple failures.

How we solved it

• Defined hospital tables (patients, doctors) and implemented hospital_mgmt_pkg:
  • bulk_load_patients – efficient bulk insert via FORALL with a collection type.
  • show_all_patients – returns SYS_REFCURSOR for listing.
  • count_admitted – returns current admitted count.
  • admit_patient – sets admission status.
• Defined login audit tables (login_audit, security_alerts) and a trigger:
  • Counts a user’s FAILED attempts per day and inserts a security alert once they exceed 2.
  • Added indexes for performance on common query patterns.
• Wrote test scripts and captured results.