ISYS1055 Database Concepts

RMIT Classification: Trusted

School of Science

ISYS1055 Database Concepts

Assessment 4: Database Design Project

RMIT_icon_Industry_connections_Red_RGB.pngAssessment type: Take-home assessmentWord limit: N/A
  RMIT_icon_add_an_event_Red_RGB.png  Due Date: 12 June at 23:59 (Melbourne Time) 
    Weighting: 35%, 35 Marks 

Overview

This is a practical and real-world project that puts the knowledge you gained into practice. You are required to investigate and understand a publicly available dataset, design a conceptual model for storing the dataset in a relational database, apply normalisation techniques to improve the model, build the database according to your design and import the data into your database, and develop SQL queries in response to a set of requirements.

The objective of this assignment is to reinforce what you have learned in the whole course. Specifically, it involves how to build a simple application that connects to a database backend, running a simple relational schema.

  • Part A: Understanding the Data (0 Marks, Preliminary Work)
  • Part B: Designing the Database (10%)
  • Part C: Creating the Database and Importing Data (10%)
  • Part D: Data Retrieval and Visualisation(15%)

Assessment criteria

This assessment will measure your ability to:

  • Analyse the requirements outlined in the problem description
  • Develop a conceptual model for the design of a database backend required for the system
  • Use an industry-standard ER modelling tool to draw the ER model
  • Use 7-step mapping process to create relational database schema
  • Use normalisation process to evaluate the schema and make sure that all the relations are at least 3NF

Page 1 of 8

RMIT Classification: Trusted

  • Create tables on SQLite Studio and populate them with data available from the specified sources.
  • Write SQL statements required for CRUD (create, read, update and delete) operations on the database you built
  • Develop your knowledge further to represent data in a meaningful way using data visualisation.

Course learning outcomes

This assessment is relevant to the following course learning outcomes:

  CLO1Describe the underlying theoretical basis of the relational database model and apply the theories into practice;
CLO2Explain the main concepts for data modelling and characteristics of database systems.
  CLO3Develop a sound database design using conceptual modeling mechanisms such as entity-relationship diagrams.
CLO4Develop a database based on a sound database design;
  CLO5Apply SQL as a programming language to define database schemas, update database contents, and to extract data from databases for specific users’ information needs.

Page 2 of 8

RMIT Classification: Trusted

Assessment details

Part A: Understanding the Data

In this assignment, we are working with the publicly available dataset: A Global Database of COVID-19 Vaccinations. Further details about this dataset are available in the article available through the following URL: https://www.nature.com/articles/s41562-021-01122-8. The abstract of the article is as follows.

Text Box: An effective rollout of vaccinations against COVID-19 offers the most promising prospect of bringing the pandemic to an end. We present the Our World in Data COVID- 19 vaccination dataset, a global public dataset that tracks the scale and rate of the vaccine rollout across the world. This dataset is updated regularly and includes data on the total number of vaccinations administered, first and second doses administered, daily vaccination rates and population-adjusted coverage for all countries for which data are available (169 countries as of 7 April 2021). It will be maintained as the global vaccination campaign continues to progress. This resource aids policymakers and researchers in understanding the rate of current and potential vaccine rollout; the interactions with non-vaccination policy responses; the potential impact of vaccinations on pandemic outcomes such as transmission, morbidity and mortality; and global inequalities in vaccine access.

A live version of the vaccination dataset and documentation are available in a public GitHub repository at https://github.com/owid/covid-19-data/tree/master/public/data/vaccinations. These data can be downloaded in CSV and JSON formats.

For the purposes of completing this assignment, we are only using the following files. You are required to review and analyse the dataset available in these files. You will find that reviewing the rest of the files, even if not listed below, will help you to form a better understanding about the big picture.

 FILE NAMEDESCRIPTION
1locations.csvCountry names and the type of vaccines administered. Each line represents the last observation in a specific country. Refer to README.md for the details.
2us_state_vaccinations.csvHistory of observations for various locations in the US.
3vaccinations-by-age-group.csvHistory of observations for vaccinations of various age groups in each country.
4vaccinations-by- manufacturer.csvHistory of observations for various types of vaccines used in each country.
5vaccinations.csvCountry-by-country data on global COVID-19 vaccinations. Each line represents an observation date. Refer to README.md for the details.
6country_data/Australia.csvDaily observations of vaccination in Australia.

