submitted
BN204Database TechnologiesPage | 7 Assessment Details and Submission Guidelines Unit Code BN204 Unit Title Database Technologies Term, Year 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. b. 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) + 70 (for 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 2 - 4 - Submit 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 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. 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) - 4 Questions 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, 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. (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) b. The Hadoop framework includes many parts. Research more about following topics and describe briefly. (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 Query 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 Rubric 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 Moderated by: Dr. Sharly Halder March, 2019 Prepared by: Dr Deepani Guruge Moderated by: Dr Sharly J. Halder March, 2019