A relational database designed in Oracle SQL for managing summer camp operations — campers, counselors, cabins, activities, and equipment. The schema implements Enhanced Entity-Relationship (EER) modeling with specialization hierarchies and union types, demonstrating advanced database design concepts including inheritance, multivalued attributes, and complex relationship constraints.
The database models a summer camp with the following entities and relationships:
| Entity | Type | Description |
|---|---|---|
| Camper | Strong entity | Campers who stay in cabins and participate in activities |
| Counselor | Strong entity | Staff who manage cabins and may lead activities |
| Cabin | Strong entity | Lodging units with capacity, gender, and age group |
| Activity | Superclass | Activities with age-group restrictions (Boating, Biking, Volleyball, Horseback Riding, Tug-of-War) |
| Equipment | Superclass | Gear used in activities (Boats, Bikes, Ropes, Horses, Volleyballs) |
| Activity_Leader | Union type | Subset of both Campers and Counselors who lead activities |
- Specialization hierarchy (Equipment): Boat, Rope, Bike, Volleyball, and Horse are subclasses of Equipment
- Specialization hierarchy (Activity): Boating, TugoWar, Biking, Volleyball_Game, and Horseback_Riding are subclasses of Activity
- Union type (Activity_Leader): An activity leader can be either a Camper or a Counselor — modeled as a separate table referenced by both
- Multivalued attributes: Equipment_Location and Activity_Type store multiple values per entity
- Relationship tables: Participate (Camper-Activity M:N), Lead (Activity_Leader-Activity M:N), Uses (Activity-Equipment 1:N)
| Query | Description | Techniques |
|---|---|---|
| ParticipatingCampers | Lists campers with their activities and activity leader info | Multi-table JOIN, subquery, LEFT OUTER JOIN, CASE |
| TweenActivities | Shows activity and equipment details for Tween age group | 11-table JOIN, LEFT OUTER JOIN, CASE expression, string concatenation |
| count_camper_gender_by_cabin | Counts campers by gender within each cabin | GROUP BY, aggregate COUNT, multi-table JOIN |
| CounselorsWith10YrsExpAndActivityLeader | Finds experienced counselors who are activity leaders | INNER JOIN, WHERE filter |
| RacingCampers | Finds campers eligible for horseback racing | 4-table JOIN, WHERE filter on age group |
| qhorseback | Lists campers and their horseback riding types | 4-table JOIN, ORDER BY |
| TeenCampers | Shows teen campers with cabin and counselor details | 3-table INNER JOIN |
├── README.md
├── sql/
│ ├── 01_create_tables.sql # DDL — CREATE TABLE with constraints
│ ├── 02_insert_values.sql # DML — INSERT sample data
│ ├── 03_desc_tables.sql # Display table descriptions
│ ├── 04_display_data.sql # Display all table data
│ ├── 05_drop_all_tables.sql # Drop all tables (cleanup)
│ ├── master_script.sql # Runs all scripts in sequence
│ ├── group_startup.sql # SQL*Plus session setup
│ ├── div.sql # Divider formatting
│ └── cl_scr.sql # Clear screen
├── queries/
│ ├── ParticipatingCampers.sql
│ ├── TweenActivities.sql
│ ├── TeenCampers.sql
│ ├── count_camper_gender_by_cabin.sql
│ ├── CounselorsWith10YrsExpAndActivityLeader.sql
│ ├── RacingCampers.sql
│ └── qhorseback.sql
└── diagrams/
└── eer_diagram.png # EER diagram
Requires Oracle Database (XE or LiveSQL). Connect via SQL*Plus or SQL Developer, then run the master script:
@master_scriptThis executes all scripts in order: create tables, insert data, display data, run all queries.
Alternatively, run scripts individually:
@sql/01_create_tables
@sql/02_insert_values
@queries/ParticipatingCampers
- Database: Oracle SQL (SQL*Plus)
- Modeling: Enhanced Entity-Relationship (EER) diagrams
- Concepts: Specialization hierarchies, union types, multivalued attributes, referential integrity, complex JOINs
Code provided for educational and portfolio purposes.