Week 1: Database System Concepts

Objectives

1. Define what a database is.

2. Understand types of databases and their real-world applications.
3. Compare the database approach and file processing approach.
4. Explore why databases are important in actuarial science.

Introduction

Actuarial science relies heavily on data, lots of it. Whether analyzing insurance risks, pension
schemes, or financial forecasts, actuaries must store, retrieve, and manipulate large datasets
efficiently. That’s where databases come in.

A. Database Definition

A database is an organized collection of data that can be easily accessed, managed, and updated.

Simple Definition: Think of a database like a digital filing cabinet where you store structured
data.

Example: An insurance company stores client data, claims, and premiums in a database.

B. Types of Databases and Applications

Type of Description Actuarial Application

Database
Relational Store data in tables with rows and Client data, claims, premiums
Databases columns
NoSQL Store unstructured or semi-structured Real-time analytics on large
Databases data (e.g., JSON, key-value) volumes of client interaction
Data Store historical data for reporting and Long-term insurance trends and
Warehouses analysis forecasting
Time-Series Designed for tracking changes over Tracking stock prices or interest
Databases time rates over time

C. Database Approach vs. File Processing Approach

File Processing Approach Database Approach

Data is stored in separate files Data is stored centrally in one database
Hard to update and prone to Easy to manage and update
redundancy
Difficult to secure and scale Better security, scaling, and sharing
Fun Fact

If actuaries used Excel alone for 10,000+ clients’ data every day, it would take forever to find
meaningful patterns. A well-designed database makes that lightning fast!

Revision Questions

1. Define a database and give a real-world example.

2. List two advantages of the database approach over file processing.
3. Match the database type with its use case:
i. Time-Series DB, Relational DB, NoSQL DB
ii. Insurance trends, Claims processing, Real-time feedback

Week 2: Database Models and MySQL Basics

Objectives

1. Understand different database models.

2. Identify which models are relevant for actuarial tasks.
3. Learn basic to intermediate MySQL commands.
4. Practice SQL queries with actuarial data examples.

Introduction

Databases aren’t all the same. They follow models, ways to organize data logically. As future
data professionals in insurance and finance, you’ll encounter multiple database models,
especially the relational model, which works hand-in-hand with SQL (Structured Query
Language).

A. Database Models

Model Description Example / Use in Actuarial

Science
Flat Model Simple table structure CSV file of daily sales
Hierarchical Model Tree-like structure Policies under policyholders
Network Model Records can have multiple Many-to-many relationships in
parent/child relationships healthcare policies
Relational Model Uses tables (relations); most Insurance databases using
widely used MySQL
 Object-Oriented Combines data and behavior Modern actuarial software tools
Model using objects
Object/Relational Hybrid of relational and object- Complex simulations or
Model oriented premium models
Semi-structured Data is partially structured, like Customer feedback, app usage
Model XML or JSON data

B. MySQL Basics

1. What is MySQL?

MySQL is an open-source relational database management system (RDBMS) that uses SQL to
manage data.

2. Basic SQL Commands

i. Create a Database

CREATE DATABASE InsuranceData;

ii. Create a Table

CREATE TABLE Clients (

ClientID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
Premium FLOAT
);

iii. Insert Data

INSERT INTO Clients VALUES (1, 'Alice', 30, 12000.00);

iv. Select Data

SELECT * FROM Clients;

 Update Data

UPDATE Clients SET Premium = 12500 WHERE ClientID = 1;

v. Delete Data

DELETE FROM Clients WHERE Age < 25;

3. Intermediate SQL: Conditions and Joins

i. Where Clause
SELECT * FROM Clients WHERE Premium > 10000;

ii. Join Example

Assuming you have a Claims table:

SELECT Clients.Name, Claims.Amount

FROM Clients
JOIN Claims ON Clients.ClientID = Claims.ClientID;

Applications in Actuarial Science

1. Estimating risk levels by querying historical data.

2. Analyzing claim trends and setting premiums.
3. Forecasting using combined datasets (e.g., policy + claims + feedback).

