Assignment 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.
Avail our finest Data Modelling and Data Managing Assignment Help service and you will surely witness the remarkable difference in your academic grades!!
Tags: Data Modelling and Data Managing Assignment Help, Data Modelling and Data Managing Homework Help, Data Modelling and Data Managing Coursework, Data Modelling and Data Managing Solved Assignments
Attachment:- Data and Information Management.rar