Scribd
Upload
86 views1 page

Database Design for Ticket Management

The document outlines a final exam for a database module at Hanoi University of Science and Technology, focusing on creating a database for managing ticket vending information for a musical tour. It includes tasks such as designing an entity-relationship model, converting it to a relational model, and writing SQL statements for various queries related to shows and attendees. The exam is scheduled for July 24, 2023, and consists of ten questions, each worth one mark.

Uploaded by

Duyên Tuấn Minh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
86 views1 page

Database Design for Ticket Management

The document outlines a final exam for a database module at Hanoi University of Science and Technology, focusing on creating a database for managing ticket vending information for a musical tour. It includes tasks such as designing an entity-relationship model, converting it to a relational model, and writing SQL statements for various queries related to shows and attendees. The exam is scheduled for July 24, 2023, and consists of ten questions, each worth one mark.

Uploaded by

Duyên Tuấn Minh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

HANOI UNIVERSITY OF SCIENCE FINAL EXAM

AND TECHNOLOGY Module: DATABASE (AC2040E)


SCHOOL OF ELECTRICAL AND Date: July 24, 2023 9:30
ELECTRONICS ENGINEERING Duration: 60 minutes
(Paper documents allowed. Write directly on this sheet.)
Signatures Lecturer in charge: Group leader:
Đào Trung Kiên Lê Thị Lan
The goal of this exercise is to establish a database to manage the ticket vending information for a
musical tour which includes multiple shows taking place at different locations. A show takes place
at a given location, and the tickets for that show are distributed or sold with a certain number of
types (such as VIP, invited, ordinary, promotional,…).

The required relations are:

- Location which includes the information of the locations where the shows take place: ID,
name, address.
- Show which includes the information of the shows: ID, name, date and time, location.
- Ticket type which includes the information of the ticket types: ID, name, corresponding
show, price, quantity.
- Attendee which includes the information of the customers who have purchased a ticket:
ID, name, birthday, gender, ticket type.

Questions (each question is worth 1 mark):

1. Design the required database using the entity-relationship (ER) model, with all the necessary
entities, attributes and relationships.
2. Convert the above database to the relational model, ensuring that the given database model
satisfies the functional dependency requirements for data redundancy.
3. Specify all the primary and foreign keys of the relations in the designed database.
Write the SQL statements to establish the designed database.
4. Write the SQL statements to: Add a show with at least 3 different types of tickets (students can
key in the sample information).
5. Use relational algebra to write the following query: Find the attendees (ID, name) who have
invited tickets for a show at the Opera House on July 24, 2023.
Then write the corresponding SQL statement for this query.
6. Write SQL statement to: Calculate the total revenue for all the shows in July 2023.
7. Write SQL statement to: List the shows (ID, name) together with the revenue of those shows,
sorted by date of the show in descending order.
8. Write SQL statement to: Find the future show (ID, name) with the most number of tickets
having not been sold out.
9. Write SQL statement to: List the attendees (ID, name) with tickets for all the shows in July
2023.
10. Write SQL statement to: List the shows (ID, name) where more than 50% of the attendees
who have purchased tickets are 20 years old or younger.

--------- End ---------

You might also like