Page 3 of 8

RMIT Classification: Trusted

7country_data/United States.csvDaily observations of vaccination in the US.
8country_data/England.csvDaily observations of vaccination in England.
9country_data/China.csvDaily observations of vaccination in China.

Table 1: List of data files

To complete the tasks in the following sections, you are required to review and analyse the dataset that is available in the named files.

Part B: Designing the Database (10%)

Task B.1 Produce an ER diagram for a relational database that will be able to store the given dataset.

It is important to note that the given CSV files are not necessarily representing a good design for a relational database. It is your task to design a database that will adhere to good design principles that were taught throughout the course. This means your database schema will not match the structure of the CSV files and, therefore, you will require to manipulate the structure of the dataset (and not the data itself) to import it into your database. Importing the data is required to complete Task C.2.

The ER diagram must be produced by Lucidchart similar to the exercises that were completed in in the course. UML notation is expected and using other notations will not be acceptable. Including a high-quality image representing your model is important, which can be achieved using Export function of Lucidchart.

You are also required to transform the ER diagram into a database schema that will be used in the next part of the assignment.

Creating a good database design typically involves some database normalisation activities. You should document your normalisation activities and support them with good reasoning. This typically involves explaining what the initial design was, what the problem was, and what changes have been made to rectify the issue.

The expected outcome of completing this task is one PDF file named model.pdf containing the following sections.

  1. Database ER diagram and, if needed, a reasonable set of assumptions.
  • Explanation of normalisation challenges and the resulting changes.
  • Database schema.

Part C: Creating the Database and Importing Data (10%)

Task C.1 Produce one SQL script file named database.sql. This script file requires all the SQL statements necessary to create all the database relations and their corresponding integrity constraints as per your proposed design in Part B. The script file must run without any errors in

Page 4 of 8

RMIT Classification: Trusted

SQLite Studio and contain necessary commenting to separate various relations. Note that this script is not supposed to store any data into the relations.

The expected outcome of completing this task is one script file with the specific name of database.sql.

Task C.2 Create a database file named Vaccinations.db. Import the given dataset into your database.

To complete this task, you may need to change the format of the CSV files to match the attributes of your designed database. You can use a spreadsheet editor such as Microsoft Excel.

The next step is to import the spreadsheets into the database you create in SQLite Studio. To complete this task, use the menu option Tools – Import in SQLite.

The expected outcome of completing this task is one database file named Vaccinations.db, which must contain all the data that is stored in the CSV files named in Table 1.

Part D: Data Retrieval and Visualisation (15%)

Now that you have created and populated a database, it is time to create some queries to investigate the data in various ways. In addition to writing the required queries, you are also asked to produce data visualisation for the results of your queries.

The tasks in this section represent the queries that must be supported. Each query must consist of one SQL statement. It would be acceptable to use several nested queries, combine several SELECT statements with various operators etc. However, it would not be acceptable to have multiple and separated queries for each task.

After you have written each query, you are expected to produce a data visualisation for each result set. You have the freedom to choose the tool for creating your visuals (e.g., Excel, Google Charts, Tableau) as well as the visualisation techniques (e.g., charts, plots, diagrams, maps). Completing this portion of the work will require that you understand the nature of the results of each query, undertake research to choose a visualisation tool you are comfortable with, decide about the best technique to visually represent each result set, and produce the visualisation. Answers to tasks in Part D that are not supported by a visualisation can achieve up to 80% of the grade associated with each task.

The expected outcome of completing this task is as follows.

  1. One SQL script file named Queries.sql containing all the queries developed for the tasks in this section. It is important that you add comment lines to separate the queries and indicate which task they belong to. Note that valid SQL comments must not generate errors in SQLite Studio. The marker of your work will use this file to execute and test your queries.
  2. A PDF file named QuerieResults.pdf containing the following elements for each task.
  1. The SQL query

Page 5 of 8