Fun Tip

SQL is like talking to your data:

“Hey database, show me all clients paying more than 10k.”

SELECT * FROM Clients WHERE Premium > 10000;

Revision Questions

1. List any four database models and describe them.

2. Write SQL to create a table called Policies.
3. Explain how JOINs can be used in insurance databases.
4. Identify the best model to store customer-product interactions.

Here is a Week 3 & 4 combined lecture note on Database Systems Concepts and
Architecture, structured in the same way as previous weeks, with objectives, tools, definitions,
tasks, application, and revision questions.

WEEKS 3 & 4: Database Systems Concepts and

Architecture
Learning Objectives

By the end of these two weeks, students should be able to:

 Explain fundamental database system concepts and components.

 Differentiate between types of data models and DBMS architectures.
 Describe how schemas and instances relate to the database lifecycle.
 Identify various DBMS languages, interfaces, and tools.
 Classify different types of DBMSs based on data and architecture models.
Tools and Technologies

 MySQL / MariaDB
 phpMyAdmin
 SQLite (for quick testing)
 XAMPP / WAMP / LAMP
 ER Diagram tools (e.g., dbdiagram.io, Lucidchart)

Key Concepts and Definitions

A. Data Models

 Definition: A data model is a collection of concepts used to describe the structure of a

database, and how data is stored, connected, and manipulated.

Type Description
Conceptual Describes what data is stored (e.g., ER model)
Logical Describes how data is stored in terms of structures like tables
Physical Describes how data is stored in memory or on disk

B. Categories of Data Models

1. High-level (Conceptual): E-R (Entity-Relationship), UML

2. Record-based (Logical): Relational, Hierarchical, Network
3. Low-level (Physical): File structures

C. Schemas, Instances, and Database State

 Schema: The blueprint or design of the database (static).

 Instance: The actual content of the database at a point in time (dynamic).
 State: The current snapshot of the data stored in the DB.

D. DBMS Architecture & Data Independence

 Three-Schema Architecture:
1. External (User views)
2. Conceptual (Logical design)
3. Internal (Physical storage)
 Data Independence:

1. Logical: Change schema without affecting application.

2. Physical: Change storage without affecting logical schema.

E. Database Languages

 DDL (Data Definition Language): Create/alter/drop tables

  DML (Data Manipulation Language): Insert/update/delete/query data
 DCL (Data Control Language): Grant/revoke access
 TCL (Transaction Control Language): Commit/rollback

F. DBMS Interfaces

 Menu-Based: e.g., phpMyAdmin

 Form-Based: Custom data entry forms
 Graphical: Drag-drop UI for schema design
 Command-Line: e.g., MySQL CLI
 Application Program Interface: APIs for developers (e.g., JDBC, PHP PDO)

G. Database System Utilities

 Backup and restore tools

 Performance monitoring
 Data loading tools
 Index creation
 Integrity check utilities

H. Tools, Application Environments, and Communication Facilities

 Tools: phpMyAdmin, DBeaver, MySQL Workbench

 App Environments: Web apps, desktop DBs, cloud-based (Firebase, Supabase)
 Communication: SQL APIs, REST APIs, Database connectors (ODBC, JDBC)

I. Classification of DBMS

Type Description
Centralized Single-site system
Distributed Data stored across multiple locations
Parallel Increases performance with multiple processors
Cloud-based Hosted remotely, accessed via internet
NoSQL For semi-structured/unstructured data (e.g., MongoDB)

Class Tasks

1. Use phpMyAdmin or MySQL CLI to:

o Create a database and table.
o Insert and query sample data using SQL.
2. Design a basic ER diagram for a student management system.
3. Map the diagram to logical schema (relational model).
4. Identify which schema (external, conceptual, internal) matches your setup.

Assignment
Title: Database Concepts Practical Review

Instructions:

1. Explain the difference between schema and instance with examples.

2. Create a 3-table ERD (Entity Relationship Diagram) for a university database.
3. Write SQL DDL and DML statements to:
o Create the tables.
o Insert at least 2 rows per table.
o Query data using SELECT.
4. Classify the type of DBMS (centralized, distributed, etc.) used in your environment (e.g.,
XAMPP = centralized).

