MS Access Database Queries And Normalization Are Essential For Essay Writing.

Creating the Database Structure on MS Access

1. The snapshot of  MovieDB  database structure is given below. MovieDB  is a database that keeps track of information about the movies, directors, and stars (i.e. actors and actresses) in a video store.

You are working as an IT specialist in this organisation and are required to extract information from this database by executing SQL queries according to the instructions given below.
The primary keys are marked with the key symbol in the following snapshot of MovieDB  database shown in figure 1.

Figure 1: Snapshot of  the MovieDB  database structure

Description of the schema
actor – stores details of the actors in the database MovieDB
director – stores details of the directors
movie – keeps track of movie details
genres – stores movie categories, such as action, horror, adventure, drama, romance, comedy

a. First you need to create the above database structure on MS Access and populate all the tables with suitable data (at least 3 records per table) using the following SQL statement or data sheet view in MS Access.
INSERT into TableName
VALUES (“..”,”..”,.....)

b. Write SQL queries (do not use QBE) for the following questions and execute the queries after creating the above database on MS Access.  Include screen shots of the outputs and all SQL statements you used to answer following questions

1. Display details of all movie titles released after 2017. Your result set should be sorted on descending order of the mov_title.
2. List titles of all horror movies.
3. Display movie title, year released of all movies and names of directors who directed them.
4. List titles of movies directed by “James Cameron”.
5. Create a new table named “JamesCameron_MOVIES” that includes titles of movies directed by “James Cameron”.
6. Find actors playing in  movies directed by “James Cameron”.
7. Assume that you want to count how many movies were released after 2017. Write a query to find the number.

2.a. Determine the Functional Dependencies that exist in the following Order table.

Order (OrderNum, OrderDate, Customer_No, Customer_Name, Customer_contactNo,        ItemNum, Description, NumOrdered, QuotedPrice)

b. Normalize the above relation to 3rd normal form, ensuring that the resulting relations are dependency-preserving and specify the primary keys in the normalized relations by underlining them.

c. An agency called WorkForce supplies part-time staff to hotels throughout Australia. The table shown below lists the time spent by agency staff working at two hotels.  The Emp_ID is unique for each employee.

Emp_ID	Contract_No	HoursAssigned PerWeek	Hotel_contact_No	HotelNo	HotelLocation
1177567	MIT1009	17	045566790	H12	Ringwood
1256788	MIT10010	20	056678954	H40	Melbourne CBD
2246769	MIT10011	30	045566790	H12	Ringwood
2254678	MIT10012	30	056678954	H40	Melbourne CBD
3758956	MIT10013	25	045566790	H12	Ringwood
4237890	MIT10014	36	056678954	H40	Melbourne CBD

Provide examples of insertion and deletion anomalies that may occur in the above table. 

3.a. A data warehouse (DW) is a collection of corporate information and data derived from operational systems and external data sources.  Research more about this topic and find out benefits of using a Data Warehouse for business.

b. Assume that you have been appointed as a database developer in an organisation. Explain briefly, what will be your key responsibilities in the role of a database developer.

c. Explore the job listings advertised online and find out the skills you need to become a database developer.

Creating the Database Structure on MS Access

a. Database and population

Relationships

Actor table

Director table

Movie

Movie_cast

Movie_director

Movie_genres

 

Question b

1. Display details of all movie titles released after 2017. Your result set should be sorted on descending order of the mov_title.

Query

SELECT *

FROM movie

WHERE mov_year>=2017

ORDER BY mov_title DESC;

Output

 2. List titles of all horror movies.

(Hint: Join movie and movie_genres and gen_title should be “horror”)

Query

select movie.* from movie

inner join movie_genres on movie.mov_id=movie_genres.mov_id

where movie_genres.gen_title='horror';

3. Display movie title, year released of all movies and names of directors who directed them.

Query

select movie.mov_title,movie.mov_year,director.dir_fname+" "+director.dir_lname AS director

from (( movie

inner join movie_direction on movie_direction.mov_id=movie.mov_id)

inner join director on director.dir_id=movie_direction.dir_id);

4. List titles of movies directed by “James Cameron”.

Query

select movie.mov_title

from (( movie

inner join movie_direction on movie_direction.mov_id=movie.mov_id)

inner join director on director.dir_id=movie_direction.dir_id)

where director.dir_fname='James' AND director.dir_lname='Cameron';

5. Create a new table named “JamesCameron_MOVIES” that includes titles of movies directed by “James Cameron”.

Query