RMIT Classification: Trusted

  • a snapshot of the first 10 results of your query. The snapshot must also show the total number of results retrieved by the query. A sample snapshot is provided below for your reference.

Figure 1: Sample results snapshot with total rows

  • Data visualisation

List of Tasks

Task D.1 For any two given dates (i.e., you can assume any two dates, e.g., 1 April and 3 April), list the dates, the total number of vaccines administered in each observation date in each of all countries, and the difference between the administered vaccines. Each row in the result set must have the following structure. (Note: OD2 is after OD1)

Observation Date 1 (OD1)Country Name (CN)Administered Vaccine on OD1 (VOD1)Observation Date            2 (OD2)Administered Vaccine                      on OD2 (VOD2)Difference of totals (VOD1-VOD2)

Figure 2: Column Headers in the Result Set for Task D.1

Task D.2 Find the countries with the cumulative numbers of COVID-19 doses administered by each bigger than the average doses administered by all countries. Produces a result set containing the name of each country and the cumulative number of doses administered in that country. Each row in the result set must have the following structure.

CountryCumulative Doses

Figure 3: Column Headers in the Result Set for Task D.2

Task D.3 Produce a list of 10 countries with the biggest numbers of vaccine types, with the type of vaccines (e.g., Oxford/AstraZeneca, Pfizer/BioNTech) administered in each country. For a country that has administered several types of vaccine, the result set is required to show several tuples

Page 6 of 8

RMIT Classification: Trusted

reporting each type of vaccine in a separate tuple. Each row in the result set must have the following structure.

CountryVaccine Type

Figure 4: Column Headers in the Result Set for Task D.3

Task D.4 There are different data sources used to produce the dataset. Produce a report showing the total number of vaccines administered according to each data source (i.e., each unique URL). Order the result set by source name and URL. Each row in the result set must have the following structure.

Source NameTotal Administered VaccinesSource URL

Figure 5: Column Headers in the Result Set for Task D.4

Task D.5 How does various countries compare in the speed of their vaccine administration? Produce a report that lists all the observation dates in 2022 and, for each date, list the total number of people fully vaccinated in each one of the 4 countries used in this assignment.

[Date, Australia, United States, England, China]

DateAustraliaUnited StatesEnglandChina

Submission Format

Figure 6: Column Headers in the Result Set for Task D.5

You are required to submit the files with the exact names as below.

  1. Model.pdf
  2. Database.sql
  3. Vaccinations.db
  4. Queries.sql
  5. Queries.pdf

In the previous sections of the assignment, the expected content of each of the files is explained in detail.

Referencing guidelines

Use RMIT Harvard referencing style for this assessment.

You must acknowledge all the courses of information you have used in your assessments.

Refer to the RMIT Easy Cite referencing tool to see examples and tips on how to reference in the appropriated style. You can also refer to the library referencing page for more tools such as EndNote, referencing tutorials and referencing guides for printing.

Academic integrity and plagiarism

Academic integrity is about honest presentation of your academic work. It means acknowledging the work of others while developing your own insights, knowledge, and ideas.

You should take extreme care that you have:

Page 7 of 8

RMIT Classification: Trusted

  • Acknowledged words, data, diagrams, models, frameworks and/or ideas of others you have quoted (i.e., directly copied), summarised, paraphrased, discussed, or mentioned in your assessment through the appropriate referencing methods.
  • Provided a reference list of the publication details so your reader can locate the source if necessary. This includes material taken from Internet sites.

If you do not acknowledge the sources of your material, you may be accused of plagiarism because you have passed off the work and ideas of another person without appropriate referencing, as if they were your own.

RMIT University treats plagiarism as a very serious offence constituting misconduct. Plagiarism covers a variety of inappropriate behaviours, including:

  • Failure to properly document a source
  • Copyright material from the internet or databases
  • Collusion between students

For further information on our policies and procedures, please refer to the University website.

Assessment declaration

When you submit work electronically, you agree to the assessment declaration.

Page 8 of 8

Order Now

Get expert help for ISYS1055 Database Concepts and many more. 24X7 help, plag free solution. Order online now!

