BN204 Database Technologies Page | 7
Assessment Details and Submission Guidelines
Unit Code
BN204
Unit Title
Database Technologies
Term, Yea
T1, 2019
Assessment Type
Assignment 1
Assessment Title
Developing and Managing 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.
. Make an informed and critical assessment of database management systems (DBMS).
c. Develop data models and implement DB systems.
Weight
100 = 30 (for online quiz- Part A XXXXXXXXXXfor Part B)
Total Marks
15% of total assessment for the unit
Word limit
No specific word limit
Due Date
This assignment consists of two parts due dates are given below:
Part A: Week 6 -An online quiz test - 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:
1. Question 1- Submit in Week 4/5 - Develop the database in week 4/ 5 lab class and upload the database.
2. Questions XXXXXXXXXXSubmit Week 8 –Saturday, 11th May 2019 before 5:00 PM
· Question 2 - Write SQL Queries to extract data from data in the database
· Question 3 - issues related to the integrity and security of database.
· Question 4 - a research question on database concepts.
Submission Guidelines
· Submit the database on Moodle in Week 4/5 during the laboratory classes.
· Quiz test Week 6
· Remaining work must be submitted on Moodle by Week 8. Write your answers in this document underneath the question and save as
“BN204_T1_2019_Assigment1_your_name.doc”.
· The assignment must be in MS Word format, 1.5 spacing, 11-pt Cali
i (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.
1.
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 only during the week 6 laboratory classes.
Part B: (70 Marks XXXXXXXXXXQuestions
1. The snapshot of HotelBookingDB database structure is given below. HotelBookingDB is a database that keeps track of information about the Hotels, Rooms, Bookings, Guests, Payments and Staff.
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 in the following snapshot of HotelBookingDB database (in figure 1).
Figure 1: Snapshot of HotelBookingDB database structure © Database Answers Ltd. 2016
Create Database –In Week 4 or Week 5 Lab classes
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) during the week 4 or week5 lab class.
NB: You only have to create 5 tables for Hotels, Rooms, Bookings, Guests and Payments. You do not have to create other tables in the ER.
You need to upload your database file on the submission link in week 4 or week 5 during the lab class and 15 marks will be deducted as per assignment ‘Marking Criteria’ for not submitting the database file. This is an individual assignment; it should be your own individual work (You should not copy Ms Access Database). If not, this is considered as cheating and you will get zero marks for the whole assignment.
You can use INSERT INTO SQL statement (as given below) to enter suitable data records.
INSERT into TableName
VALUES (“..”,”..”,.....)
Or datasheet view in MS Access. (15 Marks)
2. Write SQL queries
Write SQL queries (do not use QBE) for the following questions and execute the queries on the above database (created on MS Access). Include screen shots of the outputs and write 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 Hotels registered in the database. Your result set should be sorted on descending order of the Hotel_name. (5 Marks)
2. Customer need to list Hotel_Name, Hotel_Address, Room_number, Foor_Number, Room_rate details of all rooms with Room_rates between $500 and $1000.
Hint: Here you need to join two tables Hotels and Rooms. (5 Marks)
3. Customer need to find out the Hotel_Name, Hotel_address and number of rooms in each hotel. (Hint: You need to Join Hotel and Rooms tables and then use Group by command) (10 Marks)
4. Staff at “HotelBooking” needs to prepare a report of all hotel booking starts from (consider Date_from in Bookings table) 20th June 2019. In this report she needs to print Hotel_Name, Hotel_address, Guest_ID, Room_Number, Room_Rate and Staff_ID. She also need to sort this report based on the staff ID to find out the status of the booking. Write a SQL query to create this listing.
(Hint: Join Hotel, Rooms and Booking tables) (10 Marks)
5. Calculate the total payments made on 3rd March 2019. (5 Marks)
3. Determine the Functional Dependencies
a. Determine the Functional Dependencies that exist in the following Orders table.
This table lists customer and order data. (5 Marks)
Orders (OrderNum, OrderDate, Customer_No, Customer_Name, Customer_contactNo, XXXXXXXXXXItemNum, Description, NumOrdered, QuotedPrice)
. 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)
4. Research Question
The relational database model has been dominant for decades. Social networking and Big Data organizations such as Facebook, Yahoo, Google, and Amazon were among the first to decide that relational databases were not good solutions for the volumes and types of data that they were dealing with, hence the Hadoop file system was developed. One of the key capabilities of a Hadoop type environment is the ability to dynamically, or at least easily, expands the number of servers being used for data storage.
Big Data requires a different approach to distributed data storage that is designed for large-scale clusters. Although other implementation technologies are possible, Hadoop has become the de facto standard for most Big Data storage and processing.
a. Explain how “big data” is different from the traditional relational data. (5 Marks)
. The Hadoop framework includes many parts. Research more about following topics and describe
iefly. (5 Marks)
1. HDFS
2. MapReduce
Marking criteria
Marks are allocated for each part as below.
Section
Description of the section
Marks
PART A
QUIZ
Assignment 1 Quiz
Issues related to integrity of database
Week 6
30
PART B
DATABASE
Question 1
Model building
Upload your database on submission link in Week 4 or Week 5.
15
REPORT
(55 MARKS)
Question 2
XXXXXXXXXXQuery writing
Query writing Skills- Develop data models and implement DB systems.
35
1.
2.
Question 3
Issues related to integrity of database
10
3.
4.
Question 4
Make an informed and critical assessment of database management systems
10
Total marks for the Report
55
TOTAL
100
Marking Ru
ic for Assignment 1 –Total Marks 100
Grade
Mark
Excellent
100%
Very Good
80%
Good
60%
Satisfactory
40%
Unsatisfactory
0%-20%
PART A
Quiz
(30 marks)
Demonstrated excellent ability to think critically.
Demonstrated ability to think critically.
Demonstrated reasonable ability to think.
Demonstrated some ability to think critically but not complete.
Did not demonstrate ability to think critically.
PART B
Q1
Model building skills
(15 marks)
Demonstrated excellent model building ability.
Demonstrated model building ability.
Demonstrated reasonable model building ability.
Demonstrated some model building ability but not complete.
Did not demonstrate the model building ability.
PART B
Q2
Query Writing
Skills
(35 marks)
Evidence of accurate and well-
written queries
Evidence of good query writing skills.
Generally relevant.
Demonstrated reasonable query writing skills.
Did not demonstrate little evidence of understanding the topic /copying
PART B
Q3
Issues related to integrity of database
(10 marks)
Demonstrated excellent ability to think critically.
Demonstrated ability to think critically.
Demonstrated reasonable ability to think.
Demonstrated some ability to think critically but not complete.
Did not demonstrate the ability to think critically.
PART B
Q4
Make an informed and critical assessment of database management systems concepts (DBMS)
(10 marks)
Demonstrated excellent knowledge of database management systems concepts, applications and new trends
Demonstrated good knowledge of database management systems concepts, applications and new trends.
Demonstrated reasonable knowledge of database management systems concepts and applications.
Demonstrated some knowledge of database management systems concepts and applications.
Did not demonstrate knowledge of database management systems concepts and applications.
Prepared by: Dr. Deepani Guruge XXXXXXXXXXModerated by: Dr. Sharly Halder March, 2019
XXXXXXXXXXPrepared by: Dr Deepani Guruge Moderated by: Dr Sharly J. Halder March, 2019