[SOLVED] 7CCSMDDW- MSc_Database_CW2

Description

 Data Loading, Transformation & Normalisation

Part 1.1: Backup Restoring. Write the required SQL statement/s to restore the data contained in the file crimes2015.sql in the provided template file: backupRestoring.sql

Part 1.2: Data Loading.               Write the SQL statements to perform the following operations:

• 2.1. Table Creation. Write the required SQL DDL (Data Definition Language) statements (i.e. CREATE TABLE) to create two tables (named crimes2013 and crimes2014) that can held the data contained in the datasets crimes2013.txt and crimes2014.csv. Ensure that:
  • table and attribute names do not conflict with SQL reserved words
  • attribute data types are core SQL data types as described in the lectures
• 2.2. Data Load. Write the required SQL statements to load the data contained in the datasets crimes2013.txt and crimes2014.csv into the tables crimes2013 and crimes2014.

Write all of these SQL statements in the above order in the provided template file: dataLoading.sql

Part 1.3: Data Preprocessing. Write the required SQL statements to remove any duplicates, inconsistencies and anomalies in the data (that is in the tables crimes2013, crimes2014 and crimes2015). Among others, the following inconsistencies and anomalies need to be solved:

• The tables in the different datasets do not have the same number of columns.
• The column time occ in some of the tables may not be formatted properly (e.g., the value ’1’ should be interpreted as ’00:01’).
• The column image no in some of the tables may contain invalid values.
• The capitalization of the status codes (status) are not consistent in the three tables.
• Different formats may be used for the dates.
• The data about some area names are missing.

Write all of the SQL statements required to preprocess the data in the provided template file: dataPreprocesing.sql

Part 1.4: Data Integration. Write the required SQL statements to integrate the data in the tables crimes2015, crimes2014 andcrimes2013 in a single table named crimes. Write these SQL statements in the provided template file: dataIntegration.sql

Part 1.5: Database Normalization. Write the required SQL statements to normalize the crimes table into a set of tables in 3NF. Ensure that:

• every table has a primary key
• all foreign keys are properly declared

Write these SQL statements in the provided template file: normalization.sql

Part 2: Constraint Implementation

Setup. In Part 2, you will use SQL commands to implement domain constraints in the tables contained in the crime2015.sql backup. On KEATs you will find a .zip file which contains template SQL files to edit for your Part 2 submission. Again, be sure to:

1. Include your NAME at the top of the file in a SQL line comment.
2. Edit these files as text, not Word files or propriety SQL software.
3. Do NOT rename the files.
4. Include any comments using the SQL line comment (i.e. lines beginning with −− ).

Part 2.2: Status Transition. Create MySQL stored program/s (e.g., trigger) to ensure that the status code of crimes behaves as indicated by the following transition diagram:

That is the crimes are created with the status UNK, then crimes can transition to status IC, from that status they can transition to AO or JO, etc. Crimes can only be deleted when they are in status AA or JA.

Write all of these SQL statements in the above order in the provided template file: constraints.sql

Part 3: Data Warehouse

Setup. In Part 3, you will create a data warehouse to analyse the number of crimes contained in the crimes2015.sql backup. On KEATs you will find a .zip file which contains template SQL files to edit for your Part 3 submission. Again, be sure to:

1. Include your NAME at the top of the file in a SQL line comment.
2. Edit these files as text, not Word files or propriety SQL software.
3. Do NOT rename the files.
4. Include any comments using the SQL line comment (i.e. lines beginning with −− ).

Include your pdf in the .zip of your Part 2 Coursework submission.

2. SQL Statements. In Part 3, you will also develop SQL statements to create a data warehouse. Include your edited version of the file sql in the .zip of your Part 3 submission.

We will evaluate these SQL files using NMS’s server (which is version 5.5.50-MariaDB precisely). As a student, you can utilize NMS’s server —recall it is accessible via: https: //nms.kcl.ac.uk/mysql— or your own database server installed on your personal computer to test your files and commands.

You can assume that these files are executed in the order that they appear in the description that follows:

• sql
• sql

Any file that is missing, renamed, or does not run will result in 0 points for that section.

Part 3.1: Entity-Relationship Diagram. The police department would like to perform different queries related to the occurrence of individual crimes, by type, area, status, and by when they occurred and when they where reported. Given the data available, design a data warehouse that meets these requirements. Draw the ER Diagram of the star schema of this data warehouse. Your diagram must also cover the requirements below:

• Include all entities, attributes (including data types), and relationships. If necessary, please explain/justify your design choices.
• Cardinalities and Assumptions. On the ER Diagram include all cardinalities of the relationships using the ER Diagram notation from the lecture. State assumptions made and ensure that those assumptions do not contradict with the coursework requirements. Please list your assumptions as bullet points.
• (i) For each relation, identify their primary and foreign keys. Simply add (PK) and (FK) to indicate the attribute(s) that serve as primary and foreign keys, respectively.

Part 3.2: SQL Statements. Write the SQL statements to build and populate the data warehouse in accordance with your ER Diagram. Note that the police would like to keep the data they have already stored in the crimes2015.sql file; you should ensure that the police does not lose any data as a result of the creation of the data warehouse. Write your SQL statements in the provided template file: warehouse.sql