ISYS104-Introduction to Business Information Systems

Background Info

You are tasked to create a prototype database to keep track of movie related data and prepare all the necessary queries, a form and a report. Most of the requirements have been completed, but you will need to make a few informed decisions in preparing the tables, as noted below.

Task Descriptions

Your tasks in this assignment are as follows:

Task 1: Setting up Database

Note that anything with “???” means that you will need to assign an appropriate replacement.

1. Create a blank database called IMDB followed by your student ID number (e.g. IMDB12345678).

2. Create a table called ‘Director’ with the following fields/properties:

3. Import the other three tables (Movie, Actor and Acts) from the Excel data file provided and adjust the table properties as follow:

Assign a primary key/composite PK to each table, without adding another field.

4. Create the relationship between the four tables. Enforce referential integrity for each relationship created.

Basic Queries 

Q1: Display all actor’s names and gender. Display names sorted in reverse Alphabetical order (Z->A). Save the result as Query1.

Q2: Display all directors’ names, year of birth of those who have ‘n’ as the last character in their names and were born after 1960 (not including 1960). Save the result as Query2.

Q3: Display IDs of both actors and movies where actors made $4.2 million or less. Display the results with the lowest salary first. Save the result as Query

Intermediate Queries

Q4: Display all directors’ names who directed movies with ratings above 8.5 (including 8.5). Save the result as Query4.

Q5: Display the names of the movies and the average salary provided to the actors involved in the movie. Save the result as Query5. Hint: one salary per movie.

Q6: Display all movies names along with the director names that have female actors. Do not show the actor info. Save the result as Query6.

Q7: Display the names of actors (sorted alphabetically A->Z), and the total salary they have made. Only show the results with a total of more than $7.5 million (exclusive). Save the result as Query7.

Advanced Queries 

Q8: Display the names of the directors with the total number of movies they’ve directed. Show the directors with highest number of movies first. Save the result as Query8.

Q9: Display the actor names, movie names,salary, and a column thatshows an increase in salary (with 15% increase) without changing the actual salary. Save the result as Query9.

Q10: Create a query to permanently change the IMDB rating of ‘The Avengers’ to 8.8. Save the result as Query10.

Task 3: Form 

1. Using “Form Wizard”, create a form based on the Movie table without showing the DirectorID in columnar layout. Save this form as “Movie”.

2. Add a logo/image and title, then change the themes, colour scheme, and/or fonts.

3. Add another movie (any title) directed by James Cameron in a blank form.

4. Lock the MovieID text box. Hint: do not do this until no. 3 has been completed.

Task 4: Report

1. Using “Report Wizard”, create a report including the actor, director and movie name

2. View by Director.

3. Sort by the movie actor name alphabetically (A--> Z).

4. Use block layout in landscape orientation.

5. Use similar logo, themes/colour scheme/fonts. Save this report as “IMDB”.

Task 5: Navigation Form 

Create a navigation form with horizontal/vertical tabs that includes the following:

1. Movie form in the first tab.

2. IMDB report in the second tab.

3. A quit application button/text in the form header area.mUse similar logo, themes/colour scheme/fonts. Save this navigation form as “Menu”. Set it so that this navigation form is automatically open, when opening the database.