Qn 1 – System Design and SQL
You are tasked by OpenSpace to design a session booking system for
their co-sharing space throughout
India. Below are the functionalities of the system:
a. Allow user to start a session by keying in the start time and
duration of use of the co-sharing space in blocks of 30 minutes.
User can only have one active session at one time.
b. Display the cost of the usage. Note: Different co-sharing space
have different rates per 30 minutes.
c. Allow user who already have an active session to extend their
session. *A session is considered active when the current date and
time falls within the start date time and end date time of the
d. Allow user who already have existing session that are still active to
end their session early.
e. Display the refund amount if user end their session early.
f. Display the current capacity of each co-working space. *Current
capacity is defined as the total capacity of the co-sharing space
minus the number of active sessions.
g. Allow user to see their transaction history. Their transaction history
should show the timing for each session and its cost. An extension
or early ending is not considered a separate session.
If the user session is from 1pm to 2pm and the session ended early at
1:45pm. The transaction history should only show 1 pm to 1:45pm with
the total amount paid for the initial session from 1pm to 2pm and the
refund amount for 1:45pm to 2pm.
If the user session is from 1pm to 2pm and the session is extended to
2:30pm. The transaction history should show 1 pm to 2:30pm with the
total amount paid for the whole duration.
If the user session is from 1pm to 2pm and started another session from
3pm to 4pm. The transaction
history should show 2 session and the total amount paid for the 2
Base on requirement above design a Relational Database Model and
write create table scripts using any of the below SQL Syntax in the
working space on the right
1. Oracle SQL
2. MySQL/SQL Server
Based on the RDM Model you have designed in Question 1, write a SQL
script in the working space on the right to
a) Extract the transaction history of the logged in user
b) Extract the total amount paid and refunded for the month for each
c) Extract the current capacity of each co-sharing space.
Such a cheap price for your free time and healthy sleep
All online transactions are done using all major Credit Cards or Electronic Check through PayPal. These are safe, secure, and efficient online payment methods.