TABLE NAME THEATER EMPLOYEE CHECK MENU CHECK ITEM LOYALTY CUSTOMER MOVIE TICKET MOVIE SCREENS MOVIE SCREENING...

Transcribed Image Text:

TABLE NAME THEATER EMPLOYEE CHECK MENU CHECK ITEM LOYALTY CUSTOMER MOVIE TICKET MOVIE SCREENS MOVIE SCREENING ATTRIBUTE NAME T_ID T_NAME T_CITY T_STATE T_THEME E_ID E_FNAME E_LNAME E_DOB E ADDRESS E CITY E STATE E_ZIP E PHONENUMBER E HIRED E SALARY E MNGID T_ID C_NO C_AMOUNT E_ID CU_ID C_DATE C_TIME C_TYPE C_TAX C_TIP C_DISCOUNT MT_NO ME_ITEMID ME_ITEM ME_CATEGORY ME_SIZE ME_ORDERQTY ME QTYONHAND ME_RESTOCKLVL C_NO ME_ITEMID CH_QTY CH_COST CH_EXTCOST CU_ID CU_FNAME CU_LNAME CU_DOB CU LOYALTYLVL CU_NUMBEROFVISITS MT_NO MT SEAT M_ID M_ID M_NAME M TIME MS_ID M_ID MS_ID MS RN MS SEATCAP CONTENTS Theater ID Theater Name Theater City Theater State Theater Theme Employee ID Employee First Name Employee Last Name Employee Birthday Employee Address Employee City Employee State Employee Zip Employee Phone Number Employee Hire Date Employee Salary Employee Manager ID Theater ID Check Number Check Amount Employee ID Customer ID Check Date Check Time Check Type Check Tax Check Tip Check Discount Movie Ticket Number Menu Item ID Menu Item Name Menu Item Category Menu Item Size Menu Item Order Quantity Check Number Menu Item ID Check Item Quantity Check Item Cost Check Item Extended Cost Customer ID Customer First Name Customer Last Name Customer Birthday Customer Loyalty Level Customer Number of Visits Movie Ticket Number Movie Ticket Seat Movie ID Movie ID Movie Name Movie Time TYPE INTEGER VARCHAR(35) VARCHAR(20) CHAR(2) VARCHAR(20) Menu Item Quantity On Hand INTEGER Menu Item Restock Level CHAR(1) Movie Screening ID Movie ID INTEGER VARCHAR(15) VARCHAR(15) DATE VARCHAR(30) VARCHAR(20) CHAR(2) CHAR(5) CHAR(12) DATE INTEGER INTEGER INTEGER INTEGER DECIMAL(8,2) INTEGER INTEGER DATE TIME VARCHAR(15) DECIMAL(6,2) DECIMAL(6,2) INTEGER VARCHAR(50) VARCHAR(15) CHAR(2) INTEGER INTEGER INTEGER INTEGER DECIMAL(6,2) DECIMAL (6,2) INTEGER VARCHAR(30) VARCHAR(30) DATE INTEGER CHAR(3) INTEGER FORMAT TIME JL. JI.... INTEGER INTEGER Xxxxxxxxxxxxxxxxxxxx Xxxxxxxxxxxxx Movie Screening ID Movie Screen Room Number INTEGER VARCHAR(3) Screen Seating Capacity INTEGER N/A N/A N/A XxxxxxxxxxxxxxxxxxxX N/A DECIMAL (6,2) ###.## INTEGER Xxxxxxxxxxxxxxxxxxxxx N/A Xxxxxxxxxxxx N/A DD-MM-YYYY N/A xxxxxxxxxxxxxxx N/A Xxxxxxxxxxxx N/A 99999 N/A N/A 999-999-9999 N/A DD-MM-YYYY N/A hh:mm ###.## ###.## DD-MM-YYYY N/A N/A Xxxxxxxxxxxx N/A N/A N/A N/A 001-999 #### ### VARCHAR(6) Xxxxxx INTEGER ###.## ### RANGE* 001-999 INTEGER VARCHAR(50) hh:mm Xxxxxxxxxxxxxxx N/A Xxxxxxxxxxxxxx N/A N/A Xxx 001-999 ## ## ### ## 001-999 Xxxxxxxxxxxxxxxxxxxxxx N/A Xxxxxxxxxxxxxxxxxxx N/A DD-MM-YYYY N/A N/A XXX 01-99 001-999 1-99999 N/A 001-999 001-999 00000-99999 Y Y Y 001-999 001-9999 001-9999 N/A 1-99999 001-999 001-999 N/A N/A 0001-9999 001-999 A01-299 01-99 01-99 N/A N/A 001-999 01-99 REQUIRED 001-999 S01-S99 1-999 Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y PK OR FK PK PK FK PK FK FK FK PK PK, FK PK, FK PK PK FK PK PK, FK PK, FK PK FK REFERENCED TABLE THEATER EMPLOYEE LOYALTY CUSTOMER MOVIE TICKET CHECK NUMBER MENU MOVIE SCREENING MOVIE Basic Tests Required: Each group member must complete at least one query - label them with the creator. Two INSERT statements in a row - the first one adding a new Customer, the second one adding a new Ticket for that Customer, should succeed. A similar two INSERT statements executed in the opposite order should fail. Deleting a theater which has employees should fail. For each check, display menu items sold, total check amount and tips added. Which menu item sells best, has highest sales? Which bar item? Construct 4 other preliminary tests to show that your database design works. Advanced Tests Required: You will construct, test, and demonstrate 6 advanced queries based on your design that succeed. Each group member must complete at least one query - label them with the creator. 2 must use the GROUP BY/HAVING clauses 2 must use fully NESTED queries (no joins) 2 must be Views Make sure your advanced queries are substantial; show what your database can do rather than quick, easy queries. TABLE NAME THEATER EMPLOYEE CHECK MENU CHECK ITEM LOYALTY CUSTOMER MOVIE TICKET MOVIE SCREENS MOVIE SCREENING ATTRIBUTE NAME T_ID T_NAME T_CITY T_STATE T_THEME E_ID E_FNAME E_LNAME E_DOB E ADDRESS E CITY E STATE E_ZIP E PHONENUMBER E HIRED E SALARY E MNGID T_ID C_NO C_AMOUNT E_ID CU_ID C_DATE C_TIME C_TYPE C_TAX C_TIP C_DISCOUNT MT_NO ME_ITEMID ME_ITEM ME_CATEGORY ME_SIZE ME_ORDERQTY ME QTYONHAND ME_RESTOCKLVL C_NO ME_ITEMID CH_QTY CH_COST CH_EXTCOST CU_ID CU_FNAME CU_LNAME CU_DOB CU LOYALTYLVL CU_NUMBEROFVISITS MT_NO MT SEAT M_ID M_ID M_NAME M TIME MS_ID M_ID MS_ID MS RN MS SEATCAP CONTENTS Theater ID Theater Name Theater City Theater State Theater Theme Employee ID Employee First Name Employee Last Name Employee Birthday Employee Address Employee City Employee State Employee Zip Employee Phone Number Employee Hire Date Employee Salary Employee Manager ID Theater ID Check Number Check Amount Employee ID Customer ID Check Date Check Time Check Type Check Tax Check Tip Check Discount Movie Ticket Number Menu Item ID Menu Item Name Menu Item Category Menu Item Size Menu Item Order Quantity Check Number Menu Item ID Check Item Quantity Check Item Cost Check Item Extended Cost Customer ID Customer First Name Customer Last Name Customer Birthday Customer Loyalty Level Customer Number of Visits Movie Ticket Number Movie Ticket Seat Movie ID Movie ID Movie Name Movie Time TYPE INTEGER VARCHAR(35) VARCHAR(20) CHAR(2) VARCHAR(20) Menu Item Quantity On Hand INTEGER Menu Item Restock Level CHAR(1) Movie Screening ID Movie ID INTEGER VARCHAR(15) VARCHAR(15) DATE VARCHAR(30) VARCHAR(20) CHAR(2) CHAR(5) CHAR(12) DATE INTEGER INTEGER INTEGER INTEGER DECIMAL(8,2) INTEGER INTEGER DATE TIME VARCHAR(15) DECIMAL(6,2) DECIMAL(6,2) INTEGER VARCHAR(50) VARCHAR(15) CHAR(2) INTEGER INTEGER INTEGER INTEGER DECIMAL(6,2) DECIMAL (6,2) INTEGER VARCHAR(30) VARCHAR(30) DATE INTEGER CHAR(3) INTEGER FORMAT TIME JL. JI.... INTEGER INTEGER Xxxxxxxxxxxxxxxxxxxx Xxxxxxxxxxxxx Movie Screening ID Movie Screen Room Number INTEGER VARCHAR(3) Screen Seating Capacity INTEGER N/A N/A N/A XxxxxxxxxxxxxxxxxxxX N/A DECIMAL (6,2) ###.## INTEGER Xxxxxxxxxxxxxxxxxxxxx N/A Xxxxxxxxxxxx N/A DD-MM-YYYY N/A xxxxxxxxxxxxxxx N/A Xxxxxxxxxxxx N/A 99999 N/A N/A 999-999-9999 N/A DD-MM-YYYY N/A hh:mm ###.## ###.## DD-MM-YYYY N/A N/A Xxxxxxxxxxxx N/A N/A N/A N/A 001-999 #### ### VARCHAR(6) Xxxxxx INTEGER ###.## ### RANGE* 001-999 INTEGER VARCHAR(50) hh:mm Xxxxxxxxxxxxxxx N/A Xxxxxxxxxxxxxx N/A N/A Xxx 001-999 ## ## ### ## 001-999 Xxxxxxxxxxxxxxxxxxxxxx N/A Xxxxxxxxxxxxxxxxxxx N/A DD-MM-YYYY N/A N/A XXX 01-99 001-999 1-99999 N/A 001-999 001-999 00000-99999 Y Y Y 001-999 001-9999 001-9999 N/A 1-99999 001-999 001-999 N/A N/A 0001-9999 001-999 A01-299 01-99 01-99 N/A N/A 001-999 01-99 REQUIRED 001-999 S01-S99 1-999 Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y PK OR FK PK PK FK PK FK FK FK PK PK, FK PK, FK PK PK FK PK PK, FK PK, FK PK FK REFERENCED TABLE THEATER EMPLOYEE LOYALTY CUSTOMER MOVIE TICKET CHECK NUMBER MENU MOVIE SCREENING MOVIE Basic Tests Required: Each group member must complete at least one query - label them with the creator. Two INSERT statements in a row - the first one adding a new Customer, the second one adding a new Ticket for that Customer, should succeed. A similar two INSERT statements executed in the opposite order should fail. Deleting a theater which has employees should fail. For each check, display menu items sold, total check amount and tips added. Which menu item sells best, has highest sales? Which bar item? Construct 4 other preliminary tests to show that your database design works. Advanced Tests Required: You will construct, test, and demonstrate 6 advanced queries based on your design that succeed. Each group member must complete at least one query - label them with the creator. 2 must use the GROUP BY/HAVING clauses 2 must use fully NESTED queries (no joins) 2 must be Views Make sure your advanced queries are substantial; show what your database can do rather than quick, easy queries.