Competitions Oracle

An Oracle Database (XE 11g) schema designed for managing competitions, participants, sponsors, and prizes. The schema comes pre-populated with sample data, making it ready to use out-of-the-box for development, testing, and learning purposes.

Problem/Purpose

Setting up a relational database schema from scratch for competition management systems can be time-consuming. This project provides:

• A complete, normalized database schema for competition management
• Pre-populated sample data (countries, competitions, participants, sponsors, etc.)
• Automated installation/uninstallation scripts
• Docker support for easy deployment without local Oracle installation

Project Structure

competitions-oracle/
├── as_admin/                          # Admin-level SQL scripts
│   ├── install_user_tablespace.sql    # Creates user, schema, and tablespace
│   └── uninstall_user_tablespace.sql  # Drops user and tablespace
├── as_end_user/                       # End-user SQL scripts
│   ├── create_tables/                 # Table creation scripts
│   │   ├── create_avatar.sql
│   │   ├── create_competition.sql
│   │   ├── create_competition_participant.sql
│   │   ├── create_competition_type.sql
│   │   ├── create_country.sql
│   │   ├── create_equipment.sql
│   │   ├── create_equipment_type.sql
│   │   ├── create_organization.sql
│   │   ├── create_organization_team.sql
│   │   ├── create_participant.sql
│   │   ├── create_prize.sql
│   │   ├── create_prize_type.sql
│   │   ├── create_sponsor.sql
│   │   ├── create_sponsor_type.sql
│   │   └── add_constraints.sql        # Foreign key constraints
│   └── fill_tables/                   # Sample data insertion scripts
│       ├── 01comp_type.sql
│       ├── 02country.sql
│       ├── 03equip_type.sql
│       ├── 04prize_type.sql
│       ├── 05sponsor_type.sql
│       ├── 06org_team.sql
│       ├── 07org.sql
│       ├── 08equip.sql
│       ├── 09comp.sql
│       ├── 10sponsor.sql
│       ├── 11part.sql
│       ├── 12prize.sql
│       └── 13avatar.sql
├── install_competitions_schema.sh     # Main installation script
├── COMPETITIONS_SCHEMA.md             # Detailed schema documentation
└── README.md

Requirements/Dependencies

• Oracle Database XE 11g (or compatible version)
• Bash shell (for running installation scripts)
• SQL*Plus (Oracle command-line interface)
• Docker (optional, for containerized deployment)

Docker Image (Recommended)

The project was developed and tested using the sath89/oracle-xe-11g Docker image.

Installation

Via Docker (Recommended)

1. Clone the repository:

   git clone https://github.com/maximillian2/competitions-oracle.git
   cd competitions-oracle

2. Pull the Oracle XE Docker image:

   docker pull sath89/oracle-xe-11g

3. Start the container (replace /my/oracle/data/ with your preferred data directory):

   docker run -d -p 8080:8080 -p 1521:1521 \
     -v /my/oracle/data:/u01/app/oracle \
     -v $(pwd)/:/mnt/ \
     sath89/oracle-xe-11g

4. Get the container name:

   docker ps

5. Run the installation script inside the container:

   docker exec -it <container_name> /bin/bash -c 'cd /mnt; ./install_competitions_schema.sh'

6. Follow the prompts to enter username, password, and tablespace name.

Local Oracle Instance

1. Clone the repository:

   git clone https://github.com/maximillian2/competitions-oracle.git
   cd competitions-oracle

2. Run the installation script:

   ./install_competitions_schema.sh

3. Follow the prompts to configure the new database user.

Uninstallation

Important: Ensure the user to be deleted is disconnected from the database before uninstalling.

./install_competitions_schema.sh --uninstall

Or using the short flag:

./install_competitions_schema.sh -un

Usage Examples

Connecting to the Database

After installation, connect using SQL*Plus:

sqlplus username/password

Sample Queries

List all competitions:

SELECT c.Name, ct.Name AS Type, co.Name AS Country, c.City, c.Year
FROM COMP c
JOIN COMP_TYPE ct ON c.CompTypeFK = ct.CompTypePK
JOIN COUNTRY co ON c.CountryFK = co.CountryPK
ORDER BY c.Year DESC;

Output:

