Question
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
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:
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/07/66913ddbce014_58766913ddbb0dfa.jpg)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started