M162 - Analysing and Modelling Data

What I Learned

Core competencies and skills developed throughout this module

Data, Information & Knowledge

Learned the basic terms of data processing and why the structure of data determines how easily it can be evaluated.

Distinguishing message, data, information and knowledge
Telling structured, semi-structured and unstructured data apart
Recognising what a dataset can and cannot express
Assessing data quality: completeness, accuracy, consistency

Analysing & Visualising Data in Excel

Explored datasets in Excel to answer concrete questions and presented the results with suitable charts.

Sorting and filtering large tables
Summarising data with formulas and pivot tables
Choosing the right chart type for the message
Presenting findings clearly and without distortion

Entity-Relationship Modelling

Designed conceptual data models with entities, attributes and relationships before any database is created.

Identifying entities and their attributes from a scenario
Defining relationships and cardinalities (1:1, 1:n, m:n)
Drawing ER diagrams, for example in MySQL Workbench
Validating the model against the requirements

The Relational Model & Keys

Translated the ER model into a logical relational model made of tables, rows and columns connected through keys.

Choosing primary keys that uniquely identify each record
Linking tables with foreign keys
Resolving m:n relationships with a junction table
Understanding referential integrity

Data Types & SQL Basics

Picked appropriate data types for every column and got to know the language layers of SQL.

Choosing between INT, DECIMAL, VARCHAR, DATE and BOOLEAN
Defining structures with DDL (CREATE, ALTER, DROP)
Changing data with DML (INSERT, UPDATE, DELETE)
Querying data with SELECT

Normalisation

Normalised tables step by step up to the third normal form to eliminate redundancy and insert, update and delete anomalies.

1NF: atomic values and no repeating groups
2NF: full dependency on the whole primary key
3NF: no transitive dependencies between non-key attributes
Normalising a real example table step by step

Code Examples

Practical examples from the module

                schema.sql
            

-- Logical model of a small school database.
-- The m:n relationship between students and courses
-- is resolved with the junction table enrolment.
CREATE TABLE student (
    student_id  INT          PRIMARY KEY AUTO_INCREMENT,
    first_name  VARCHAR(50)  NOT NULL,
    last_name   VARCHAR(50)  NOT NULL,
    birth_date  DATE
);

CREATE TABLE course (
    course_id   INT          PRIMARY KEY AUTO_INCREMENT,
    title       VARCHAR(100) NOT NULL,
    room        VARCHAR(10)
);

CREATE TABLE enrolment (
    student_id  INT NOT NULL,
    course_id   INT NOT NULL,
    grade       DECIMAL(2,1),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES student(student_id),
    FOREIGN KEY (course_id)  REFERENCES course(course_id)
);
            

                normalisation.sql
            

-- Before (not normalised): one row per order,
-- customer data repeated in every row
-- order_id | customer | city   | products
-- 1        | Meier    | Basel  | Pen, Notebook

-- After 3NF: every table describes exactly one thing
CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    city        VARCHAR(50)
);

CREATE TABLE product (
    product_id  INT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    price       DECIMAL(8,2)
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

CREATE TABLE order_item (
    order_id    INT,
    product_id  INT,
    quantity    INT DEFAULT 1,
    PRIMARY KEY (order_id, product_id)
);
            

Skills Acquired

Technologies and concepts mastered in this module

Data Analysis Microsoft Excel Sorting & Filtering Charts ER Modelling Cardinalities Relational Model Primary & Foreign Keys Data Types SQL Basics Normalisation (1NF–3NF) MySQL Workbench

Key Takeaways

Data needs context: Only once data is interpreted does it become information.
Model before you build: An ER diagram is much cheaper to change than a finished database.
Keys hold everything together: Primary and foreign keys guarantee unique and consistent data.
Redundancy causes errors: Normalisation prevents the same fact from being stored twice.
Choose types deliberately: The right data type is the first line of defence for data quality.