Universal Assignment (October 2, 2022) ISYS1055 Database Concepts. Retrieved from https://universalassignment.com/isys1055-database-concepts/.
"ISYS1055 Database Concepts." Universal Assignment - October 2, 2022, https://universalassignment.com/isys1055-database-concepts/
Universal Assignment July 26, 2022 ISYS1055 Database Concepts., viewed October 2, 2022,<https://universalassignment.com/isys1055-database-concepts/>
Universal Assignment - ISYS1055 Database Concepts. [Internet]. [Accessed October 2, 2022]. Available from: https://universalassignment.com/isys1055-database-concepts/
"ISYS1055 Database Concepts." Universal Assignment - Accessed October 2, 2022. https://universalassignment.com/isys1055-database-concepts/
"ISYS1055 Database Concepts." Universal Assignment [Online]. Available: https://universalassignment.com/isys1055-database-concepts/. [Accessed: October 2, 2022]

Please note along with our service, we will provide you with the following deliverables:

Please do not hesitate to put forward any queries regarding the service provision.

We look forward to having you on board with us.

Get 45%* OFF on Assignment Help

Most Frequent Questions & Answers

Universal Assignment Services is the best place to get help in your all kind of assignment help. We have 172+ experts available, who can help you to get HD+ grades. We also provide Free Plag report, Free Revisions at a Pocket Friendly cost, We offer Best Price in the industry guaranteed.

We provide all kinds of assignmednt help, Report writing, Essay Writing, Dissertations, Thesis writing, Research Proposal, Research Report, Home work help, Question Answers help, Case studies, mathematical and Statistical tasks, Website development, Android application, Resume/CV writing, SOP(Statement of Purpose) Writing, Blog/Article, Poster making and so on.

We are available round the clock, 24X7, 365 days. You can appach us to our Whatsapp number +1 (613)778 8542 or email to info@universalassignment.com . We provide Free revision policy, if you need and revisions to be done on the task, we will do the same for you as soon as possible.

We provide services mainly to all major institutes and Universities in Australia, Canada, China, Malaysia, India, South Africa, New Zealand, Singapore, the United Arab Emirates, the United Kingdom, and the United States.

We provide lucrative discounts from 28% to 70% as per the wordcount, Technicality, Deadline and the number of your previous assignments done with us.

After your assignment request our team will check and update you the best suitable service for you alongwith the charges for the task. After confirmation and payment team will start the work and provide the task as per the deadline.

Yes, we will provide Plagirism free task and a free turnitin report along with the task without any extra cost.

No, if the main requirement is same, you don’t have to pay any additional amount. But it there is a additional requirement, then you have to pay the balance amount in order to get the revised solution.

The Fees are as minimum as $10 per page(1 page=250 words) and in case of a big task, we provide huge discounts.

We accept all the major Credit and Debit Cards for the payment. We do accept Paypal also.

Popular Assignments

Plant Production System Assignment Help

The Best Instant Academic Assistance Online Instant Plant Production System Assignment Help for Students Plant production system assignment help students help them understand the various theoretical and practical aspects of the subject. It is an intensive study of the scientific methods of plant production for an effective and economized production

Read More »

Integrated Science Assignment Help to Boost your Grades Immediately

Integrated Science Assignment Help for Students Integrated Science is taught from a high-school level. This consists of an investigative study of physics, chemistry, biology, forensic science, biotechnology, and geology. This is a course that introduces young students to the interconnectedness between all the scientific knowledge. Students require integrated science assignment

Read More »

Top Human Biology Assignment Help from Experts

Ask an Expert for top Human Biology Assignment Help Sometimes, expert guidance with Human Biology assignment help is all you need in order to boost your grades. Universal Assignment Help Service is a top-ranking student-help platform that offers a wide range of services for students. Students can order human biology

Read More »

Aviation Assignment Help from Experts

The Most Trusted Guidance for Students Aviation Assignment Help for All Challenging Problems Aviation assignment help online from the greatest experts can relieve student pressure to a great extent. It is well known that aviation is an extremely challenging stream, as it is, and students have to keep too many

Read More »

Growing demand for Assignment Help in Ireland

