Description
Your task is to create a SQlite database to represent current Coronavirus data from an Open Data Source, in order to be able to answer questions and perform simple analysis. This will involve creating and normalising a database to hold the data from a dataset and constructing queries against the data once it is in a suitable form. There are 4 parts, along with an optional extension, for this coursework: 1. The Relational Model (20%) 2. Normalisation (25%) 3. Modelling (20%) 4. Querying (30%) 5. Extension (5%) You will be required to write a brief report to answer the questions and note down your process, thoughts and assumptions made as well as any answers. You will also need to construct a SQLite database, and produce a set of queries that can be run against that database. Dataset The dataset to be used for this exercise is COVID-19 Coronavirus data from the EU Open Data Portal. The dataset contains the latest available public data on COVID-19. Please use the snapshot of this dataset, taken on the 27th of April, available here for this coursework: http://secure.ecs.soton.ac.uk/notes/comp1204/1920/ofb/cw2/dataset.csv 1. The Relational Model EX1: Write down the relation directly represented in the dataset file. Assign relevant data types to each column. EX2: List all of the functional dependencies that exist in the dataset Note: You do not need to (but will not be penalised for) including overlapping dependencies, but you should aim for the tightest dependency (e.g. if A -> B, it adds nothing that A, C -> B) Tip: Explain any assumptions you make applying what you know of the domain to the data and consider future data and the impact it may have as well. Tip: You do not need to include trivial dependencies EX3: List the potential candidate keys EX4: Identify a suitable primary key, and justify your decision 2. Normalisation Keys: Where possible, you should only introduce new Surrogate Keys where they are necessary. If and where they are necessary, to avoid anomalies, you should explain this in your report with a justification. Attributes: While you are able to introduce new attributes if you wish, you must not remove any of the attributes in the original relation (such as the dateRepresentation, even though it is otherwise broken up) NULL values: NULL values are not values in themselves, but represent unknown values in the dataset (you cannot treat all NULL values as the same ‘null’ value). NULL values can be present throughout the normalisation process, you do not need to remove them. However, you may find you need to introduce surrogate keys in the case where a NULL could or is present in something you would want to be a key or split into a relation, for example. EX5: List any partial-key dependencies in the relation as it stands and any resulting additional relations you should create as part of the decomposition. EX6: Convert the relation into 2nd Normal Form using your answer to the above. List the new relations and their fields, types and keys. Explain the process you took. EX7: List any transitive dependencies in your new relations EX8: Convert the relation into 3rd Normal Form using your answers to the above. List the new relations and their fields, types and keys. Explain the process you took. EX9: Is your relation in Boyce-Codd Normal Form? Justify your answer. 3. Modelling Note: Where possible, you should only introduce new Surrogate Keys where they are necessary. If and where they are necessary, to avoid anomalies, you should explain this in your report with a justification. Attribute Names: All original attribute names must remain as they were in the original dataset, you may not rename them – this ensures consistency with the original dataset and easy importing of new data. EX10: Using the CSV import function, import the raw dataset into SQLite into a single table called ‘dataset’ in an SQLite database called coronavirus.db. Tip: You can import a CSV in SQlite using .mode csv then .import file table Note: You may not change the CSV file – it must be the original provided dataset file Export this table as dataset.sql (including CREATE and INSERT statements), such that running it will import the full dataset into a fresh SQLite database. The entire database at this point should be exported as dataset.sql EX11: Write the SQL to create the full normalised representation, including all additional tables, with no data. The SQL should contain CREATE statements to create any new tables. You should include indexes where appropriate, and list and justify these in your answer. If you have introduced any surrogate keys, please list and justify them as part of this answer. The SQL statements to create the tables should be saved as ex11.sql The entire database at this point should be dumped as dataset2.sql EX12: Write INSERT statements using SELECT to populate the new tables from the ‘dataset’ table The SQL statements to populate the tables from the dataset table should be saved as ex12.sql The entire database at this point should be dumped as dataset3.sql EX13: Test and ensure that on a clean SQLite database, you can execute dataset.sql followed by ex11.sql followed by ex12.sql to successfully populate your database. 4. Querying For each exercise in this question, you will need to write an SQL query (against your newly created normalised tables in your database). Each SQL statement should be written in the report, as well as saved as ex.sql (e.g. ex14.sql for the first) which can be run against your database, as it stands at the end of EX12. IMPORTANT: When run, the query MUST return the results(such that sqlite3 coronavirus.db < ex14.sql will return the results) Ordering: Remember, you can order by things not in the SELECT part of the query (e.g. ORDER BY year,month,day) Write an SQL statement for each of the following: EX14: The worldwide total number of cases and deaths (with total cases and total deaths as columns) EX15: The number of cases and the date, by increasing date order, for the United Kingdom (with date and number of cases as columns) EX16: The number of cases, deaths and the date, by increasing date order, for each continent (with continent, date, number of cases and number of deaths as columns) EX17: The number of cases and deaths as a percentage of the population, for each country (with country, % cases of population, % deaths of population as columns) EX18: A descending list of the the top 10 countries, by percentage deaths out of the cases in that country (with country name and % deaths of country cases as columns) EX19: The date against a cumulative running total of the number of deaths by day and cases by day for the united kingdom (with date, cumulative UK deaths and cumulative UK cases as columns) Tip: You will want to use the Window Functions in SQLite to achieve this. However, these require a newer version of SQLite, which you can download from the SQLite website. I would suggest either using the autoconf amalgamated version and using the configure script with a –prefix, or using the precompiled Linux binaries. 5. Extension EX20: Using GnuPlot, write a small script (plot.sh) which will, using the data in the SQLite database (called coronavirus.db), produce a graph named graph.png with the date on the horizontal axis and the cumulative number of deaths by country on the vertical axis. Tip: You may need to create temporary files or folders as part of your script – please ensure you use mktemp to create these (you can create an entire temporary folder with mktemp and then write inside it if you wish). Note: You must run ‘sqlite3’ not a different path or copy. If you have updated sqlite3 and need to run it explicitly, please add it to your path not the script. Note: Assume that the coronavirus.db file is in the same folder as the script. Note: There will be a lot of countries if you represent them all, so if possible, limit to the top 10 (in terms of cumulative deaths) countries Include an explanation of your script in the report. The full script and resulting graph should be included as an appendix in the report (not counting towards the page limit) and in the archive itself. Submission Report You must write your report in LaTeX and produce a report PDF. The name of the generated file should be report.pdf, the source report.tex. Your report should not be more than 5 pages long excluding the cover (first) page. Your report should contain: A title, your name, your username and your student ID. You must have a /section for each part of the coursework, and they must be named as follows: 1. The Relational Model 2. Normalisation 3. Modelling 4. Querying 5. Extension (if applicable) Each section must have a /subsection for each exercise labelled EX#. Files Your submission should be a single cw2.tar.gz tar.gz file. Your submission must include the following in this structure: 1. report.tex: The full source code to the LaTeX report 2. report.pdf: The generated result of the LaTeX report 3. dataset.sql: The full dump of the database after importing the dataset 4. dataset2.sql: The full dump of the database after creating the normalised tables 5. dataset3.sql: The full dump of the database after the normalised tables have been populated 6. ex11.sql: The SQL to create the normalised tables 7. ex12.sql: The SQL to populate the normalised tables from the dataset table 8. ex14.sql 9. ex15.sql 10. ex16.sql 11. ex17.sql 12. ex18.sql 13. ex19.sql 14. plot.sh: The script to generate a graph for the extension section (optional) 15. graph.png: The generated graph (optional) Support Any questions and answers will be added to the FAQ Please email ob1a12@soton.ac.uk with any questions you may have or anything you would like us to go over. Please make sure you include COMP1204 in the subject. Good luck! Retrieved from “https://secure.ecs.soton.ac.uk/noteswiki/index.php?title=COMP1204/Coursework2&oldid=32310” Views Page Discussion View source History Watch Personal tools At2n19 My talk My preferences My watchlist My contributions Log out Navigation Main Page Recent changes Random page Help Search Go Search Toolbox What links here Related changes Special pages Printable version Permanent link This page was last modified on 29 June 2020, at 11:55. This page has been accessed 10,861 times. Privacy policy About NotesWiki Disclaimers