Banquet Management System

Please check out the package branch for the packaged version of this application.

YANG Xikun, YANG Jinkun, REN Yixiao, Arda EREN, MIAO Xutao

Designer of the application: YANG Xikun, YANG Jinkun

Maintainer of the repo: YANG Jinkun

Designer of the database schemas: REN Yixiao

Overview

This document outlines the database schema for a Banquet Management System. The system manages banquets, meals, attendees, registrations, and administrators.

Tables

BANQUETS

Stores information about banquet events.

Column	Type	Constraints	Description
BIN	INTEGER	PRIMARY KEY, AUTOINCREMENT	Unique banquet identifier
Name	VARCHAR(255)	NOT NULL	Name of the banquet
DateTime	DATETIME	NOT NULL	Date and time of the banquet
Address	VARCHAR(255)	NOT NULL	Physical address
Location	VARCHAR(255)	NOT NULL	Specific location details
ContactStaffName	VARCHAR(255)	NOT NULL	Staff contact person
Available	CHAR(1)	NOT NULL, CHECK ( IN ('Y', 'N') )	Availability status
Quota	INTEGER	NOT NULL, CHECK ( >= 0 )	Maximum capacity

MEALS

Contains meal options for each banquet.

Column	Type	Constraints	Description
BIN	INTEGER	NOT NULL, FK	Reference to BANQUETS
ID	INTEGER	NOT NULL	Meal identifier
Name	VARCHAR(255)	NOT NULL	Name of the meal
Type	VARCHAR(255)	NOT NULL	Type of meal
Price	DECIMAL(5,2)	NOT NULL	Cost of the meal
SpecialCuisine	VARCHAR(255)		Special cuisine details

Primary Key: (BIN, ID)
Foreign Key: BIN REFERENCES BANQUETS(BIN)

ATTENDEE_ACCOUNTS

Manages attendee information.

Column	Type	Constraints	Description
ID	VARCHAR(255)	PRIMARY KEY	Unique attendee identifier
HashedPassword	VARCHAR(255)	NOT NULL	Encrypted password
HashedSalt	VARCHAR(255)	NOT NULL	Password salt
Name	VARCHAR(255)	NOT NULL	Attendee name
Type	VARCHAR(255)	NOT NULL	Attendee type
MobileNo	INTEGER	NOT NULL, CHECK( 8 DIGITS )	Contact number
Organization	VARCHAR(255)		Associated organization

REGISTRATIONS

Tracks banquet registrations.

Column	Type	Constraints	Description
ID	INTEGER	PRIMARY KEY, AUTOINCREMENT	Registration identifier
AttendeeID	VARCHAR(255)	NOT NULL, FK	Reference to ATTENDEE_ACCOUNTS
GuestName	VARCHAR(255)	NOT NULL	Name of guest
BIN	INTEGER	NOT NULL, FK	Reference to BANQUETS
MealID	INTEGER	NOT NULL, FK	Reference to MEALS
Drink	VARCHAR(255)	NOT NULL	Drink preference
Seat	VARCHAR(255)		Seat assignment

Foreign Keys:

• AttendeeID REFERENCES ATTENDEE_ACCOUNTS(ID)
• BIN REFERENCES BANQUETS(BIN)
• (BIN, MealID) REFERENCES MEALS(BIN, ID)

ADMINISTRATORS

Stores administrator accounts.

Column	Type	Constraints	Description
ID	VARCHAR(255)	PRIMARY KEY	Admin identifier
HashedPassword	VARCHAR(255)	NOT NULL	Encrypted password
HashedSalt	VARCHAR(255)	NOT NULL	Password salt

Relationships

• Each BANQUET can have 0 ~ 4 MEALS
• Each REGISTRATION is associated with one ATTENDEE_ACCOUNT, one BANQUET, and one MEAL
• All foreign key relationships implements ON DELETE CASCADE.

Notes

• Mobile numbers are restricted to 8 digits
• Availability is marked with Y or N
• Quota must be non-negative
• DateTime is stored as TEXT in SQLite

DDL for the table creation

SQL DDL courtesy of YANG Xikun and REN Yixiao:

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS BANQUETS (
    BIN INTEGER PRIMARY KEY AUTOINCREMENT,
    Name VARCHAR(255) NOT NULL,
    DateTime DATETIME NOT NULL, -- This will then be TEXT.
    Address VARCHAR(255) NOT NULL,
    Location VARCHAR(255) NOT NULL,
    ContactStaffName VARCHAR(255) NOT NULL,
    Available CHAR(1) NOT NULL CHECK ( Available IN ('Y', 'N') ),
    Quota INTEGER NOT NULL CHECK ( Quota >= 0 )
);

CREATE TABLE IF NOT EXISTS MEALS (
    BIN INTEGER NOT NULL,
    ID INTEGER NOT NULL,
    Name VARCHAR(255) NOT NULL,
    Type VARCHAR(255) NOT NULL,
    Price DECIMAL(5,2) NOT NULL CHECK ( Price >= 0 ),
    SpecialCuisine VARCHAR(255),
    CONSTRAINT MEALS_PK PRIMARY KEY (BIN, ID),
    CONSTRAINT MEALS_FK FOREIGN KEY (BIN) REFERENCES BANQUETS(BIN) ON DELETE CASCADE -- ON UPDATE CASCADE;
);

CREATE TABLE IF NOT EXISTS ATTENDEE_ACCOUNTS (
    ID VARCHAR(255) PRIMARY KEY CHECK ( ID LIKE '_%@_%._%' ),
    HashedPassword VARCHAR(255) NOT NULL,
    HashedSalt VARCHAR(255) NOT NULL,
    Name VARCHAR(255) NOT NULL, -- the check are assigned to the Model part
    Address VARCHAR(255),
    Type VARCHAR(255) NOT NULL,
    MobileNo INTEGER NOT NULL CHECK ( LENGTH ( CAST ( MobileNo AS TEXT ) ) = 8 ),
    Organization VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS REGISTRATIONS (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    AttendeeID VARCHAR(255),
    GuestName VARCHAR(255),
    BIN INTEGER NOT NULL,
    MealID INTEGER NOT NULL,
    Drink VARCHAR(255) NOT NULL,
    Seat VARCHAR(255),
    FOREIGN KEY (AttendeeID) REFERENCES ATTENDEE_ACCOUNTS(ID) ON DELETE CASCADE,
    FOREIGN KEY (BIN) REFERENCES BANQUETS(BIN) ON DELETE CASCADE,
    FOREIGN KEY (BIN, MealID) REFERENCES MEALS(BIN, ID) ON DELETE CASCADE,
    CONSTRAINT IDENTIFIER CHECK ( AttendeeID IS NOT NULL OR GuestName IS NOT NULL )
);

CREATE TABLE IF NOT EXISTS ADMINISTRATORS (
    ID VARCHAR(255) PRIMARY KEY,
    HashedPassword VARCHAR(255) NOT NULL,
    HashedSalt VARCHAR(255) NOT NULL
);