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 (December 4, 2023) ISYS1055 Database Concepts. Retrieved from https://universalassignment.com/isys1055-database-concepts/.
"ISYS1055 Database Concepts." Universal Assignment - December 4, 2023, https://universalassignment.com/isys1055-database-concepts/
Universal Assignment July 26, 2022 ISYS1055 Database Concepts., viewed December 4, 2023,<https://universalassignment.com/isys1055-database-concepts/>
Universal Assignment - ISYS1055 Database Concepts. [Internet]. [Accessed December 4, 2023]. Available from: https://universalassignment.com/isys1055-database-concepts/
"ISYS1055 Database Concepts." Universal Assignment - Accessed December 4, 2023. https://universalassignment.com/isys1055-database-concepts/
"ISYS1055 Database Concepts." Universal Assignment [Online]. Available: https://universalassignment.com/isys1055-database-concepts/. [Accessed: December 4, 2023]

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.

Categories

Get 90%* Discount 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,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

5 Places you must visit in Busan, South Korea

Write 2 articles 1st Topic: 5 Places you must visit in Busan, South Korea 2nd topic: Korean language: various tips and resources to learn Deadline: 10th October 2023 12:00 AM Instructions: Article Structure is given below Meta Description: First, you will start with a meta description of your article in

Read More »

2002HSV Human Services Research

Assessment 2: Written research proposal Worth 45% of final grade Complete your answers in this assignment template Name: xxxxxxx Student ID: xxxxxxx Title: xxxxxxx Exact word count (exc. cover sheets and reference list; max. 2,000 words): x,xxx Please specify the research methodology are you proposing to use: ☐ Quantitative                                    ☐

Read More »

COIT29226 Introduction to IoT

Assessment item 3 – Project Due date: Week 12 (8 October 2023) 11:55 pm AEST Weighting: Length: Submission: 50% There is no word limit for this report Group work (submit via Moodle) Objectives This assessment item relates to the following unit learning outcomes: Learning Outcomes Assessed: Enabling objectives What do

Read More »

BUSM1008 Assessment 2 Brief Spring 2023

Assessment 2: Individual Report (20%) and Presentation (20%) Background You are appointed as a business consultant, recruited by the company allocated to you in week 6 by your tutor.  Your role is to undertake research on the company, the allocated topic and an issue identified in the media in relation to your company and

Read More »

ECON1020 PRINCIPLES OF ECONOMICS

INDIVIDUAL ASSIGNMENT Total Marks 100 | Weighting 25% DUE: 29/09/2023, 11:55pm IMPORTANT INFORMATION: READ BEFORE YOU START SUBMISSION 11.55 pm. A 1-hour grace period is provided to students who experience a technical concern. DOCUMENT PREPARATION REFERENCING UNACCEPTABLE ACADEMIC ACTIVITIES MARKING Question 1 | [12 marks] Download the spreadsheet data that

Read More »

ENTERPRISE LAW LAWS1001

Assessment task 3 Individual Case Study This task is the result of your individual effort, so students must not discuss their work with anyone else, otherwise it may lead to an allegation of collusion. Weight: 15% Due: Before 5 pm on Wednesday 27th September. Late submissions will incur a penalty

Read More »

International Business INBU01-7

Module International Business INBU01-7 (NQF LEVEL 7) FORMATIVE ASSESSMENT – ASSIGNMENT C Assignment C (INBU01-7/DLO4 10/2023) Exam Date 20 October 2023 Marks 50 Assignment C (INBU01-7/DLO4 10/2023)   Total: 20 marks SECTION A (10 MARKS) – PARAGRAPH QUESTION Question 1 (10 marks) Coca-Cola is marking its 70th year in Nigeria

Read More »

Summative Assessment Help

Summative Assessment Brief: Learning Outcomes: LO1: Construct a problem/issue/need identified from practice that impacts on individual outcomes LO2: Formulate a focus-based enquiry on the identified problem/issue/need utilising an area of individual learner interest LO3: Critically evaluate the evidence base to support the proposal of the solution focused initiative LO4: Develop

Read More »

EDU30067 Teaching English

Assignment 2: Folio Template INTRODUCTION:         LESSON 1 Lesson title:   Year level: Topic:   Duration of lesson: Curriculum links: Strands & sub-strands Content descriptions       Students background knowledge: What is your starting point – what do the students already know, what have they done

Read More »

Edge Computing Security and Privacy

Proposal Background Title: Edge Computing Security and Privacy Proposal The new computing paradigm in the Internet of Things domain is known as IoT edge computing which functions by processing calculations at the edge of the network (Alli & Alam, 2020). The current technology aided by cloud and fog computing has created

Read More »

Foundations of Communication Assessment 2 Instructions

Discussion task (Total grade – 10%)   Process Work (Steps to complete the task) Answer on the Assessment 2 Submission Document – Do not upload this instruction document. Requirements (important elements to include) lastname_firstname_studentID_COMS_Assess 2_semester_year. Process for Assessment discussion tasks NOTE: If you do not participate in the class discussions then

Read More »

Assessment Two Dos and Don’ts (Internal)

Do: Read the assessment instructions carefully Read the marking rubric carefully Understand the question – break it down Conduct research – record the reference details Read the topic materials about communication theories, forms and factors and refer to them Contribute to the discussions in class or no marks for the

Read More »

Unpacking the Question: Assessment Two

Studying at university requires you to become familiar with strategies to un-pack or breakdown a variety of assessment questions/tasks throughout your studies.  Task Example of integrating the discussion, research and theories. In the group discussion my group members (name them) stated that an advertisement for a beauty product would most

Read More »

MGMT0001 Introduction to Commerce Assessment

