SQL Database Design Project. Conference Room Booking database.

Objective: Design a database for a company hosting conferences for start-up Tech companies to present their businesses and seek venture capital.

The database will be accounting:

1. Each of the individual attendees: those who provide venture capital
   

2. Each of the individual start-up Tech companies
   

3. Each of the individual presentations
   

4. Conference room
   

5. Each of individual tickets for registered attendees to attend individual presentation

Five tables with corr. columns are defined as below.

1. ATTENDEE ATTENDEE_ID, FIRST_NAME, LAST_NAME, PHONE, EMAIL, IS_VIP
   

2. COMPANY COMPANY_ID, NAME, DESCRPITON, PRIMARY_CONTACT_ATTENDEE_ID.
   

3. PRESENTATION PRESENTATION_ID, BOOKED_COMPANY_ID, BOOKED_ROOM_ID, START_TIME, END_TIME.
   

4. ROOM ROOM_ID, FLOOR_NUMBER, SEAT_CAPACITY.
   

5. TICKET TICKET_ID, PRESENTATION_ID, ATTENDEE_ID.

Study the primary key and foreign key relationships among tables

Forward

1 -> 2 by ATTENDEE_ID/ PRIMARY_CONTACT_ATTENDEE_ID;

2 -> 3 by COMPANY_ID/ BOOKED_COMPANY_ID;

1 -> 5 by ATTENDEE_ID;

3 -> 5 by PRESENTATION_ID;

4 ->5 NULL

Backward

4 -> 3 by ROOM_ID/ BOOKED_ROOM_ID

Using a table to represent the directed graph

Reading vertically, table COMPANY and PRESENTATION both have 1 foreign key. Table TICKET has 2 foreign keys.

Visualizing the directed graph ATTENDEE is the most important node.

Create database and Tables

Create database and tables using user interface provided. The following are equivalent command. Some declaration of foreign key are after the definition of corr. primary key from other table.

CREATE TABLE ATTENDEE (

ATTENDEE_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

FIRST_NAME VARCHAR NOT NULL,

LAST_NAME VARCHAR NOT NULL,

PHONE INTEGER NOT NULL,

EMAIL VARCHAR NOT NULL,

IS_VIP BOOLEAN DEFAULT (0)

);

CREATE TABLE COMPANY (

COMPANY_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

NAME VARCHAR NOT NULL,

DESCRPITON VARCHAR (100),

PRIMARY_CONTACT_ATTENDEE_ID INTEGER REFERENCES ATTENDEE (ATTENDEE_ID) NOT NULL

);

CREATE TABLE ROOM (

ROOM_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

FLOOR_NUMBER INTEGER NOT NULL,

SEAT_CAPACITY INTEGER NOT NULL

);

CREATE TABLE PRESENTATION (

PRESENTATION_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

BOOKED_COMPANY_ID INTEGER REFERENCES COMPANY (COMPANY_ID) NOT NULL,

BOOKED_ROOM_ID INTEGER NOT NULL ROOM (ROOM_ID),

START_TIME DATETIME NOT NULL,

END_TIME DATETIME NOT NULL

);

CREATE TABLE TICKET (

TICKET_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

PRESENTATION_ID INTEGER NOT NULL REFERENCES PRESENTATION (PRESENTATION_ID),

ATTENDEE_ID INTEGER REFERENCES ATTENDEE (ATTENDEE_ID) NOT NULL

);

Create a VIEW for details of existing presentations

CREATE VIEW [Details of presentation table] AS

SELECT PRESENTATION.PRESENTATION_ID AS presentation_id,

PRESENTATION.START_TIME AS start_time,

PRESENTATION.END_TIME AS end_time,

COMPANY.NAME AS booked_company_name,

ROOM.ROOM_ID AS room_id,

ROOM.FLOOR_NUMBER AS floor,

ROOM.SEAT_CAPACITY AS room_capacity

FROM PRESENTATION

INNER JOIN

COMPANY ON PRESENTATION.BOOKED_COMPANY_ID = COMPANY.COMPANY_ID

INNER JOIN

ROOM ON PRESENTATION.BOOKED_ROOM_ID = ROOM.ROOM_ID;