select movie.mov_title INTO jamesCameron_MOVIES

from (( movie

inner join movie_direction on movie_direction.mov_id=movie.mov_id)

inner join director on director.dir_id=movie_direction.dir_id)

where director.dir_fname='James' AND director.dir_lname='Cameron';

 

6. Find actors playing in  movies directed by “James Cameron”.

Query

SELECT actor.act_fname+ " " + actor.act_lname AS actorname

from (((( movie

inner join movie_direction on movie_direction.mov_id=movie.mov_id)

inner join director on director.dir_id=movie_direction.dir_id)

inner join movie_cast on movie_cast.mov_id=movie.mov_id)

inner join actor on actor.act_id=movie_cast.act_id)

where director.dir_fname='James' AND director.dir_lname='Cameron';

7. Assume that you want to count how many movies were released after 2017. Write a query to find the number.

Query

select count(mov_id) FROM movie where mov_year>2017;

2.

a. Determine the Functional Dependencies that exist in the following Order table.

Order (OrderNum, OrderDate, Customer_No, Customer_Name, Customer_contactNo, ItemNum, Description, NumOrdered, QuotedPrice)

The following diagram shows the functional dependencies existing in the relation. These dependencies are;

OrderNum>orderDate,customerNO, customerName, customer_contactNO, itemNum, description, NumOrdered, quotedPrice

customerNo>customerName, customer_contactNO

itemNum> description, numOrdered, QuotedPrice

b Normalize the above relation to 3rd normal form, ensuring that the resulting relations are dependency-preserving and specify the primary keys in the normalized relations by underlining them.

Normalization of the relation to 3NF will result to the following relations;

1. Order (orderNum, orderDate,customerNo)
2. Customer (customerNO, customerName, customerContactNO)
3. Items (ItemNum, description)
4. OrderItems (orderNum, itemNum, NumOrdered, QuotedPrice)

c. Provide examples of insertion and deletion anomalies that may occur

Insertion anomaly

Since the Emp_ID is unique an insertion anomaly would occur if you tried to insert an employee who is already existing in the table. For example unique key constraint would be violated if Emp_ID 1177567 was inserted for another contract.

Deletion Anomaly

A deletion anomaly would happen if for example you wanted to delete an employee using the contract_NO. This would result to deleting all employees instead of one employee.

3.a. A data warehouse (DW) is a collection of corporate information and data derived from operational systems and external data sources.  Research more about this topic and find out benefits of using a Data Warehouse for business.

Currently, there is a lot of competition involved with various types of businesses. For companies or businesses to stay ahead in the market, companies are taking advantage of the large volumes of data generated by its operations internally or externally through its branches (TechAdvisory, 2015). Each branch or office generates a huge volume of data on a daily basis. This is data is aggregated to form a data warehouse. The data warehouse is then designed to conduct data analysis to get business insights from the data.

Data warehouses have many benefits to companies or businesses that implement them. These advantages are;

• Data warehouses help improve decision making in an organization- Due to the large volumes of data contained in data warehouses, analysis of the data can be very helpful for a business as decisions made are based on more concrete analysis from historical data (Data warehousing, 2011).
• Easy and quick way to access data- Using a data warehouse makes it easy for organizations to access data easily and quickly thus saves time for the organization.
• Data quality and consistency- One capability of data warehouses is to aggregate data from different sources into one data warehouse thus providing quality data and consistency in the data

By implementing a data warehouse a company is able to stay ahead of its competitors by getting insights from the large volumes of data.

b. Assume that you have been appointed as a database developer in an organisation. Explain briefly, what will be your key responsibilities in the role of a database developer.

The role of a database developer is to develop database and maintain databases in an organization. Developing a database involves modelling the database from a set of requirements and implementing it into tables to form a database.

Maintaining a database involves making regular backups and running queries and generating reports when need arises.

c. Explore the job listings advertised online and find out the skills you need to become a database developer.

The following skills are needed;

• SQL
• SQL performance testing and tuning
• Some basic experience in a server side language e.g php

References

[1]Data Warehousing, "Benefits of a data warehouse", BI-Insider.com, 2011. [Online]. Available: https://bi-insider.com/portfolio/benefits-of-a-data-warehouse/. [Accessed: 04- May- 2018].

[2]TechAdvisory, "Benefits of data warehouses for business", Techadvisory.org, 2015. [Online]. Available: https://www.techadvisory.org/2015/03/benefits-of-data-warehouses-for-business/. [Accessed: 04- May- 2018].