Small Business: CORNER VIDEO (CV)

A new neighborhood video store will open next week close to Adelphi University. The owner heard about our Database Management Systems course and approached you to quickly develop a small database for the new business. The brief description of this business follows.

The Corner Video (CV) rents videotapes (movies). To rent a movie, the customer has to become a member of CV. Membership is free, but each customer must hold a major credit card in his or her own name to be eligible to join.

The first time a customer comes into CV, he or she fills out a membership application form. The form contains the following information:

• First Name
• Last Name
• Date of Birth
• Street Address
• City
• State
• Zip code
• Phone number
• Major Credit card type
• Credit card number
• Driver's license number
• Age

The cashier fills out the form into the computer and creates a customer account on the spot. The computer assigns a sequential account number to the account. The cashier places a sticker with this number on a blank member card and types the new member's name onto the card. After the new member signs the card, the card is laminated and given to him or her. Now the customer may check out videotapes.

The checkout procedure is as follows. To check out a tape, the customer browses the shelves, which contain empty boxes for all the videos not checked out. The boxes contain the description of the movie. The customer selects the empty boxes and takes them to the checkout point along with the membership card. The cashier enters the member number into the computer and the customer record is pulled out. This also includes the balance on the customer's account. If there are any late charges, they must be paid before any additional tapes may be checked out. The cashier enters the transaction (for example, tape id., rental price, date rented). The customer pays, cashier records the payment and issues the receipt.

When the customer returns the tapes, they are placed in a return bin. At a convenient time, the cashier removes the tapes from the return bin and enters them into the computer system as returned so that the customer will not be charged for additional time. If the tapes are late, a late charge is determined and entered into the customer's account.

Write queries to answer the following questions:

• Find a DVD/s with a specific movie on it.
• Find the number of employees CV has.
• How often each DVD rented.
• List all customer from a specific city.
• List all payments in a specific period.
• List customers who owe some money.
• List payments by a specific customer.
• List purchases by a certain employee.
• List purchases from a certain supplier.
• List suppliers to which CV owes money.
• Number of purchases this month.
• Rental history for a specific customer.
• Requests by a specific customer.
• Total number of DVDs purchased.
• Total payments collected this year.
• Which movie was requested the most.

Normalized Table:

. . . X N16 A B C D E F G H K L M N 0 P Q R S Avoid MemberShip Redundency AccountNumberFirstName LastName DateOfBirth StreetName City State ZipCode PhoneNumberCrunchur Driver'sLicenseNumber site attribut multivalued attributes Derived attributes 3normal form Movie Actor Many to Many relationship 8 Movie10 Title Description Release Year Director Rating Length Tim RentalPrice Actorlo FirstName LastName 9 IM-1 $20 if the relationship is 1 to many we will need a foreign key in the 10 17-2 11 Movie Actor 12 Moviell Actorlo 13 Tape 14 LapelD MovielD TapeStatus 15 T-1 M-1 Yes 16 T-2 M-1 Yes 17 T-3 M-2 Yes Movie_Genre 18 Moviell Genre 19 Purchase EmployeelD FirstName LastName StreetName City State ZipCode PhoneNumberail PurchaselD SupplierIC Employeell OrderDat Quantity Pric Pag SupplierID SupplierName ContactFirstNar ContactLastNar StreetName City State ZipCode PhoneNumberail Purchase_Payment Paymentlo Purchase Payment Payment[ PaymentMod 35 BentallD AccountNumb TapelD EmployeelD RentalDate ExpectedReturnDa ReturnDat RentalFees LateFees 36 R- A-1 T- 2-Nov 5-Nov 6-Now $60 37 38 39 40 ayment Movie Actor 41 Payment RentallD Payment PaymentDate PaymentMode MV 42 43 44 Movie M M Genre 45 46 47 ard 48 Credit CardNum Paymentlo Movie 1-M Tape 49 50 51 CreditCard Sheet1 (+ 5:09 PM O W X 71.F Partly sunny A 12/5/2022