MN405 Data and Information Management Assignment

Assignment Help Database Management System

Reference no: EM132498905

MN405 Data and Information Management Assignment - Melbourne Institute of Technology, Australia

Assessment Title - Data Modelling and Data Managing

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. Model organisational information requirements using conceptual data modelling techniques.

b. Convert the conceptual data models into relational data model and verify their structural characteristics with normalisation techniques.

Assignment Description -

Part A: Data Modelling

Question A1 - Create the Database

The snapshot of Party_BookingDB database structure is given below. Party_BookingDB is a database that keeps track of information about the Bookings, Staff, Menus and Customers registered in the system.

Assume that, you are working as an IT specialist in an organisation and are required to extract information from this database by building the database and executing SQL queries according to the instructions given below.

The primary keys and foreign keys are marked and entered in the database structure as shown in Figure1 (attached).

a. First, you need to create the above database in MS Access. Create only 5 tables.

b. Populate those tables with suitable data (at least 3 records per table).

i. You can use Datasheet view in MS Access or SQL statement (as given below) to enter suitable data records.

INSERT into TableName

VALUES ("..","..",.....)

ii. Include Proper foreign keys to create relationships in between tables.

Question A2 - Write SQL queries -basic skills

Write SQL queries for the following questions.

Execute the following queries on the "Party_BookingDB" database you created in MS Access. Include screen shots of the outputs and all SQL statements you used to answer following questions:

a. Display details of all Customers recorded in the database. Your result set should be sorted on ascending order of the Customer_id.

b. Assume you need to find details of the Bookings in the Bookings table.

c. To count how many bookings recorded in the booking table.

Part B: Conceptual data models and SQL Queries

Question B1- Write SQL queries

Write SQL queries for the following questions.

Execute the following queries on the "Party_BookingDB" database you created in MS Access. Include screen shots of the outputs and all SQL statements you used to answer following questions:

a. Prepare a list of all the records in the "Booking" table where date_of _event is on 4th June 2020 which are booked on 2nd February 2020.

b. Assume you need to find out BookingID, CustomerID, date_booked and menu_id of all bookings.

c. Assume that you want to count how many bookings are there in the "Bookings" table for the customer with customer_id =124. Write a query to find the number.

d. Staff at "Party_Booking" needs to prepare a report with CustomerID, StaffID(Booking taken by), first_name(staff) and date_booked of all events scheduled for 24th August 2020.

Question B2 - ER-to-Relational Mapping

This question is on "ER-to-Relational Mapping". Figure 2 (attached) shows the ER diagram that captures important information about customer and loans.

a. You are required to mark cardinality according to the following statements.

I. Customer can borrow one or more loans

II. One loan belongs to only one customer.

b. Convert the ER diagram into a relational database schema. Be certain to indicate primary keys (underline). For example, Customer entity can be mapped to relational database schema as given below.

Eg. Customer (Cust_ID:text; name:....)

Borrower (cust_id, loan_id, access_date)

Customer (cust_id, name, street_address, city)

Loan (loan_id, amount, cust_id)

Question B3 - Research and trends

a. Refer to the paper "Visualizing Big Data with augmented and virtual reality: challenges and research agenda", which is accessible, write a paragraph explaining the usage of AR and VR in Big Data visualization with proper references.

b. Create 2 data visualisations using Tableau. Tableau is a data visualization software. Use data provided in "Resources/ Sample Data" section in the Tableau.

Part C: Performance Evaluation

Demonstrate your progress to your tutor in week 7 or week 8 lab classes.

This will allow students to demonstrate their understandings and skills (to their teacher) as they perform these activities. The teacher evaluates you on the quality of your ability to perform specific tasks and the products you created in the process. Your final mark for Part B of the assignment will be calculated based on this evaluation as describe below.

Attachment:- Data and Information Management Assignment File.rar

Reference no: EM132498905

Questions Cloud

What would be appropriate tax rate be for use in calculation : annual coupon bonds outstanding. What would be the appropriate tax rate be for use in the calculation of the debt component of LilyMac's WACC?

What is the projects discounted payback period : $40,000, expected net cash inflows of $9,000 per year for 7 years, and a cost of capital of 11%. What is the project's discounted payback period?

What is the equivalent annual annuity for each plane : Shao Airlines, What is the equivalent annual annuity for each plane? Do not round intermediate calculations. Enter your answers in millions.

What was the kennedy strategy for dealing : What was the Kennedy strategy for dealing with the civil wars that erupted in former colonial territories and what was the problem

MN405 Data and Information Management Assignment : MN405 Data and Information Management Assignment Help and Solution - Melbourne Institute of Technology, Australia. Title - Data Modelling and Data Managing

How did the second world war affect the american populace : How did the Second World War affect the American populace? Provide three specific examples to support your position on the question.

Determine the internal rate of return : With a cost of capital of 18 percent, should the machine be purchased? Determine the internal rate of return. Home Security Systems is analyzing the purchase

What do you think fdr did well : If you had been the President during the Great Depression what would you have done differently? What do you think FDR did well?

Determine how does carrying securities at fair value : Determine How does carrying securities at fair value differ from the equity method in reporting income from nonsubsidiary investments?

User Account

All Pages