The number of multinational corporations choosing Ireland as their regional headquarters is increasing. So more and more students are choosing Ireland as an idle place to take their degrees. But studying in universities and keeping up the pace is not an easy task and getting good marks is not enough

Read More »

Board appraisal report

Weight: 40% Format: 2,000-word written report. Note: All written assessments should be submitted as Word files (.docx) (Word is available from your AIB Office 365 account) Summary Write a 2000-word Board Appraisal Report where you will evaluate an organisation’s corporate governance practices, board structure including committees and board diversity against

Read More »

49102: Traffic and Transportation

Assignment 2: Transport Modelling and Operations Due: 7 October 2022 This is an individual assignment Please answer the following questions and present your work in a report format clearly including your name and student ID on the cover page. Submit a PDF of your report into the Assignment Activity shown

Read More »

ENV3105 Hydrology Assignment 2

Assignment 2 Due date:       04 October 2022 Weighting:   25% (250 marks) 1. Overview This assessment is designed to test your achievement of selected learning objectives of Modules 6 to 9. In particular, the assignment involves estimation of urban design discharges, reservoir routing and water balance. It is intended to reinforce and

Read More »

Assessment 1: The Journal of Biological Chemistry

Guide abstract All cells within the body are bathed in a fluid. The composition of this fluid is tightly regulated to contain specific levels of various ions. Disruption in the balance of these ions can lead to cell death and damaging inflammation. However, standard surgical practice fails to consider these

Read More »

Project 2: Assignment on Scheme

Due: Oct 1, before midnight. This document first provides the aims of this project. It then lists the requirements as explicitly as possible. It then hints at how these requirements can be met. Finally, it describes how it can be submitted. Aims The aims of this project are as follows:

Read More »

Analysis of Rube Goldberg machine

Week 5 homework for tutorial  In week 4 we needed to also look at how many failures we could have to meet our six sigma level – we will also need to look at this for the times for each stage and total time. To effectively put the flame out

Read More »

PROCESS AND PHILOSOPHY OF ENGINEERING

ASSESSMENT BRIEF 3: Calculations Summary Title Calculations Due Date Monday the 3rd of October at 11:59 (NSW) AEST – End of Week 5 Length 1000-word (does not include equations, charts, graphs or tables) Weighting  35% Submission  WORD document submitted to Turnitin Unit Learning Outcomes  ULO 3: apply basic statistical analysis

Read More »

THT3114 Research Proposal

Mini Report Assignment 2 Executive Summary (100- 150 words) This is the summary of your research and thus needs to be completed after you have completed writing your research proposal. This is not counted towards the word count. Mention the key aspects of the research proposal. Separate paragraphs Area of

Read More »

LITERATURE REVIEW – DATA MINING TO RESEARCH PROPOSAL

ASSESSMENT TWO: LITERATURE REVIEW – DATA MINING TO RESEARCH PROPOSAL – DESIGN RESEARCH INSTRUMENT – PRESENTATION) Task Length: Mini Report (750 words) – Research Proposal) Presentation – Not more than 15 Slides –  (Templates Provided for both the mini report and presentation)  Duration of the Video Presentation – Not more

Read More »

Assignment on COVID-19 pandemic in the hospitality industry

Exploring the new health and safety innovative business practice to restore consumer confidence in the COVID-19 pandemic in the hospitality industry Task Length: Report – 2000 words (+-10%) Task Type Individual Due week and submission deadline Week 11 – SUNDAY – 02/10/2022 – 23:59 Value 40%   PART A: On

Read More »

Assignment Help in Israel

Leading assignments help in Israel Israel is known for its high-tech development, innovations, and entrepreneurship, and is home to the best start-ups in the world. Students from all over the world are drawn to this ecosystem of “innovation and development,” which is supported by a robust higher education system with

Read More »

Animal Production System Assignment Help

Animal Production System Assignment Help: Get 100% guaranteed Expert Assistance Immediately Where can you find the best Animal Production System Assignment Help Online?  Animal Production System assignment help online can boost student results while encouraging interest in the field. The hectic schedule of students, along with their burden of various

Read More »

Immediate Calculus Assignment Help from Top Experts