Submission Date: Week 5

Application in Real Life

 Banking systems: Use DBMS for transactions, user data, and backups.
 Social media: Store and serve billions of data points (users, messages).
 E-commerce: Store product, customer, and order data using relational and NoSQL
DBMSs.

Revision Questions

1. What is the difference between a data model and a schema?

2. List and explain the three levels of DBMS architecture.
3. Explain logical vs. physical data independence.
4. Name two types of data models and give examples.
5. Differentiate between DDL and DML with one SQL example each.
6. What are the roles of DBMS interfaces like JDBC or PHP PDO?
7. Compare centralized and distributed DBMS with examples.
Database Systems – Week 3 & 4
Topic: Database System Concepts and Architecture

Learning Objectives

By the end of these weeks, students should be able to:

 Understand and differentiate various types of data models and schemas.

 Explain DBMS architecture and the importance of data independence.
 Identify and use different database languages and interfaces.
 Understand and apply database concepts in actuarial science scenarios.

A. Data Models

Definition:
A data model is an abstract way to organize and define how data is connected, stored, and
manipulated within a database.

Examples of data models:

 Relational Model: Data is stored in tables (relations).

Example: A table with columns: ClientID, PolicyType, PremiumAmount, ClaimStatus.
 Hierarchical Model: Data is organized in a tree-like structure.
Example: Insurance company data where one branch (parent) covers multiple policies
(children).
 Network Model: More flexible than hierarchical; uses graphs.
Example: A client can have multiple insurance policies and policies can be linked to
many clients.
 Object-Oriented Model: Stores data as objects (with attributes and methods).
Example: Actuarial risk objects with methods to compute life expectancy or premium.

Application in Actuarial Science:

 Modeling insurance policy structures.

 Storing mortality tables and actuarial models.
 Managing client records and historical claim data.
B. Categories of Data Models

1. Conceptual (High-level): Focuses on user perspective, often using ER diagrams.

o Example: An ER model showing Policyholder, Policy, and Claims entities.
2. Logical (Representational): Maps conceptual models into a logical structure, like
relational schemas.
o Example: Tables with foreign keys linking Policyholders to Claims.
3. Physical (Low-level): Details of how data is stored on disk (e.g., indexes, partitions).
o Application: Optimizing performance of large-scale actuarial simulations.

C. Schemas, Instances, and Database State

 Schema: Structure of the database (constant).

o Example: Table definition for Policy(ID, Type, StartDate, Premium).
 Instance: The actual data at a point in time (changes frequently).
o Example: Policy(1023, "Life", "2023-01-01", 2500).
 Database State: The current contents of the database.

Application:
Actuaries use schemas to organize client data, premium history, risk models, and instances to run
simulations or produce actuarial reports.

D. DBMS Architecture & Data Independence

Three-Schema Architecture:

1. External Level: Custom views for users.

o Example: A view showing only policy and premium for marketing analysts.
2. Conceptual Level: Overall logical structure of the database.
o Example: ER diagram of the whole insurance database.
3. Internal Level: How data is physically stored.

Data Independence:

 Logical Data Independence: Schema change at conceptual level doesn’t affect external
level.
o Example: Adding a PhoneNumber column to Client doesn’t affect marketing
view.
 Physical Data Independence: Changes in internal structure (e.g., indexing) don’t affect
logical structure.

Application in Actuarial Science:

  Enables updating actuarial models or client data structures without disrupting user
dashboards or interfaces.

E. Database Languages

1. DDL (Data Definition Language):

o Used to define structures.
o Commands: CREATE, ALTER, DROP.
o Example: CREATE TABLE MortalityTable( Age INT, MortalityRate FLOAT
);
2. DML (Data Manipulation Language):
o Used to retrieve or modify data.
o Commands: SELECT, INSERT, UPDATE, DELETE.
o Example: SELECT * FROM Clients WHERE Age > 60;
3. DCL (Data Control Language):
o Manages user permissions.
o Commands: GRANT, REVOKE.
4. TCL (Transaction Control Language):
o Manages transactions.
o Commands: COMMIT, ROLLBACK, SAVEPOINT.

