help
Project - CPSC 440 Part 1 In this project, you will create a database to model and support ticket sales for a theater production. Start this project by looking at the color diagram at the very bottom of the document. This is a graph of seats as laid out in a theater. Your SQL code will create tables to store information relating to these seats, along with tickets and customer data. This ERD shows the required tables and their relationships : Requirements: 1. DROP then CREATE a SCHEMA named theater. Use cascade and if exists so that the commands succeed no matter the starting state of the database. Only specified tables will be placed in the theater schema. As you create tables, select appropriate types for all columns. Variable-length text should be text, while fixed 1 or 2 character fields should be char(1) or char(2). IDs should be int unless otherwise specified. 2. CREATE TABLES SeatRow and SeatNum to hold lists of all seat rows and numbers. SeatRow should have one column called row holding values A through R and AA,BB…HH. SeatNum should have one column called num holding values 1-15 and 101-126. Populate these tables using Python. 3. CREATE TABLE Seat with columns SeatRow, SeatNumber, Section, Side, PricingTier, and Wheelchair. Use not null and check to constrain values where and if appropriate. Use the SeatRow and SeatNumber as a composite PK. Put these two columns into two FKs referencing the previous tables' columns. 4. INSERT INTO Seat to populate it with the following: 1. All seats are shown in the illustration in the appendix below. Use this as a guide. 2. Side holds values ‘Right’, ‘Middle’, or ‘Left’. 3. Section holds values ‘Balcony’ or ‘Main Floor’. 4. PricingTier is shows by the color codes and holds values ‘Upper Balcony’, ‘Side’, or ‘Orchestra’. 5. Wheelchair and Handicapped seating occupy rows PQR, numbers 109-122. These 42 entries should be marked Wheelchair=true. All others seats have Wheelchair=false. 6. There is no Row I (the letter after H). 7. There are 805 seats in the theater. SELECT COUNT(*) should reflect this. 8. You can use tricks to make this easier. Seat table population can be accomplished with INSERT INTO SEAT using Python. 9. You can also do quite a bit of work by using UPDATE and exploiting patterns in seat numbering and sections. For example, even numbers over 100 are always on the right. Python supports the modulo operator (100%2 = 0) for checking odds/evens. af://n0 af://n2 Jaiquan Findley Assignment 5. CREATE TABLE Customer with columns CustomerID (PK), FirstName, LastName, and PaymentMethod (Cash, Credit Card). 6. CREATE TABLE Ticket with columns TicketNumber, CustomerID, SeatRow, SeatNumber, and ShowTime. Generate ticket numbers automatically using the “Auto Incremental”, and make it the primary key. Create a constraint ensuring that the no two tickets sell the same seat for the same show (unique composite seat row, seat number, show time). Make CustomerID an FK referencing the Customer table. 7. INSERT INTO tables Customer and Ticket some data to show that John Johnson (Customer ID of 1234) has bought tickets for Row A, Numbers 6, 8, 10, and 9 for a show on April 15 2021 at 2:00pm. He used a credit card. Deliverable: 1. An SQL file consisting entirely of your code to create the tables, in plain text. It must run on a MySQL database. 2. Python file to populate SeatRow and SeatNum tables. Name the file populate_seatrow_seatnum.py. 3. Python file to populate Seat table. Name the file populate_seat.py. (NOTE: Use keyring for you password when you connect to the database) Appendix 1: Phi Beta Kappa Memorial Hall af://n54 Project - CPSC 440 Part 1 Appendix 1: Phi Beta Kappa Memorial Hall