Immediate Calculus Assignment Help from Top Experts Calculus Assignment Help to Boost your Grades Students can now order instant Calculus assignment help on Universal Assignment Help Service in a fast and easy way. Calculus in taught at a slightly advanced stage of school, and can be an exciting subject in

Read More »

Mathematics Foundation Assignment Help

Effective Mathematics Foundation Assignment Help for Students: Order Expert Solutions Immediately Mathematics Foundation Assignment Help for Students Students often require mathematics foundation assignment help for clearing doubts, understanding the derivation of a formula or theorem, or getting urgent solutions to certain mathematical problems. Online mathematics foundation assignment help is extremely

Read More »

Mathematics Application Assignment Help

Top for Students: Order Expert Solutions Immediately for the Best Results Why do students need Mathematics Application Assignment Help? Mathematics Application assignment help can lead students to perform excellently in the subject in a convenient and relaxed way. Many students find it difficult to apply theoretical knowledge in large and

Read More »

The finest assignment helps in Saudi Arabia

Many students dream to study in Saudi Arabia as they offer some of the best facilities, and they boast one of the most spectacular and rapidly expanding higher education systems in the Middle East. So, this should be of no surprise that every year, many international students prefer to register

Read More »

Expert Solutions for the Best Arithmetic Assignment Help Online

Instant Arithmetic Assignment Help for Students Students most often require Arithmetic assignment help online to solve various calculations. Universal Assignment Help Service provides the best solutions to all arithmetic problems for each student. Students simply have to upload their assignment guidelines on their website, and a customized and accurate assignment

Read More »

Assignment help in Cameroon

The Necessity of Assignment help in Cameroon Today management degrees are one of the most prestigious degrees out there and gaining immense popularity, this degree has the most demand in the market as new startups are emerging day by day, and every company needs the best people to run their

Read More »

Assignment Help in South Korea

Difference between a Good vs Great Online Assignment Tutor The e-learning market is growing at a fast rate due to digitalization. This fact causes an increment in the demand for online tutors as students worldwide searches for a suitable solution for online assignment help in South Korea. You might hear

Read More »

Assignment Help in Japan

Outside-The-Classroom Learning Opportunities It is a typical thinking lesson that stands inside the four walls of a classroom. Some educators say the classroom is the abode of learning and knowledge. We cannot prove these statements wrong. However, one thing is certain: they are partially correct. Learning of concepts starts in

Read More »

Assignment Help in Brazil

Why do Students Consider Online Assignment Help in 2022? Academic life is full of ups and downs. Students who experience such a phase struggle to maintain the equilibrium between personal and professional life. It is because of the load of homework, exams, and participation in extracurricular activities. These events cause

Read More »

Assignment Help in Taiwan

What are the Necessities of Assignment Guidance for Students? As technology takes a new turn around, homework assignments become lengthy and complex. This academic work needs lots of core subject knowledge along with proper research to finish it with proper care. Such work is very time-consuming and hectic for the

Read More »

Assignment Help in Hong Kong

Can You Achieve High Academic Results? Every student wishes to get top scores in their exams. Yet it does not happen if you do not get the correct assignment help in Hong Kong from an online tutor. Such support is in the form of tips, advice, hacks, or taking guidance,

Read More »

VU22977 – Practice in a Legal Environment

Advanced Diploma of Legal Practice – 22565VIC PORTFOLIO- CLIENT MATTER FILE Assessment task 1 Prepare a comprehensive portfolio of documents to create a Client Matter File which must include: Element 1 Area of law Cover sheet stating client name and short description of legal matter Client Instruction Sheet, Costs Agreement

Read More »

KIT714 ICT Research Principles: Assignment 1

Practical Qualitative Research Exercise                                                                                                                                         Type:                 In-Semester, Individual Assignment Task Length:   minimum 2,000 words Weighting:     20% of total assessment for this unit Due Date:       Friday 5 August 2022 – 11:55 pm (Week05) Submission:    electronic submission on MyLO (WORD or PDF) Description:    This practical exercise will engage students in a qualitative research

Read More »

Can't Find Your Assignment?

Get Free Assignment

Open chat
1
Free Assistance
Universal Assignment
Hello 👋
How can we help you?