The next query will find out what action movies there are for a 12-year-old-boy.
The picture below shows a screen print of the design view of this query:
The results of running this query look like this:
The next query will be based on the question what the best action movie with Jet Li is being in stock. To find out this I will use the following query:
This query will show the best movie with the highest rating on top, which are in stock, and so I only need to look at the top film to find the answer. It is the movie ‘Fearless’.
Now I am going to sort my database. I will sort the field title in ascending order:
The next screen print shows how the database looks like when sorted by the data field ‘Running Time’ in descending order:
Here I am going to edit my database. This is the database before changes were made:
Now I am going to edit my database. I will highlight the changes with these boxes:
I changed some of the data in the rows that are about the films Romeo Must Die and Spiderman 3.
I made some mistakes in my database that I which to correct now. I will mark these mistakes on the following paper with these ovals:
I made three mistakes, which I will need to correct. The screen print on the following page shows the corrected table.
On the following page I will design a data capture form in order to collect some data about films. The next page shows the form. The five pages thereafter show how these forms can be filled out with information based on some films that also occur in my database.
Now I will set up some validation checks on the two data fields certificate and genre.
The following screen print shows me setting a validation check up in the design view of my database. This validation rule is for the data field genre:
On the next page I will present the other validation rule I set up.
In this screen print I will show the validation rule I set up to validate typed in data for the data field certificate:
In the following two screen prints I will demonstrate how the data I type in is validated by typing in unsuitable data. The following screen print is a result of typing in the word ‘mixture’ in the data field genre for a film:
The next error message will demonstrate the result of typing in ‘xy’ in the data field certificate:
There are two more questions that could have been asked by customers, which are harder to answer. I will have to deal with them:
- I want to buy a movie but I have forgotten its name. I am only able to recall some features. Can you tell me a movie that has not the letter ‘x’ in its title, but the letter ‘e’, and came out between the years 1999 and 2006 with the genre action? When I see the movie in the list I might remember it.
- I need to find particular movies for my Media Studies class. I want movies with a rating between 6 and 10 that have either the genre action and were produced in the year 2006, or movies with genre science fiction or comedy that were produced between the years 1998 and 2004.
These requests are very specific, and need complex queries. The following screen print shows a query in the design view that is needed to answer the first question. It is on the next page.
The films that have these particular features are shown in the screen print below:
For the second request I need too fill out two rows with criteria, since the criteria are fully independent in two possibilities. Both need to be entered separately in these two rows. The screen print below shows the query in the design view:
The following screen print shows the result of this query:
The software Microsoft Access is quite suitable for this task, because it specialises on the sorting and filtering of data. The other program that I could have used is Microsoft Excel. This program specialises more on the use of mathematical formulas, and there many features which are simple unnecessary for this task, while the features that are really needed are missing. One thing that makes Excel partly suitable for this task is the typed in data enabled of being in a variety of formats. E.g. text, numbers, currency, memo and date/time. But Access is capable of the same.
The following features of Access are significant for this task, which are not features of Excel or are very complicated to set up in Microsoft Excel:
- Complex queries( using and, or, and not criteria)
- Ascending and descending sorting (special mechanism is needed to do this in Excel.)
- Validation Checks
- Filtering of particular data
- Using logical (Yes\No) formats for data
Besides, Excel is more focused on handling of numeric data. Access is stronger in handling strings, which is the main format needed for this task.
In the following table I will list all the data fields I have used and its features:
Now I will explain my choice of fields:
- Title – This field is necessary to identify each movie. The video shop can also run a search on the film name if it is given by the customer and find some details about this movie.
- Genre – The genre helps to identify the type of movie. If someone asks for a certain type of movie, you can run a search on the genre and see what movies there are of this genre that match the needs of this customer.
- Certificate – This is very important, because it will tell you what ages of audience the movie is not suitable for. If the certificate is 18 because, it might contain some adult material e.g. Violence, bad language, nudity etc. and should not be watched by a minor. Having this field would make sure you don’t supply an adult film to a child. It also helps to narrow down the search if a customer asks for a particular range of certificates.
- Running Time – This is used to show the length of the movie in minutes. It helps the customer to know how much time of watching the movie they can get in return for the money. Generally, customers are more willing to spent money on a movie with longer running time. Customers could ask for a long running time, so this can be searched for.
- Year – This tells when a movie was first released. Sometimes a customer might like to see a movie from an earlier age. Other customers might like new movies. So a customer can tell a certain year range in order to narrow down the search.
- Lead Star – This field is used to find out who played the main role in a movie. Nowadays this appears to be very important, as customers connect a good image of an actor to the performance of the movie. So by telling the favourite actor of a movie, a customer can specify the search a lot more.
- In Stock – This ensures that the film which is searched for is available at the store. If the customer says that he\she wants to take the searched movie directly home and is not willing to wait until it is in stock again, it can be used to narrow down the search.
- Country – This could be important as the customer might prefer movie from his\her native country. By telling what movies are preferred (e.g. Indian), the customer can help to specify the search. Instead of U.S.A, I will write America, since it is very common to say America instead of U.S.A., which is done by most of the customers.
- Rating – The rating can tell how much a film is liked or disliked by an audience. It is a whole number from 1 to 10 with ten being the highest. If a customer wants to buy a good movie, but is not very specific, the member of staff can search for all movies with a fairly high rating (E.g. 9 and 10).
Now I will explain why I used these specific features for every data field:
- Title: I used the data type text, since a title contains always some letters and possibly some numbers. The field size will be 30, since it is very unlikely that a film name has more than 30 characters.
- Genre: The data type will be text, since a genre always consists of letters. No genre has more than 25 characters, so the field size will be 25.
- Certificate: Every certificate consists of one or two characters, so I will use the field size two. These characters can be letters or numbers, so the data type will be text.
- Running Time: The running time will always consist of numbers, so I used the data type numeric. It is always rounded to the nearest minute, so the field size will be an integer.
- Year: The year is also always a number and thus I will use the data type numeric. It is always an integer and so I need to use the field size integer.
- Lead Star: a name of a human will always consist of letters, so I need to use the data type text. It is unlikely that someone has a name that is longer than 15 characters, so that the field size will be 15.
- In Stock: There are always two possibilities for this data type – Yes or No. A tick represents this quite well, so I will use the data type logical. No specific field size is needed for this data type.
- Country: The name of a country will always consist of letters, so I need to use the data type text. I used the field size 15, since it is very unlikely that the name of a country will consist of more than 15 characters.
- The rating of a film will always consist of a number from 1 to 10, so that the data type numeric is suitable. The field size is an integer, since a rating is always a whole number.
The whole project was quite easy to produce thanks to my experience with Microsoft Access.
The main things that I have done are:
- Setting up a database and entering the data
- Setting up complex and simple queries
- Sorting (ascending and descending)
- Validation checks
To set up the database I simply opened Microsoft access and created a new table which I called video database. After that the design view occurred. Here I could set up the settings for the various data fields. After that I began to type in the data.
The data I typed in came from two different sources. The first source was the example data provided by my teacher. The second source was the films I had at home. I used the DVD-sleeves of them to obtain the data.
To set up queries I selected the object queries in the first window that came up when I opened Microsoft Access. Then I selected the option ‘create query in design view’. When I had to design complex queries, I could rely on my experience for complex queries. If you understand how the computer works, it is fairly easy to set them up. After that, the design view occurred in which I set up the various settings of the query. At the end I only needed to run the query and I had the results.
The next main thing that I had to do was sorting. In order to do this, I simple used the right button of my mouse to select the box above one column and either selected the option sort descending for descending sorting or the option sort ascending for ascending sorting. Even if nobody had showed me this I could have quested that sort ascending would mean that the whole table will be sorted from A-Z. From my point of view Access is very user-friendly.
In order to set up validation rules I changed the whole table back to design view. And here is the reason why I find Access very user-friendly:
After that I simply typed in the validation rule in a format that Access understood.
The statement in blue that I have marked with a box gives a clear and brief description of every field. This was all what I needed to find out how I will have to set up validation rules. It was very useful. And if I do get stuck somewhere, I am able to use the help:
This software enabled me to produce this project without getting stuck somewhere.
Now I will use Excel in order to present the ratings of all movies in the database using a graph.
First I copied the whole table from Microsoft Access to Microsoft Excel:
The next thing that I did was selecting chart wizard.
After that I followed the instructions and selected the columns title and rating as my data range. The screen print showing this is on the next page.
After that I followed the last steps and than I clicked finish. Then the graph appeared:
This graph is very useful as it clearly shows the ratings of the films compared to each other. E.g. a customer asks for the best movie in the database. By simply looking at the graph I can tell that the movie Matrix has the highest rating and this would be the answer to the customer’s question. Or if another customer asks which movies have a rating of seven I can simply look at the graph and answer this. These movies are Spiderman 2, Unleashed, The Fast and the Furious 3, X-Men: The Last Stand and Romeo Must Die. These are the advantages of a graph as it is very easy to find information about a film being different from the others. Another advantage is also that information looks clearer and better when presented in that way. I would prefer to look at a graph rather than numbers, as it is easier.
I found it quite easy to do this graph, as the program leaded me step by step with instructions that were easy to understand. I also had some experience with Excel, so overall I had no problems with it.
