Create a Microsoft Access document

You will be generating a database for a video rental store. You will be required to create an Access database containing tables CUSTOMER, RENTS and MOVIE, using the attributes shown in the list of relations and establish Primary Keys for each table. One record in the CUSTOMER Table must contain your name, last namc and 0587795 inputted as the “customer number”. You are free to choose your own data for the movie and customer entries. You must satisfy the requirements below: Create a database and save it “[login to view URL]” You need to enter at least 10 customers and 20 movies. • At least 5 of the movies need to be rented (note: to simplify the database, you will only have 1 copy of each movie to rent) • To simplify the database, duedate on your tables will be either 31/12/2014 for overdue or 31/12/2015 for not overdue The tables must have the following columns. Note the ASIN is the “Amazon Standard Identification Number” which you can look up on [login to view URL] in the product details section; you can also use this as a method of obtaining movie information for your database. The StarRate will be the number of stars the movie received (5 stars=excellent, 0 stars = very poor), you can make this information up according to your preference. CUSTOMER (CustomerNumber, Lastname, Firstname, Address, City, Province, PostalCode, Phone) RENTS (CustomerNumber, ASIN, Duedate) MOVIE (ASIN, Movie Title, StarRate) NOTE sizes of each field: Customer Number - 7 Lastname - 25 Firstname - 25 Page 5 Address - 50 City - 20 Province - 2 Postal Code – format with input mask Phone – format with input mask Duedate - (ShortDate Format) ASIN - 10 Title – 40 Star Rate – (Number) Part B: Relationships, Forms and Queries • Create the appropriate Relationships among the tables Enforcing Referential Integrity (it’s a good idea to try and do this before you start entering all the records required) • Create a Validation Rule for the Province Field – the user may only type the two letter abbreviations for Canadian provinces and territories. The validation text should be “Only Canadian two letter abbreviations” • Create a Query called "Overdue Movies Query" where the Customer's last name and first name are used along with the due date and movie title. The criteria for an overdue movie should be due before 31/12/2015. The user should just click on your "Overdue Movies" query and get the result of all movies that are overdue. • Create a Query called "Renting Customer Query" where the Customer's last name and first name are used along with the due date and movie title. The user should just click on this Query and get the result of all customers last and first names who are currently renting a movie along with due dates and movie titles. • Create a Query called "Specific Movie Query" • Create a Query called "Specific Movie Query" where you will query one movie in your database; the choice of title is up to you. • Create a Query called “Star Rate Query” Where the movie title and StarRate are selected fields and Star Rate is displayed is descending order. • Create three Split Forms, one for each of the tables you have created. Use the table name and ending with Split Form to title the form. • Create a Report based on the items in your StarRate Query. • Create a Report that shows all the customers (name and numbers) that have currently rented a movie. • Create a Report that shows all the movies that are currently overdue and customer names.