Application:
Used to query client data, update risk models, run premium simulations, and ensure secure data
access in actuarial systems.

F. DBMS Interfaces

 Menu-based Interfaces: ATM-like systems for policy updates.

 Form-based Interfaces: Input forms for client data.
 GUI Interfaces: Dashboards with charts for actuaries.
 Natural Language Interfaces: AI tools where actuaries query "Show average premium
by age."
 Command-line Interfaces: SQL consoles for data analysis.

Application in Actuarial Science:

Actuaries use GUIs to analyze trends and command-line SQL for data extraction during financial
modeling.

G. Database System Utilities

  Backup and Recovery: Safeguards actuarial data.
 Data Import/Export Tools: Useful for integrating third-party data (e.g., census or
healthcare data).
 Performance Monitoring: Optimize large simulations or queries.
 Indexing: Speed up access to large mortality or claims datasets.

Example: Indexing Age in MortalityTable improves performance when computing life tables.

H. Tools, Application Environments, and Communication Facilities

 CASE Tools: Help design databases (e.g., ERWin).

 Application Development Environments: Python, R with database connectors.
 Communication Facilities: ODBC, JDBC to connect actuarial models to databases.

Application in Actuarial Science:

 R or Python scripts extract data from DBs to calculate premiums or risk.

 Actuarial modeling tools like Prophet may connect to relational databases for
input/output.

I. Classification of DBMS

1. Based on Data Model:

o Relational: MySQL, PostgreSQL (used in insurance DBs)
o Document-oriented: MongoDB (used for unstructured policy data)
2. Based on Number of Users:
o Single-user: Local actuarial modeling databases.
o Multi-user: Enterprise systems in insurance companies.
3. Based on Sites:
o Centralized: One DB server.
o Distributed: Data spread across branches/regions.
4. Based on Applications:
o OLTP: Daily client transactions.
o OLAP: Actuarial analysis, claims forecasting, and business intelligence.

Summary Table

Concept Description & Example Actuarial Application

Data Model Abstract way of organizing data Policyholder, claims, and premium
(e.g., Relational model) modeling
Schema Database structure (e.g., table Storing actuarial assumptions or
structure) mortality tables
DB Languages SQL commands to define and access Retrieve clients over 65 for risk
data calculation
DBMS Three-level structure: external, Separate user interface from
Architecture conceptual, internal actuarial calculations
Interfaces GUI, form-based, CLI Dashboards for premium forecasts
DBMS Utilities Tools for backup, performance, Ensuring reliability of simulations
indexing and reports
DBMS Relational, distributed, OLTP/OLAP Used in insurance, pension
Classification systems, and reinsurance

Revision Questions

1. What are the different types of data models? Provide examples relevant to insurance.
2. How does physical data independence benefit actuarial model updates?
3. Differentiate between schema and instance using a life insurance policy example.
4. Write an SQL query to retrieve clients aged above 55 who have active claims.
5. Explain how actuarial tools can use DBMS interfaces to perform premium calculations.
Week 5 & 6
Topic: Data Modelling Using Entity-Relationship (ER) Model

Learning Objectives

By the end of these weeks, students should be able to:

 Define and explain the ER model and its components.

 Identify entities, attributes, and relationships in real-world domains.
 Construct ER diagrams using standard conventions.
 Address design issues in ER modeling.
 Extend ER models using Enhanced ER concepts.
 Apply ER modeling to actuarial science scenarios like insurance policy, claims, and
customer data.

A. ER Model

Definition:
The Entity-Relationship (ER) model is a high-level conceptual data model used to define data
elements and relationships for a database system.

Purpose:
To visually represent data requirements and relationships before implementing in a database.

Example (Insurance):
Modeling relationships between Client, Policy, and Claim.

B. ER Concepts – Entities and Attributes

