Scribd
Upload
18 views2 pages

Rent-A-Movie Database Design Guide

Uploaded by

edjudge123uk
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
18 views2 pages

Rent-A-Movie Database Design Guide

Uploaded by

edjudge123uk
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd

RENT-A-MOVIE DATABASE PROJECT

The Rent-A-Movie video rental store wants to design a relational database to store
information about their customers and their videotapes.

They hope the database will improve their ability to market to and provide enhanced service
to their customers. Among the things they would like to be able to do, once the database is
up and running are:

 Search for customers by last name or phone number


 When entering new customers, enter a zip code and the database will find the city and
state
 Keep track of which movies their customers rented, on what dates, and how much
they spent on each date and in total
 Be able to send letters to their customers, addressing them by first name
 Search for movies by movie name or type of movie
 Search for movies that have a certain actor (note that some movies will have more
than one actor worth searching on)

Note: You are NOT expected to demonstrate all of these features; simply make sure your
database is designed so that these features would be possible.

Some things you might need to know are:

 Movies rent for $3-$5 each, plus a 10% entertainment tax


 The possible types of movies are “Comedy”, “Drama”, “Family”, “Horror”, “SciFi”,
and “Romance”

Guidelines:

 Make sure all tables have a primary key that will be unique
 Avoid many-to-many relationships between tables
 No two students’ assignment hand-ins should be identical (or even very similar)

YOUR ASSIGNMENT (Deliverables are underlined):

 Determine the schema (logical structure and relationship of the data) for your
database (This is the most important step; do this correctly and the rest should be
easy.)
 Create a database using Microsoft Access
 Create the appropriate tables and keys
o Don’t add any fields you don’t need
 Create the appropriate table relationships, enforcing Referential Integrity
o Save and print the relationships (cut and paste or draw them if you are using an
older version of Microsoft Access)
 Add records (feel free to be creative), including at least:
o 5 different customers
o 10 different videos (you might want to use the newspaper)
 Specify a variety of prices and movie types
 Associate more than one actor with some of the movies
o 15 rental transactions
 At least 5 of them should include more than one movie
 Every customer has at least 2 transactions
 Specify a variety of movies and customers

 Create and print results from the following queries, including at least the indicated
fields:

o Customer List: ID, Phone Number, Customer Name, Street Address, City,
State, Zip
 No duplicate rows (a duplicate row is one where ALL the same data
appears in another row)
 Sorted in ascending order by Customer’s Last Name
o Transaction List: Date, Customer Name, Video Name, Price, Tax, Total Price
(for each movie rented). Format each field appropriately.
 No duplicate rows (a duplicate row is one where ALL the same data
appears in another row)
 Sorted by Customer’s Last Name and, within each customer, by Date
with the most recent rental appearing first

 Use your Transaction List query to create and print the following report using the
Report Wizard:
o Each transaction, showing Customer Name, Date, Video Name, and Total Price
(including tax)
o Group by Customer and then by Date
o Separate Customers with horizontal lines
o Within each transaction date, sort by video name
o Show the total amount that each Customer has spent at Rent-A-Movie on each
date and overall
o Format each field appropriately
o Use Portrait view and fit on no more than 2-3 pages.

 Hand in all your printouts with a cover sheet, indicating your name and 5-digit ID
number. Staple everything together with one staple. No folder covers, please.

 To earn maximum points, you must conform to all instructions, requirements, and
guidelines and the printouts must be in a form that would be acceptable to a client.

Common questions

Powered by AI

To resolve many-to-many relationships, the project guide recommends avoiding them by ensuring that all tables have a primary key. This suggests using intermediary tables to separate entities where many-to-many relationships might naturally occur, effectively normalizing the data .

The project requires creating queries for a Customer List and a Transaction List. The Customer List should include ID, phone number, customer name, street address, city, state, zip code, and be sorted by customer's last name without duplicates. The Transaction List should include date, customer name, video name, price, tax, and total price, sorted by customer's last name and date .

The guidelines suggest enforcing referential integrity within the table relationships in the database. This involves creating appropriate table relationships and ensuring that primary and foreign keys are used correctly to maintain the integrity of the data .

The printouts must include all database elements such as tables, relationships, queries, and reports. They should be presented with a cover sheet with the student's name and ID, and all documentation should be neatly stapled together, without using folder covers .

The guidelines specify that movies rent for $3-$5 each, plus a 10% entertainment tax. This information should be used to calculate the total price for each movie rental in the database, including these details in reports and transactions .

The database should include features such as searching for customers by last name or phone number, entering a zip code to find the city and state, keeping track of rented movies along with dates and total spending, sending personalized letters addressing customers by their first name, and searching for movies by name, type, or actor. These features aim to improve the store's marketing and service offerings .

The database should include tables that allow for actor information to be associated with movies. This likely involves creating an intermediary table linking actors and movies to facilitate searches by actor, considering that a movie can have multiple search-worthy actors .

Creating the schema involves determining the logical structure and relationships of the data, which is crucial as it sets the foundation for the rest of the database design. Without a correct schema, building a functional and efficient database aligned with project goals would be challenging .

Avoiding duplicate rows ensures that reports and data outputs are accurate and concise, reducing redundancy and improving data integrity. This is important for reliable analysis and decision-making, as well as for maintaining a streamlined database .

Transaction data should be organized by customer and date, including fields such as customer name, date, video name, and total price (including tax). Reports should group transactions by customer, then by date, and sort video name within each date. Additionally, the total amount spent by each customer on each date and overall should be displayed, ensuring it fits on 2-3 pages in portrait view .

You might also like