Data Management I

The route

Lessons are in the order that makes each one easier than the last. Each unit ends with a short quiz.

1. 0/4

   Unit 1

   Information Management Concepts

   Facts are cheap; answers are valuable. How data is captured, represented, stored, indexed, protected and scaled before any database query is written.
   1. Data, Information and Information ManagementFree A shoebox of receipts is data; knowing which customer owes you money is information. 11 min
   3. Information Capture and Representation Garbage in, garbage out, so the first job is getting facts in cleanly and in a shape a computer can use. 11 min
   4. Information Storage, Retrieval and Indexing Finding one customer among twenty million in a blink is not magic; it is file organisation and a good index. 13 min
   5. Information Privacy, Analysis and Scalability Keeping people's data safe, squeezing insight out of it, and staying standing when twenty million users log in on salary day. 12 min
   6. Unit 1 quiz6 questions on this unit 8 min
2. 0/4

   Unit 2 Full plan

   Database Systems and Architecture

   Why one shared database beats a cupboard of separate files, what a DBMS does all day, and how three levels of description keep apps safe from change.
   1. The File-Based Approach and the Database Approach Why every department keeping its own spreadsheet ends with three different phone numbers for the same student. 12 min
   2. Components of a Database System and Functions of a DBMS Hardware, software, data, procedures and people, and the quiet list of jobs the DBMS does for all of them. 12 min
   3. The ANSI/SPARC Three-Schema Architecture and Data Independence Three views of the same data, so the storage can change without breaking a single app. 12 min
   4. Data Models: Hierarchical, Network, Relational and Semi-Structured Trees, webs, tables and self-describing documents, four ways of giving data a shape. 13 min
   5. Unit 2 quiz6 questions on this unit 8 min
3. 0/4

   Unit 3 Full plan

   Entity-Relationship Modelling

   Drawing the world before building the tables, with boxes, diamonds and the lines between them.
   1. Entities, Attributes and Keys in the ER Model Before a single table exists, sketch the things the business cares about and what it needs to know about each. 12 min
   2. Relationship Types, Cardinality Ratios and Participation Constraints The lines between the boxes carry the business rules, such as one doctor many patients, or no ward without a hospital. 12 min
   3. The Enhanced Entity-Relationship (EER) Model Every lecturer and every cleaner is staff, but only one of them has a research area, and the model should know that. 11 min
   4. Mapping an ER Schema to Relations Seven mechanical rules turn every box, diamond and oval into tables, keys and foreign keys. 13 min
   5. Unit 3 quiz6 questions on this unit 8 min
4. 0/4

   Unit 4 Full plan

   The Relational Model and Relational Algebra

   Codd's big idea that everything is a table, the keys that hold tables together, and the small algebra every SQL query secretly becomes.
   1. The Relational Model: Relations, Tuples, Attributes and Domains A table looks ordinary, but in Codd's model it is a mathematical set with strict rules. 11 min
   2. Relational Keys and Integrity Constraints Superkeys, candidate keys, primary keys and foreign keys, and the two rules that stop a database contradicting itself. 13 min
   3. Relational Algebra: Selection, Projection and Rename Three operations that cut a table horizontally, cut it vertically, and give the pieces new names. 12 min
   4. Relational Algebra: Set Operations, Joins and Division Combining two tables is where the real questions get answered, and where the classic slips happen. 14 min
   5. Unit 4 quiz6 questions on this unit 8 min
5. 0/5

   Unit 5 Full plan

   Structured Query Language (SQL)

   Talking to a database in its own language, from creating tables to joins, groups, subqueries and the optimiser that makes them fast.
   1. SQL Data Definition Language (DDL) Before a single row goes in, tell the database exactly what a valid row looks like. 12 min
   2. SQL Data Manipulation: INSERT, UPDATE, DELETE and SELECT Put rows in, change them, take them out, and ask for exactly the ones you want. 13 min
   3. SQL Joins, Aggregate Functions and Grouping Stitch tables back together, then count, total and average whole groups of rows at once. 14 min
   4. Subqueries and Views A query inside a query answers questions in two steps; a view saves a query and lets people use it like a table. 13 min
   5. Query Processing and Optimisation The same question can be answered in a second or in an hour; the optimiser's whole job is to pick the second. 13 min
   6. Unit 5 quiz7 questions on this unit 9 min
6. 0/5

   Unit 6 Full plan

   Normalisation, Security and Transaction Management

   Designing tables that do not contradict themselves, keeping intruders out, and making sure a failed transfer never loses a single naira.
   1. Update Anomalies and Functional Dependencies When one fact is stored in many rows, the table starts to contradict itself, and functional dependencies show exactly why. 13 min
   2. Normalisation: First, Second and Third Normal Forms Three tests applied in order, each one removing a kind of repetition, until every fact lives in exactly one place. 14 min
   3. Boyce-Codd Normal Form and Properties of Decomposition The stricter cousin of 3NF, and the two promises every good split of a table must keep. 12 min
   4. Database Security and Integrity Keep the wrong people out, keep the right people within their limits, and keep the data itself honest. 13 min
   5. Transaction Management, Concurrency Control and Recovery How a bank lets a million people move money at once, survives a power cut mid-transfer, and never loses a naira. 15 min
   6. Unit 6 quiz7 questions on this unit 9 min

Practise and remember