Entities

 Definition: Objects or things in the real world that have an independent existence.
 Types:
o Strong (Regular) Entity: Exists independently.
Example: Policyholder, Claim.
o Weak Entity: Depends on a strong entity.
Example: Beneficiary (depends on Policy).
Attributes

 Definition: Properties that describe an entity.

 Types:
o Simple vs. Composite:
Example: Name (Composite = FirstName + LastName)
o Single-valued vs. Multi-valued:
Example: PhoneNumber may be multi-valued.
o Derived Attribute:
Example: Age derived from DateOfBirth.

Actuarial Science Application:

Entities like Policyholder, Policy, Claim, and ActuarialReport are used in databases to
model and manage actuarial computations.

C. Relationships and Relationship Types

Relationships

 Definition: Associations between entities.

 Example: A Policyholder owns a Policy.

Types of Relationships

1. One-to-One (1:1):
o One Client has one NationalID.
2. One-to-Many (1:N):
o One Client can have multiple Policies.
3. Many-to-Many (M:N):
o Many Clients can be associated with many Agents.

Participation Constraints

 Total Participation: Every entity must participate.

Example: Every Claim must belong to a Policy.
 Partial Participation: Optional.
Example: Not all Policies have Claims.

Degree of Relationship

 Unary: Relationship with itself (e.g., manager supervises employee).

 Binary: Most common (e.g., Client—Policy).
 Ternary: Three entities involved (e.g., Policyholder, Agent, Policy).
Application in Actuarial Science:

 Actuarial systems use relationships to track dependencies like client-beneficiary, policy-

premium-payment, etc.

D. ER Diagrams, Naming Conventions, and Design Issues

ER Diagram Components

 Rectangles: Entities
 Ellipses: Attributes
 Diamonds: Relationships
 Lines: Connectors
 Double rectangles/ellipses: Weak entities/derived attributes

Naming Conventions

 Use singular nouns for entities (Client, not Clients)

 Use meaningful, consistent names for attributes
 Relationship names should reflect actions (owns, files, calculates)

Design Issues

1. Use of Aggregation: For modeling relationships as higher-level entities.

Example: Assessment of a Claim by an Actuary
2. Redundancy Avoidance: Do not duplicate attributes unnecessarily.
3. Generalization/Specialization Decisions
4. Attribute vs. Entity Decision
Example: Store Beneficiary as entity if detailed data is needed.

Application:
In actuarial databases, ER diagrams help design systems to store historical claims data, risk
factors, or client behaviors in a structured and efficient way.

E. Enhanced ER Modelling (EER)

Definition:
An extension of the basic ER model that includes:

 Specialization and Generalization

 Category (Union) Types
 Aggregation
1. Specialization

 Top-down approach.
 Define subclasses from a superclass.
 Example:
Policyholder specialized into IndividualClient and CorporateClient

2. Generalization

 Bottom-up approach.
 Combine common attributes/entities into a superclass.
 Example:
CarInsurance, LifeInsurance → generalized as Policy

3. Aggregation

 Treats relationships as higher-level entities.

 Example:
ClaimAssessment as an aggregation of Policy, Actuary, and Claim

Application in Actuarial Science:

 Use generalization to model various types of insurance products.

 Aggregation helps when modeling complex actuarial assessments involving multiple
entities.

Sample ER Diagram (Actuarial Example)

Entities:

 Client(ClientID, Name, DOB)

 Policy(PolicyID, Type, StartDate, Premium)
 Claim(ClaimID, DateFiled, Amount)
 Actuary(StaffID, Name)

Relationships:

 Client owns Policy (1:N)

 Policy has Claim (1:N)
 Actuary assesses Claim (M:N)

Summary Table
 Concept Description Example in Actuarial Science
Entity Object with independent existence Client, Policy, Claim
Attribute Property of an entity Premium, Age, PolicyType
Relationship Association between entities owns, files, assesses
ER Diagram Visual representation of Mapping Client, Policy, and Claim
entities/relationships
Enhanced Specialization, generalization, Modeling complex policy structures
ER aggregation and assessments

