The purpose of this assignment is to develop skills

Assessment Details and Submission Guidelines
Unit Code	BN204
Unit Title	Database Technologies
Term, Year	T1, 2018
Assessment Type	Assignment 1
Assessment Title	Managing Data in Databases
Purpose of the assessment (with ULO Mapping)	The purpose of this assignment is to develop skills in managing data in databases and to gain understanding of data model development and implementation using a commercially available database management system development tool. On completion of this assignment students will be able to: a. Describe issues related to the integrity and security of database (DB) systems. b. Make an informed and critical assessment of database management systems (DBMS). c. Develop data models and implement DB systems.
Weight	70 Marks + 30 Marks for the quiz
Total Marks	15% of total assessment for the unit
Word limit	No specific word limit
Due Date	Lab class week 8
Description of this assignment:	This assignment consists of two parts: Part A: An online quiz Assignment 1, which includes 30 questions. Quiz will cover basic concepts of the relational data models. Students may have 3 attempts at this quiz. The system will keep the highest grade. Part B: Write SQL Queries to extract data from data tables and 2 questions on database concepts.
Submission Guidelines	All work must be submitted on Moodle by the due date. Write your answers in this document underneath the question and save as “BN204_T1_2018_Assigment1_your_name.doc”. The assignment must be in MS Word format, 1.5 spacing, 11-pt Calibri (Body) font and 2.5 cm margins on all four sides of your page with appropriate section headings. Reference sources must be cited in the text of the report, and listed appropriately at the end in a reference list using IEEE referencing style.
Extension	If an extension of time to submit work is required, a Special Consideration Application must be submitted directly to the School’s Administration Officer, in Melbourne on Level 6 or in Sydney on Level 7. You must submit this application three working days prior to the due date of the assignment. Further information is available at: http://www.mit.edu.au/about–mit/institute–publications/policies–procedures–and–guidelines/specialconsiderationdeferment
Academic Misconduct	Academic Misconduct is a serious offence. Depending on the seriousness of the case, penalties can vary from a written warning or zero marks to exclusion from the course or rescinding the degree. Students should make themselves familiar with the full policy and procedure available at: http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/Plagiarism-Academic-Misconduct-Policy-Procedure. For further information, please refer to the Academic Integrity Section in your Unit Description.

Assignment Description

Answer All Questions (Part A and Part B)

Part A: (30 Marks)

Paste below the summary of your Moodle Assignment 1 quiz. The quiz will be open after week 5

Part B: (70 Marks)

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

1. 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 (“..”,”..”,…..)

(10 Marks)

2. 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

(3 marks for each screen shot & remaining marks for the SQL query)

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

(5 Marks)

1. 2. List titles of all horror movies.

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

(5 Marks)

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

(Hint: you need to join 3 tables; movie, director, movie_direction tables)

(10 Marks)

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

(10 Marks)

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

(5 Marks)

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

(3 Marks)

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

(2 Marks)

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

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

(5 Marks)

1. 2. 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.

(3 Marks)

1. 3. 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.

(2 Marks)

3. 1. 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.

(5 Marks)

1. 2. 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.

(2 Marks)

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

(3 Marks)

Marking criteria:

Marks are allocated for each part as below.

	Section	Description of the section	Marks
QUIZ (30 MARKS)	Assignment 1 Quiz	Issues related to integrity of database	30
REPORT (70 MARKS)	Question 1	Query writing Skills- Develop data models and implement DB systems.	50
	Question 2`	Issues related to integrity of database	10
	Question 3	Make an informed and critical assessment of database management systems (DBMS)	10
		Total marks for the Report	70
		TOTAL	100

Marking Rubric for Assignment 1 –Part B: Total Marks 70

Grade/ Mark	Excellent 100%	Very Good 80%	Good 60%	Satisfactory 40%	Unsatisfactory 0%-20%
Q1 Query writing Skills 50 marks	Evidence of accurate and well- written queries 100%	Evidence of good query writing skills. 80%	Generally relevant. 60%	Displayed reasonable query writing skills. 40%	Demonstrated little evidence of understanding the topic. 0-20%
Q2 Issues related to integrity of database 10 marks	Demonstrated excellent ability to think critically. 100%	Demonstrated the ability to think critically. 80%	Demonstrated reasonable ability to think. 60%	Demonstrated some ability to think critically but not complete. 40%	Did not demonstrate the ability to think critically. 0-20%.
Q3 Make an informed and critical assessment of database management systems concepts(DBMS) 10 marks	Demonstrated excellent knowledge on database management systems concepts and applications. 100%	Demonstrated good knowledge on database management systems concepts and applications. 80%	Demonstrated reasonable knowledge on database management systems concepts and applications. 60%	Demonstrated some knowledge on database management systems concepts and applications. 40%	Did not demonstrate knowledge on database management systems concepts and applications. 0-20%

• • Assignment status: Resolved by our Writing Team
  • Source@PrimeWritersBay.com

GET THIS PAPER COMPLETED FOR YOU FROM THE WRITING EXPERTS  CLICK HERE TO ORDER 100% ORIGINAL PAPERS AT PrimeWritersBay.com

• • • • NO PLAGIARISM