Microsoft access

DEPARTMENT OF ACCOUNTING, FINANCE & ECONOMICS

7150AFE Business Information Systems

MS ACCESS PROJECT (DATABASES) (TOTAL MARKS = 10)

This project requires you to demonstrate knowledge of databases using MS Access. It covers the following database concepts:

 Use of Primary and Foreign Keys

 Understanding and using cardinalities

 Understanding and defining relationships

 Developing queries using appropriate tools

 Creating forms for data entry

 Producing reports for use by management PREPARATION

Review database lectures (weeks 9 and 10) and workshop materials (weeks 10 to 13) and complete

any outstanding workshop activities prior to commencing with this project. Also review all

supplementary videos and/or documents (available via the Course Activity Centre).

PROJECT PART ONE (8 Marks)

Task Activity Mark

1 Download the VideoRental_student database file from L@G

Save your database file as lastname_student_id_project_3_videorental

(NOTE: Regularly SAVE your database as you complete individual tasks).

-

2 Familiarize yourself with the tables and data provided. The following

relationships apply to the Customers, VideoRentals and Movies tables:

 A customer can rent many movies

 A movie can be rented by many customers

(Take note of the one-to-many relationships.)

Using this information to establish appropriate relationships amongst the

Customers, VideoRentals and Movies tables. Ensure that you Enforce

0.5

Task Activity Mark

Referential Integrity for all relationships.

3 Create a query saved as Expired Credit Cards that shows the ID, Name,

Address, Phone Number, and Credit Card Expiration date for all customers

with credit cards expiring in January, February and March 2013. Sort by

month in ascending order. (Hint: cc expiration field is text).

0.5

4 Create a query saved as Best of the Best New Movies that shows the ID,

Title and IMDB Rating for all movies with an IMDB rating of 8.5 or higher,

and were released after 2009. Sort by Year in descending order.

0.5

5 Create a query saved as Worst of the Worst Movies that shows the ID,

Title and IMDB Rating for all movies with an IMDB rating less than 5. Sort

by Year in descending order.

0.5

6 Write a SQL query saved as January Rentals that shows the Rental ID,

Movie Title, Customer Name, and Checkout Date for all rentals that

occurred in January 2013.

1

7 Write a SQL query saved as Most Popular Rentals that shows Movie Title

and count of rental ids. Sort in descending order and show the top 25. 1

8 Write a SQL query saved as Heavy Renters that shows customer id,

customer name, and count of rental ids for customers that have rented

movies at least 10 or more times. Sort in descending order of count.

1

9 Create appropriate relationships amongst the Actors, Actor-Movies and

Movies tables. Ensure that you Enforce Referential Integrity for all

relationships. Note the one-to-many relationships.

 An actor can act in many movies

 A movie can have many actors

0.5

10 Research the use of the DateDiff function. Use the VideoRentals table to

create a form. Edit the form to add a new text field that calculates the

difference between Checkout_Date and Return_Date using the DateDiff

function.

1

11 Create a form for adding new customers into the Customers table. Add

yourself as a new customer (Note: use the first two initials of your name

and a four digit number, e.g. AB1234, as the customer id).

0.5

Task Activity Mark

12 Create a report titled Best of the Best Movies and Actors. The report will

include the Movie Title, Movie Year and list of Actor Names. Add Movie

Title as a Grouping Level. (Hint: write a query based on the relationships

created in task 9 above). Ensure you format the report appropriately and

use descriptive names for the report title and headers.

1

13 Save your database file as lastname_student_id_project_3_videorental

PROJECT PART TWO (2 Marks)

“Big Data” is a new buzz-word in the field of data collection, processing and management. Write a

300-350 word report that defines and discusses the issues surrounding the collection, processing

and management of “Big Data”. Use simple language. Copying sections from online sources will

result in you receiving 0 for this task.

(Reference the report appropriately. References are not part of the word count. At least three

references are required.)

Create a PDF file of thee report. Name it lastname_student_id_project_3_report

SUBMISSION REQUIREMENTS

SUBMIT

1. Copy of MS Access database (accdb file).

2. Copy of answer to Part 2 (PDF file).

3. Assignment cover sheet (saved as lastname_student_id_project_3_cover).

END OF PROJECT