Managing Data in Databases -BN204 D

Click here to order this assignment @Essaywriting.us.No Plagiarism.Written from scratch by professional writers.

Managing Data in Databases 

Managing Data in Databases 

Assessment Details and Submission Guidelines
Unit Code BN204 D
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

“BN204D_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/aboutmit/institutepublications/policiesproceduresandguidelines/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.

 

Snapshot of  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)

 

  1. 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. Display details of all movie titles released after 2017. Your result set should be sorted on descending order of the

 (5 Marks)

 

  1. List titles of all horror movies.

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

(5 Marks)

 

  1. 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. List titles of movies directed by “James Cameron”

(10 Marks)

 

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

(5 Marks)

 

 

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

(5 Marks)

 

 

 

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

 

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

 

(5 Marks)

 

 

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

(5 Marks)

 

 

 

 

 

    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. Explore the job listings advertised online and find out the skills you need to become a database developer.

 (5 Marks)

 

 

 

 

 

Marking criteria:

Marks are allocated for each part as below.

  Section Description of the section Marks
QUIZ  (30 MARKS) 1.       Assignment 1 Quiz Issues related to integrity of database

 

30
REPORT

 

(70 MARKS)

2.       Question 1 Query writing Skills- Develop data models and implement DB systems. 50
3.       Question 2` Issues related to integrity of database 10
4.       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  an ability to think critically.

 

80%

Demonstrated reasonable   ability to think.

 

 

60%

Demonstrated   some ability to think critically but not complete.

 

40%

Did not demonstrate 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%