Revision Questions

1. Define an entity and attribute. Give an example relevant to insurance.

2. Differentiate between one-to-many and many-to-many relationships.
3. What is specialization? How is it applied in modeling insurance clients?
4. Construct an ER diagram for an insurance system with clients, policies, and claims.
5. Describe how aggregation can be used in an actuarial assessment system.
🌟 Mid-Module Reflection: Database Systems for Problem
Solving, AI, and Actuarial Applications
🚀 Where You Are Now

By Week 6, you've built a strong foundation in:

 Database architecture and concepts

 Entity-Relationship (ER) modeling
 Schema and data modeling principles
 Understanding DBMS tools, utilities, and interfaces

These skills are not just theoretical — they are core enablers for solving real-world problems in
AI, actuarial science, and business intelligence.

🤖 Applications in Artificial Intelligence (AI)

Modern AI systems depend on structured, clean, and well-modeled data:

 AI models require large, reliable databases for training (e.g., client behavior, risk
patterns).
 Knowledge representation in AI often starts with data models, relationships, and entity
mapping.
 Tools like Knowledge Graphs use ER concepts to model intelligence and context in AI
systems.

✅ Action Plan:

 Learn about SQL for AI workflows (e.g., feeding databases into ML pipelines).
 Explore Neo4j and graph databases for AI-driven relationship discovery.
 Try using Python (Pandas + SQLAlchemy) to connect and query databases in data
science projects.

📊 Applications in Actuarial Science

Databases are at the heart of actuarial systems:

 Store and manage mortality tables, policyholder records, claims data, and risk
indicators.
  Use EER models to capture complexity in products (e.g., life vs general insurance).
 Actuaries query databases for simulations, forecasting, and compliance reporting.

✅ Action Plan:

 Start working with actuarial datasets (public insurance or pension data).

 Practice creating ER diagrams for policy-claim models and client risk profiles.
 Use SQL to compute simple summaries like claim ratios, average premiums, and lapse
rates.

🧠 Problem Solving & Analytical Thinking

Through schema design and ER modeling, you've gained:

 Logical thinking: Identifying entities, attributes, and relationships mirrors solving real-
world problems.
 Data structuring skills: Critical for system design, data migration, and analytics.
 Communication ability: ER diagrams are universal visual tools to communicate system
structure across teams.

✅ Action Plan:

 Take real-life scenarios (e.g., NHIF database, insurance claim workflows) and model
them using ER.
 Collaborate on group case studies to improve teamwork and presentation skills.
 Practice transforming ER models into actual relational schemas and basic queries.

🧭 Research & Job Market Readiness

To become career-ready in tech and actuarial fields, build depth in the following areas:

🔍 Suggested Research Topics:

1. Data Warehousing & OLAP in Insurance

2. NoSQL vs Relational Databases for Big Data
3. Database Security & Privacy (GDPR, HIPAA) for sensitive client data
4. Automation of Data Pipelines for Actuarial Dashboards
5. AI-powered Data Cleaning and Integrity Checks

💼 Job Roles to Explore:

 Junior Database Developer

  Data Analyst (Actuarial or Business Intelligence)
 Actuarial Assistant with SQL knowledge
 AI/Data Science Intern with DBMS exposure
 Risk Data Modeller / Data Quality Analyst

✅ Action Plan:

 Create a LinkedIn project post: “Designed a sample insurance database with ERD + SQL
queries.”
 Attend webinars or tutorials on PostgreSQL, MySQL, or MongoDB.
 Volunteer to join actuarial or tech hackathons focused on data-driven challenges.
 Add "SQL", "ER Modeling", and "Database Design" to your CV and GitHub.

✨ Final Word: Your Database Journey = Career Foundation

From actuarial forecasting to powering AI models, databases are the invisible engines of modern
decision-making. The skills you're developing now are your passport to contributing in finance,
healthtech, insurance, and AI sectors.

🔄 Keep modeling. Keep querying. Keep solving.

📘 Your next mission: design a mini actuarial system with real data and SQL queries.