TABLE NAME THEATER EMPLOYEE CHECK MENU CHECK ITEM LOYALTY CUSTOMER MOVIE TICKET MOVIE SCREENS MOVIE SCREENING...
Fantastic news! We've Found the answer you've been seeking!
Question:
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.
Expert Answer:
Related Book For
Systems Analysis And Design
ISBN: 9781118897843
6th Edition
Authors: Alan Dennis, Barbara Haley Wixom, Roberta M. Roth
Posted Date:
Students also viewed these databases questions
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Emily Jackson (Social Security number 765-12-4326) and James Stewart (Social Security number 466-74-9932) are partners in a partnership that owns and operates a barber shop. The partnership's first...
-
Determine the range of the 2x function y = 3 sec 3
-
A computer costs $500 in the United States. The same model costs 550 euros in France. If purchasing power parity holds, what is the spot exchange rate between the euro and the dollar?
-
Elton Mayo, human relations movement. Identify the person or theory that helped develop the following six theory categories. A. Classical theory B. Behavioral theory C. Management science D. Systems...
-
Use the September transaction data for Otis Carpenter, M.D., P.C. Requirements 1. Open the following T-accounts: Cash, Accounts receivable, Medical supplies, Accounts payable, Common stock, Service...
-
Reservoir Bottling Company reported the following information at the end of the year. Total current assets are worth $237,513 at book value and $219,344 at market value. In addition, plant and...
-
Create a graph for y = f(x) which satisfies ALL the given properties: '(3) = '(3) = 0 The domain of f(x) is {x E R} and the domain of f'(x) is {x = R|x # 2} f'(x) < 0 if x = (-2,2) f'(x) > 0 if x < 3...
-
Sophia D. Corporation produces elegant gowns for Hollywood movie stars attending the Oscars and TIFF. The standard cost for each gown is as follows: Standard Cost per Gown Total Variable overhead 8...
-
2.Summarize the relationship between magnetism and current electricity. 7. What are magnetic domains? What are some different ways of representing magnetic domains? 4. What are some characteristics...
-
Robin Company has the following balances for the current month: Direct materials used Direct labor Sales salaries Indirect labor Production manager's salary Marketing costs Factory lease What is...
-
4. describe why "unification" is an important goal in science. 1.Discuss how magnets behave differently from electrostatically charged objects, for instance a balloon rubbed with fur. 11. How do...
-
Explain the differences between task context and interrupt context in an RTOS. How does an RTOS manage interrupt service routines to maintain real-time requirements ?
-
How does memory management in an RTOS differ from a general-purpose operating system? What techniques are used to ensure consistent memory allocation and prevent fragmentation?
-
What is required (it may be multiple steps) to complete the following reaction? HC 1) NaCN 2) CH31 1)Pyridine/heat 2) CH3CH3 1) LDA/THF 2) CH3CHI O 1) LiAlH4 2) HO CH3 H3C H H C-CH3
-
Give an example of transitory income. What effect does this income have on the marginal propensity to consume?
-
ComputerBuddies is a group that was formed in the Chicago metro area as an information exchange and social outlet for people with interests in computers. Members of ComputerBuddies range from...
-
Assume you are the project manager on a large systems development project team. Your team has been working on a major new system for your company. The project has progressed very well, and your team...
-
Cal Strong is a project manager for a systems development project involving a new database application for his organizations human resources department. Despite Cals best efforts at estimating the...
-
True or false? Explain: modularity reduces complexity because A. It reduces the effect of incommensurate scaling. B. It helps control propagation of effects.
-
True or false? Explain: hierarchy reduces complexity because A. It reduces the size of individual modules. B. It cuts down on the number of interconnections between elements. C. It assembles a number...
-
If one created a graph of personal friendships, one would have a hierarchy. True or false?
Study smarter with the SolutionInn App