NAME                                     TYPE          COUNTRY        CITY              YEAR
---------------------------------------- ------------- -------------- ----------------- ----
Competition Coming Es Capture            singing       Seychelles     Lakewood          2017
Of Funeral Settings Sa                   game          Luxembourg     Trotwood          2017
Jurisdiction Specific Crisis Too         intellectual  Bulgaria       Mountain View     2013
...

Find participants from a specific country:

SELECT p.Name, p.Surname, p.Email, c.Name AS Country
FROM PART p
JOIN COUNTRY c ON p.CountryFK = c.CountryPK
WHERE c.Name = 'United States';

Get prize winners:

SELECT pr.Name AS Prize, pt.Name AS Type, p.Name || ' ' || p.Surname AS Winner, c.Name AS Competition
FROM PRIZE pr
JOIN PRIZE_TYPE pt ON pr.TypeFK = pt.PrizeTypePK
JOIN PART p ON pr.WinnerFK = p.PartPK
JOIN COMP c ON pr.CompFK = c.CompPK;

Schema Overview

The database consists of 14 interconnected tables organized into logical groups:

Entity Relationship

                    ┌─────────────┐
                    │  ORG_TEAM   │
                    └──────┬──────┘
                           │
              ┌────────────┴────────────┐
              │                         │
              ▼                         ▼
        ┌─────────┐               ┌─────────┐
        │   ORG   │               │  COMP   │
        └────┬────┘               └────┬────┘
             │                         │
             │       ┌─────────────────┼──────────┬──────────┐
             │       │                 │          │          │
             ▼       ▼                 ▼          ▼          ▼
        ┌─────────┐ ┌─────────┐  ┌────────┐ ┌───────┐ ┌─────────┐
        │  EQUIP  │ │ SPONSOR │  │ PRIZE  │ │ PART  │ │COMP_PART│
        └────┬────┘ └────┬────┘  └───┬────┘ └───┬───┘ └─────────┘
             │           │           │          │
             ▼           ▼           │          ▼
      ┌───────────┐ ┌───────────┐    │    ┌─────────┐
      │EQUIP_TYPE │ │SPONSOR_TYPE│   │    │ AVATAR  │
      └───────────┘ └───────────┘    │    └─────────┘
                                     ▼
                               ┌───────────┐
                               │PRIZE_TYPE │
                               └───────────┘

        ┌─────────┐  ┌───────────┐
        │ COUNTRY │  │ COMP_TYPE │
        └─────────┘  └───────────┘
         (Referenced by multiple tables)

Table Documentation

Table	Description	Key Columns
COMP	Competitions/events	Name, City, Location, Year
COMP_TYPE	Competition categories	Name (sport, game, literature, singing, intellectual)
COMP_PART	Junction table linking competitions and participants	CompFK, PartFK (composite PK)
PART	Participants/competitors	Name, Surname, Email, Birthday
AVATAR	Participant profile images	Title, URL
PRIZE	Awards given at competitions	Name, Year
PRIZE_TYPE	Prize categories	Name, Description
SPONSOR	Competition sponsors	Name, Website, ResourcesCount, Currency
SPONSOR_TYPE	Sponsor categories	Name, Description
ORG	Organizations	Name, Website, EmployeeCount
ORG_TEAM	Organization teams that run competitions	Name
EQUIP	Equipment used in competitions	Name, Quantity
EQUIP_TYPE	Equipment categories	EquipName, Description
COUNTRY	Country reference data	Name, Abbrv (ISO code), Currency

Sample Data

The schema comes pre-populated with:

Table	Record Count	Notes
COUNTRY	246	Complete world country list with ISO codes and currencies
COMP_TYPE	5	sport, game, literature, singing, intellectual
COMP	100	Sample competitions spanning years 1970-2017
PART	100	Sample participants with contact information
Additional tables	Variable	Sponsors, prizes, equipment, etc.

SQL Scripts Reference

Script	Purpose
install_user_tablespace.sql	Creates tablespace, user, and grants privileges
uninstall_user_tablespace.sql	Drops user and tablespace with cascade
create_*.sql	Individual table creation scripts
add_constraints.sql	Adds all foreign key constraints
01-13*.sql	Numbered data insertion scripts (executed in order)

License

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