New

Learn smart - Learn online. Upto 88% off on courses for a limited time. View Courses

Securing Higher Grades Costing Your Pocket? Book Your Assignment at The Lowest Price Now!
loader
Add File

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!

BN204 Database Technologies

tag 0 Download 5 Pages / 1,013 Words tag 12-11-2020

Questions:

 
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.

Download Sample Now

Earn back the money you have spent on the downloaded sample by uploading a unique assignment/study material/research material you have. After we assess the authenticity of the uploaded content, you will get 100% money back in your wallet within 7 days.

Upload
Unique Document

Document
Under Evaluation

Get Money
into Your Wallet

Total 5 pages

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help (2020) BN204 Database Technologies [Online]. Available from: https://myassignmenthelp.com/free-samples/bn204-database-technologies/responsibilities-in-the-role-of-a-database-developer.html
[Accessed 10 August 2022].

My Assignment Help. 'BN204 Database Technologies' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/bn204-database-technologies/responsibilities-in-the-role-of-a-database-developer.html> accessed 10 August 2022.

My Assignment Help. BN204 Database Technologies [Internet]. My Assignment Help. 2020 [cited 10 August 2022]. Available from: https://myassignmenthelp.com/free-samples/bn204-database-technologies/responsibilities-in-the-role-of-a-database-developer.html.


We Can Help!

Get top notch assistance from our best tutors !
Excel in your academics & career in one easy click!

icon

Other Samples

Content Removal Request

If you are the original writer of this content and no longer wish to have your work published on Myassignmenthelp.com then please raise the content removal request.

icon

5% Cashback

On APP - grab it while it lasts!

Download app now (or) Scan the QR code

*Offer eligible for first 3 orders ordered through app!

screener
ribbon
callback request mobile
Have any Query?