top of page
Microsoft Access Database
Task:
My team and I were tasked with developing a database that could be used to store various information for a target user.
Implementation:
We decided on designing a database with movie theaters as the targeted user. This database stores various information about the movies being shown, reservations, customers, etc.
Relationship Scema
Snapshot of the Customers Relation
This outlines all the relationships, relations, primary, and foreign keys of the database
Querying:
My team also developed multiple queries for the database that the users could use to easily access various information from the database.
Example: This query gives the user the average movie runtime for each customer.
The SQL for the query
SELECT Customers.[Last Name]+", "+Customers.[First Name] AS Customer, ROUND(AVG(Movies.[Runtime(minutes)])) AS [Average Sit Time]
FROM Customers, Movies, Reservations
WHERE Customers.[Customer ID] = Reservations.[Customer ID] AND Reservations.[Movie ID] = Movies.[Movie ID]
GROUP BY Customers.[Last Name], Customers.[First Name];
SELECT Theatres.[Theatre ID], Sum(Reservations.[Ticket Cost]) AS [Ticket Cost]
FROM Theatres INNER JOIN Reservations ON Theatres.[Theatre ID] = Reservations.[Theatre ID]
GROUP BY Theatres.[Theatre ID]
ORDER BY Sum(Reservations.[Ticket Cost]);
The query shows the sum of the ticket sales for each movie theater in the database. This is accomplished by using inner join to get relative information from both the Theatres Relation and Reservations Relation
Simple user friendly GUI to review and Add entires directly into the database
The GUI was made by putting together all the forms we created. For example, the Reservations button leads the user to the form "add reservation" which is displayed in the bottom image.
bottom of page