MGMT0001 Introduction to Commerce Assessment 2 (worth 30%) Due date:                  2:00pm (AWST) Friday 15th September 2023 Submission:              Via Turnitin assessment submission link (see ‘Assessment 2’ folder in ‘Assessments’ section of Blackboard). Additional Instructions: Part A (42 marks) Nathan is a well-known chef with years of experience cooking various cuisines. After

Read More »

ACCT5011: Accounting Systems in the Digital Age

Practical Assignment Guide, Semester 2, 2023 Due Date for Submission: Monday 11th September 2023 at 5.00 PM Please note that the Folio Assignment for ACCT5011 Accounting Systems in the Digital Age is an individual assessment task worth 30% of your total marks in the unit. Please refer to the online

Read More »

EC229- Review session

Assume two cities, A and B, that can’t trade between them. Each city produces its own coconuts for its local market. If suddenly trade is possible then: D) As we saw in class, the new price will be somewhere between the original price 𝑃_𝐴,𝑃_𝐵. Hence it is impossible for consumers

Read More »

Computing Theory COSC

Computing Theory COSC 1107/1105 Assignment 1: Fundamentals Assessment Type Individual assignment. Submit online via Canvas → As- signments → Assignment 1. Marks awarded for meeting re- quirements as closely as possible. Clarifications/updates may be made via announcements/relevant discussion forums. Due Date Week 6, Sunday 27th August 2023, 11:59pm Marks 125

Read More »

BE279 Applied Statistics and Forecasting

Strategy, Operations, & Entrepreneurship Group Essex Business School Module Code BE279 Module Title Applied Statistics and Forecasting Assessment Type Individual Report (2,000 word) Academic Year 2022/23, Spring Term Submission Deadline Refer to FASER Task Specific Guidance Please note that: Module Learning Outcomes On successful completion of the module, students will

Read More »

Learning Design Tool: Little Learners Level 1 sounds

Learning Design Tool: Little Learners Level 1 sounds. Prepared by Sara Hart Date 30th August, 2023 (feel free to leave this date as it is the AT2 due date) Brief description of Learning Design   Chooseit Maker: Create, edit and play personalised learning activities that can be used in your

Read More »

HUMN1041 PEOPLE, PLACE AND SOCIAL DIFFERENCE ASSIGNMENT

HUMN1041 PEOPLE, PLACE AND SOCIAL DIFFERENCE ASSIGNMENT 1 TEMPLATE This assignment is made up of three (3) parts, plus a reference list and appendix. Marks are allocated for each section, as follows: – 1 mark Please ensure that you provide your answers in this template, and provide a Reference list

Read More »

Model 3 Launch in Australia

Client Information Company Name Tesla Contact Name   Email   Phone Number   Address Level 14, 15 Blue St. North Sydney, NSW 2060 Australia Ad link & image Project Information Project Title Model 3 Launch in Australia Project Description (100 words) Highlight the uniqueness of the car. Show the superior

Read More »

MKT10009 Marketing and the Consumer Experience

School of Business, Law and Entrepreneurship Assessment Task – Assignment 2  MKT10009 Marketing and the Consumer Experience Semester 2, 2023. Assessment Type Analytical Report Associated Unit Learning Outcomes (ULO’s) 2, 3, 4 Group or Individual task Individual Value (%) 25% Due Date Monday 20th September at 10:00 AET – Enterthis

Read More »

ASSIGNMENT – 1st Evaluation

ASSIGNMENT – 1st Evaluation Date of Submission- 4TH SEP 2023 25 MARKS COMPARATIVE PUBLIC LAW Q. You are the new Central Minister for Urban Development who is keen to make major Indian cities as smart cities. You visit different countries like USA, UK, European Countries, Japan and other developed countries

Read More »

Corporate & Financial Due Diligence Report

[Name of the company] Note: Students should keep in mind that application of legal provisions (including Securities Regulations) and analysis of the same is important. Merely putting the facts and figures won’t fetch even a decent mark. Note: Students should only mention the broad area of business. This part should

Read More »

ECON1000 S2 2023 – Marking Guidance and FAQs on GTP

Students will be marked on the extent to which they specifically answer the question and provide clear, logical, well-reasoned and sufficient explanations. Here is a summary breakdown of how marks are allocated in this GTP: Part 1 [15 marks] §  Providing relevant observations from the information provided in the articles

Read More »

ECON1000 S2 2023 – GTP Brief

ECON1000 S2 2023 – GTP Brief A.  Context and Overview The Game Theory Presentation (GTP) is worth 30% of the final mark. GTP is a ‘take-home’ exercise with a set of tasks to do. The GTP is based on Lecture Topics: L1 and L2. You will have twenty (21) days

Read More »

Order #35042 Human Rights Research Essay

Draft due 26th Aug 2,500 words (excluding references and bibliography) Instructions: Structure: Title – The Tigray War: A Critical Analysis of What the Future Holds for Human Rights in the Region What can be done to ensure human rights violations will cease and be prosecuted? Expand on different actors and

Read More »

PMC1000: Applied Pathology

Assessment Task Sheet: Poster Presentation Date: Thursday 31st August 2023 | Weighting: 30% Assessment Task 2 You are required to develop a handout style resource (pamphlet or brochure) on a selected pathological condition which is aimed at first year student paramedics. You will need to use high quality peer reviewed

Read More »

Detailed Information: Reflective Assignment

Indigenous Peoples, Law and Justice Detailed Information: Reflective Assignment Due Date:                   Tuesday 5th September 2023 at 4:00 pm (AWST). Marks:                       30% of the total marks for this unit. Assignment:              The assignment will comprise two questions. Students must answer all parts of both questions. Examinable topics: The cultural immersion exercise and/or

Read More »

Can't Find Your Assignment?

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