CREATE TABLE event
(
    event_id   TEXT
        constraint event_pk
            primary key,
    event_name TEXT,
    event_date TEXT,
    type       TEXT,
    notes      TEXT,
    location   TEXT,
    status     TEXT
);
event
CREATE TABLE major
(
    major_id   TEXT
        constraint major_pk
            primary key,
    major_name TEXT,
    department TEXT,
    college    TEXT
);
major
CREATE TABLE zip_code
(
    zip_code    INTEGER
        constraint zip_code_pk
            primary key,
    type        TEXT,
    city        TEXT,
    county      TEXT,
    state       TEXT,
    short_state TEXT
);
zip_code
CREATE TABLE "attendance"
(
    link_to_event  TEXT,
    link_to_member TEXT,
    primary key (link_to_event, link_to_member),
    foreign key (link_to_event) references event(event_id),
    foreign key (link_to_member) references member(member_id)
);
attendance
CREATE TABLE "budget"
(
    budget_id     TEXT
            primary key,
    category      TEXT,
    spent         REAL,
    remaining     REAL,
    amount        INTEGER,
    event_status  TEXT,
    link_to_event TEXT,
    foreign key (link_to_event) references event(event_id)
);
budget
CREATE TABLE "expense"
(
    expense_id          TEXT
            primary key,
    expense_description TEXT,
    expense_date        TEXT,
    cost                REAL,
    approved            TEXT,
    link_to_member      TEXT,
    link_to_budget      TEXT,
    foreign key (link_to_budget) references budget(budget_id),
    foreign key (link_to_member) references member(member_id)
);
expense
CREATE TABLE "income"
(
    income_id      TEXT
        constraint income_pk
            primary key,
    date_received  TEXT,
    amount         INTEGER,
    source         TEXT,
    notes          TEXT,
    link_to_member TEXT,
    foreign key (link_to_member) references member(member_id)
);
income
CREATE TABLE "member"
(
    member_id     TEXT
        constraint member_pk
            primary key,
    first_name    TEXT,
    last_name     TEXT,
    email         TEXT,
    position      TEXT,
    t_shirt_size  TEXT,
    phone         TEXT,
    zip           INTEGER,
    link_to_major TEXT,
    foreign key (link_to_major) references major(major_id),
    foreign key (zip) references zip_code(zip_code)
);
member
original_column_name,column_name,column_description,data_format,value_description
income_id,income id,A unique identifier for each record of income,text,
date_received,date received,the date that the fund received,text,
amount,,amount of funds,integer,the unit is dollar
source,,"A value indicating where the funds come from such as dues, or the annual university allocation",text,
notes,,A free-text value giving any needed details about the receipt of funds,text,
link_to_member,link to member,link to member,text,
Income
original_column_name,column_name,column_description,data_format,value_description
zip_code,zip code,The ZIP code itself. A five-digit number identifying a US post office.,integer,
type,,The kind of ZIP code,text,"commonsense evidence: 
� Standard: the normal codes with which most people are familiar 
� PO Box: zip codes have post office boxes 
� Unique: zip codes that are assigned to individual organizations."
city,,The city to which the ZIP pertains,text,
county,,The county to which the ZIP pertains,text,
state,,The name of the state to which the ZIP pertains,text,
short_state,short state,The abbreviation of the state to which the ZIP pertains,text,
Zip_Code
original_column_name,column_name,column_description,data_format,value_description
event_id,event id,A unique identifier for the event,text,
event_name,event name,event name,text,
event_date,event date,The date the event took place or is scheduled to take place,text,e.g. 2020-03-10T12:00:00
type,,"The kind of event, such as game, social, election",text,
notes,,A free text field for any notes about the event,text,
location,,Address where the event was held or is to be held or the name of such a location,text,
status,,"One of three values indicating if the event is in planning, is opened, or is closed",text,Open/ Closed/ Planning
Event
original_column_name,column_name,column_description,data_format,value_description
budget_id,budget id,A unique identifier for the budget entry,text,
category,,"The area for which the amount is budgeted, such as, advertisement, food, parking",text,
spent,,The total amount spent in the budgeted category for an event.,real,the unit is dollar. This is summarized from the Expense table
remaining,,A value calculated as the amount budgeted minus the amount spent,real,"the unit is dollar 
commonsense evidence: If the remaining < 0, it means that the cost has exceeded the budget."
amount,,The amount budgeted for the specified category and event,integer,"the unit is dollar 
commonsense evidence:
some computation like: amount = spent + remaining "
event_status,event status,the status of the event,text,"Closed / Open/ Planning 
commonsense evidence: 
• Closed: It means that the event is closed. The spent and the remaining won't change anymore.
• Open: It means that the event is already opened. The spent and the remaining will change with new expenses.
• Planning: The event is not started yet but is planning. The spent and the remaining won't change at this stage. "
link_to_event,link to event,The unique identifier of the event to which the budget line applies.,text,References the Event table
Budget
original_column_name,column_name,column_description,data_format,value_description
expense_id,expense id,unique id of income,text,
expense_description,expense description,A textual description of what the money was spend for,text,
expense_date,expense date,The date the expense was incurred,text,e.g. YYYY-MM-DD
cost,,The dollar amount of the expense,real,the unit is dollar
approved,,A true or false value indicating if the expense was approved,text,true/ false
link_to_member,link to member,The member who incurred the expense,text,
link_to_budget,link to budget,The unique identifier of the record in the Budget table that indicates the expected total expenditure for a given category and event. ,text,References the Budget table
Expense
original_column_name,column_name,column_description,data_format,value_description
member_id,member id,unique id of member,text,
first_name,first name,member's first name,text,
last_name,last name,member's last name,text,"commonsense evidence: 
full name is first_name + last_name. e.g. A member's first name is Angela and last name is Sanders. Thus, his/her full name is Angela Sanders."
email,,member's email,text,
position,,The position the member holds in the club,text,
t_shirt_size,,The size of tee shirt that member wants when shirts are ordered,text,commonsense evidence: usually the student ordered t-shirt with lager size has bigger body shape 
phone,,The best telephone at which to contact the member,text,
zip,,the zip code of the member's hometown,integer,
link_to_major,link to major,The unique identifier of the major of the member. References the Major table,text,
Member
original_column_name,column_name,column_description,data_format,value_description
link_to_event,link to event,The unique identifier of the event which was attended,text,References the Event table
link_to_member,link to member,The unique identifier of the member who attended the event,text,References the Member table
Attendance
original_column_name,column_name,column_description,data_format,value_description
major_id,major id,A unique identifier for each major,text,
major_name,major name,major name,text,
department,,The name of the department that offers the major,text,
college,,The name college that houses the department that offers the major,text,
Major