Data and Information Management

Assessment Details and Submission Guidelines
Unit Code	MN405
Unit Title	Data and Information Management
Assessment Type	Assignment 1 (T32018)
Assessment Title	Managing Data in Databases
Weight	20% of total assessment for the unit
Total Marks	100 = 30 (for online quiz- Part A)+ 70 (for Part B)
Description of this assignment:	This assignment consists of two parts: Part A: An online quiz on Moodle, comprising 30 questions. This quiz coversbasic concepts of the relational data models. Students may have up to 3 attempts to answerthis quiz.  The system will keep the highest score. Part B:Answer SQL Queries to extract data from data tables and questions on mapping conceptual data models into relational data model.

Assignment Description

Part A: (30 marks)

The quiz will open after week 5. Paste below the summary of your Assignment 1 quiz results from Moodle.

Part B: (70 marks)

1.     The snapshot of Loan Management DB database structure is given in Figure 1.

This database collects information about Customers who borrow money from a company.Collector is the person who gives the money to the customers and collects it at a later point in time.

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 Loan_ManagementDBdatabase, shown in figure1.

Figure 1: Snapshot of Loan Management DB database  

a.   First you need to implement the above database in MS Access. Create 4 tables Customer, Collector, Loan Contract and Payment.                   (5 Marks)

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

i.  You can use Datasheet view in MS Access or SQL statement (asgiven below) to enter suitable data records.INSERT into TableName

VALUES (“..”,”..”,.....)

.

           ii.    Include CustomerNumber and CollectorID into the Loan Contract table as                    foreign keys.
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.                                                                                                                                                                                                          (5 Marks)

Write SQL queries for the following questions.

Execute the queries on the “Loan_ManagementDB” database you created in 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)               

c.      Prepare a list of all the records in the “Loan Contract” table. You are required to sort the list in descending order of Loan Amount.                      (5 Marks)

d.  Assume that you want to count how many customers are there in the Loan Contract table  with “Loan Payment Amount Due” greater than $5000. Write a query to find the number.                                                               (5 Marks)

e.    Display contract ID, Date Contract Ends, Customer Number and Collector ID of all customers whose “Loan Payment Amount Due”  is  greater than $5000 (Hint: Join Loan Contract and collector tables )                              (5Marks)

2.Figure 2 shows the ER diagram that captures important information in the tracking of course registration.

                          Figure 2: Course registration ER Diagram

a.    Convert the ER diagram into a relational database schema.  Be certain to indicate primary keys (underline).

Eg.Student
(StudentID:NUMBER; Student_name:text; Student_address:text)                                                                                                         (10 Marks)

b.     Explain the purpose of using anAssociative /Bridge entity.Identify a place in the ER diagram where you can include an Associative / Bridge entity. Justify your answer.                                                              (5 Marks)

4.Business intelligence (BI) is a term that describes a comprehensive, cohesive, and Integrated set of tools and processes used to capture, collect, integrate, store, and analyse data with the purpose of generating and presenting information to support business decision making.Components of the BI framework are given in the Figure 3.

Data visualization is one of the BASIC BI ARCHITECTURAL COMPONENT.

                  Figure 3:Components of the BI framework

Answer following questions.           

                  

a.    Explain the importance of “data visualisation” in Business Intelligence, by using an example.                                                                         (5 Marks)

b.   Tableau (www.tableau.com) is a data visualization tool.Tableau can help anyone see and understand their data. Connect to almost any database, drag and drop to create visualizations.

Install Tableau Public(https://public.tableau.com/s/) on your laptop /computer and create any 2 visualisations. Follow the following instructions:

                          i.  First go to Tableau Public  https://public.tableau.com/s/ and enter your email address and select “Download the App”

                           ii.      Then you can download the software and run the .exe file to install.

                    iii.      Now you will get the following starting screen. Here you can upload MS Excel or MS Access file. Watch this video to find more details on “How to”.https://public.tableau.com/en-us/s/resources

                iv.      You can use any MSExcel, MS Access file or any data file to create 2 visualisations. It should be your own individual work.                                                                                                   (10 Marks)

4.     Performance assessment

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

This will allow students to demonstrate their understandings and skills (to their teacher) as they perform these activities. You are evaluated by the teacher on the quality of your ability to perform specific tasks and the products you create in the process.                                                                                                                                            (15 Marks)

To get solution visit our website www.sourceessay.com