You are given the 13 assumptions listed below and have to design a movie ticket purchase system using Oracle PL/SQL and not writing a graphic user interface. How would you write the code for features 6, 7, 12, and 15?
You can make the following assumptions in this project. 1. The system needs to store data about users. Each user has user id, name of user, email, address, and password. 2. The system needs to store data about cinemas. Each cinema belongs to a company (e.g., AMC, Regal). Each cinema has a cinema ID, cinema name, address, phone, and has one or more auditoriums. 3. Each company (like AMC) has a company ID, company name 4. Each company has discounts for child and senior. The discount rate may vary. E.g., company A may have a rate of 0.8 (ie., 20% off for child and 0.9 (i.e., 10% off) for senior Each cinema auditorium has an auditorium ID (only unique within the cinema), cinema ID auditorium type (regular, 3D, or IMAX), and capacity (#of seats). 3D auditorium can show both regular and 3D movies. Regular auditorium can only show regular movies. IMAX auditorium can only show IMAX movies. Each IMAX cinema auditorium has a seat map with a varchar type seat ID. E.g., '9A' means a seat in row 9. The combination of cinema ID, cinema auditorium ID, and seat ID will uniquely identify a seat in the whole system. 5. 6. 7. The system stores information about movies, including movie ID, title, release date, rating (e.g., 'PG 13'), average jmdb review score, length of the movie (in hours and minutes). 8. The system stores show time of a movie at a cinema, including a show time ID, movie id, auditorium id, cinema id, start time of the show, format (regular, 3D, or IMAX), whether the show is full (no more tickets can be purchased), whether the show allows selection of seat (typically for IMAX format), base price (for adults) For shows that allow selection of seats (typically IMAX movies), the system keeps track of whether a seat in the seat map table is still available for that show time 9. 10. The system stores information about purchase transactions, including a transaction ID, user ID, show ID, purchase time, quantity (#of tickets), status (1 means paid, 0 not paid, 2 canceled), and total amount of the transaction, which includes sum of price of each ticket in the transaction plus a $1.50 fee per ticket and a 6% sales tax. 11. The system stores information about tickets. Each ticket is associated with a purchase transaction. Each ticket has a unique ticket ID, a ticket type (adult, child, or senior), price for that ticket (equals base price * applicable discount rate depending on the type of ticket) optional cinema ID, auditorium ID, and seat ID (identifying the assigned seat), and whether the ticket has been issued (after the transaction has been paid in full) 12. The system stores information about payment. Each payment has a unique payment ID, ID of the transaction the payment is applied to, a payment type (1 means payment, 2 means refund), payment method (1 credit card/gift card, 2 debit card, 3 pavpal), last 4 digits of payment card, amount of payment, and payment time. It is possible to make multiple payment to a transaction (e.g., using a gift card plus a credit card) as long as the total payment reaches the total amount of the transaction. 13. The system stores reviews about cinema auditoriums, including review ID, user ID, cinema ID, a numerical score from 1 to 5, and a textual content and review time. 6. List all movies in a cinema. Input includes cinema name and an input time. a. First check whether any cinema has the given name (could be part of the full name, e.g, AMC If there is no matching cinema, print out no such cinema List all movies shown in that cinema on the same date but after the input time. Print title, format, start time and whether it is full, and order results by cinema name, movie title, cinema, format, and start time. b. c. Features related to purchasing tickets 7. Display available seats for a movie show. Input includes movie id, cinema id, start time of the movie, format. First check whether movie id is valid. If not print a message saying the movie does not exist. Next find a show with given movie id, cinema id, start time, and format, and allows seat selection and is not full. If no match, print a message no such show. Finally, print out all available seats for the matching show, order by show id and then seat id. a. b. c. 12. Add a review for a cinema. Input includes a user ID, cinema ID, review score, and content of review. First check whether user ID, cinema ID are valid and review score is from 1 to 5. If not print an error message. Please also check if the same user has left a review for the same cinema within 30 days. If so print an error message that you cannot review the same place twice within 30 days. Otherwise insert a new review with the review time is current time. a. b. 15. Computer statistics for a given user. Input includes user id and a date range. First check whether the user id is valid. If not print an error message. Otherwise compute for that user the following statistics a. b. c. #of paid transactions, #of tickets bought, total money spent #of canceled transactions, t freuerntyh the salumber of auaions) Hint to compute most frequently visited cinema, you can use subquery in having clause. You can make the following assumptions in this project. 1. The system needs to store data about users. Each user has user id, name of user, email, address, and password. 2. The system needs to store data about cinemas. Each cinema belongs to a company (e.g., AMC, Regal). Each cinema has a cinema ID, cinema name, address, phone, and has one or more auditoriums. 3. Each company (like AMC) has a company ID, company name 4. Each company has discounts for child and senior. The discount rate may vary. E.g., company A may have a rate of 0.8 (ie., 20% off for child and 0.9 (i.e., 10% off) for senior Each cinema auditorium has an auditorium ID (only unique within the cinema), cinema ID auditorium type (regular, 3D, or IMAX), and capacity (#of seats). 3D auditorium can show both regular and 3D movies. Regular auditorium can only show regular movies. IMAX auditorium can only show IMAX movies. Each IMAX cinema auditorium has a seat map with a varchar type seat ID. E.g., '9A' means a seat in row 9. The combination of cinema ID, cinema auditorium ID, and seat ID will uniquely identify a seat in the whole system. 5. 6. 7. The system stores information about movies, including movie ID, title, release date, rating (e.g., 'PG 13'), average jmdb review score, length of the movie (in hours and minutes). 8. The system stores show time of a movie at a cinema, including a show time ID, movie id, auditorium id, cinema id, start time of the show, format (regular, 3D, or IMAX), whether the show is full (no more tickets can be purchased), whether the show allows selection of seat (typically for IMAX format), base price (for adults) For shows that allow selection of seats (typically IMAX movies), the system keeps track of whether a seat in the seat map table is still available for that show time 9. 10. The system stores information about purchase transactions, including a transaction ID, user ID, show ID, purchase time, quantity (#of tickets), status (1 means paid, 0 not paid, 2 canceled), and total amount of the transaction, which includes sum of price of each ticket in the transaction plus a $1.50 fee per ticket and a 6% sales tax. 11. The system stores information about tickets. Each ticket is associated with a purchase transaction. Each ticket has a unique ticket ID, a ticket type (adult, child, or senior), price for that ticket (equals base price * applicable discount rate depending on the type of ticket) optional cinema ID, auditorium ID, and seat ID (identifying the assigned seat), and whether the ticket has been issued (after the transaction has been paid in full) 12. The system stores information about payment. Each payment has a unique payment ID, ID of the transaction the payment is applied to, a payment type (1 means payment, 2 means refund), payment method (1 credit card/gift card, 2 debit card, 3 pavpal), last 4 digits of payment card, amount of payment, and payment time. It is possible to make multiple payment to a transaction (e.g., using a gift card plus a credit card) as long as the total payment reaches the total amount of the transaction. 13. The system stores reviews about cinema auditoriums, including review ID, user ID, cinema ID, a numerical score from 1 to 5, and a textual content and review time. 6. List all movies in a cinema. Input includes cinema name and an input time. a. First check whether any cinema has the given name (could be part of the full name, e.g, AMC If there is no matching cinema, print out no such cinema List all movies shown in that cinema on the same date but after the input time. Print title, format, start time and whether it is full, and order results by cinema name, movie title, cinema, format, and start time. b. c. Features related to purchasing tickets 7. Display available seats for a movie show. Input includes movie id, cinema id, start time of the movie, format. First check whether movie id is valid. If not print a message saying the movie does not exist. Next find a show with given movie id, cinema id, start time, and format, and allows seat selection and is not full. If no match, print a message no such show. Finally, print out all available seats for the matching show, order by show id and then seat id. a. b. c. 12. Add a review for a cinema. Input includes a user ID, cinema ID, review score, and content of review. First check whether user ID, cinema ID are valid and review score is from 1 to 5. If not print an error message. Please also check if the same user has left a review for the same cinema within 30 days. If so print an error message that you cannot review the same place twice within 30 days. Otherwise insert a new review with the review time is current time. a. b. 15. Computer statistics for a given user. Input includes user id and a date range. First check whether the user id is valid. If not print an error message. Otherwise compute for that user the following statistics a. b. c. #of paid transactions, #of tickets bought, total money spent #of canceled transactions, t freuerntyh the salumber of auaions) Hint to compute most frequently visited cinema, you can use subquery in having clause
