CMS 706 · Database Systems

Data by nature: quantitative · numeric information that can be measured (weight, height, sales figures, temperature) · qualitative · descriptive information expressing characteristics or attributes (customer feedback, colour of a car, satisfaction level).

Core components of information management (6)

1. Data collection · gathering raw data from sources (forms, sensors, logs)
2. Data storage · saving data in databases, cloud or files
3. Data organization · structuring data into tables, categories or format
4. Data processing · converting raw data into useful information
5. Data retrieval · accessing stored data when needed
6. Data security · protecting stored data from unauthorized access

Objectives of information management (4)

Improved decision making · ensure data accuracy and consistency · enhance efficiency · support business operation.

Real-world examples (3)

Banking systems managing transactions · hospital systems managing patient records · e-commerce platforms tracking orders.

The eight stages of the data lifecycle

Importance of the data lifecycle: ensures data quality · improves data quality and usefulness · supports compliance and security.

How data is used in data science (5 steps)

1. Data is collected · from databases, APIs, sensors
2. Data cleaning · removing errors, duplicates, spaces
3. Data analysis · finding patterns and trends
4. Model building · predictive models, e.g. machine learning
5. Decision making · business insights and strategies

Examples: Netflix recommending movies · TikTok · banks detecting fraud · hospitals predicting disease.
Importance of data: data is the foundation of data science · without data, analysis is impossible.

Data-driven decision making · 6 steps

Data collection → data processing → data analysis → interpretation → decision making → prediction.
Example: sales data shows declining product demand → decision: reduce production or improve marketing.

The three data collection techniques

Use cases of data science, by sector

Healthcare · patient records, disease prediction · Banking · fraud detection, transaction tracking · E-commerce · product recommendation, customer behaviour analysis · Education · student performance prediction, learning analytics · Transportation · traffic prediction, ride sharing.

The two storage methods compared

Information retrieval system

Components (5): data collection · indexing system · query processor · search engine · user interface.
Retrieval process (4): user enters query → system searches indexed data → matches result → returns relevant information.
Types of retrieval: exact match (database query) · best match (search engines).

The four types of index

Benefits of indexing: faster search · improved query performance.
Limitations of indexing: takes extra storage · slows down data updates (every insert or update must maintain the index too).

Worked example from class: search for 25 in {10, 15, 25, 30} · linear search checks 10, then 15, then 25 and stops. An index search would go straight to it.

B-tree:      [20]
            /    \
        [10]   [30, 40]      all leaves on one level

The class table · StudentDB

SELECT Name FROM StudentDB WHERE Age >= 30;

Result:  Rueben
         Eze

Six techniques for efficient queries

1. Use an index · speeds up the WHERE condition
2. Avoid SELECT * · retrieve only the needed columns
3. Use WHERE properly · filter the data at the source
4. Use JOIN efficiently · join Table 1 (Name, MatNo, Age, Dept, Sex) to Table 2 (State, LGA) to get the combined row
5. Limit results · return only the rows you need
6. Use aggregation carefully · e.g. SELECT COUNT(*) FROM Table1

Query optimization concept: the query optimizer chooses the best execution plan · by reducing disk access, by reducing CPU usage, and by using indexes and statistics.

The four sub-languages of SQL LEARN THE TABLE

The core statements, as written in class

SELECT Name, Price
FROM Products
WHERE Category = 'iphones'
ORDER BY Price DESC
LIMIT 10;

INSERT INTO Users (Name, Email)
VALUES ('Ada', '[email protected]');

UPDATE Products
SET Price = Price * 1.1
WHERE Category = 'iphone';

Real-world applications: e-commerce · fast product search using indices · banking · quick transaction retrieval · social media · searching users.

The five key principles of privacy

1. Consent · data should be collected with user permission
2. Purpose limitation · data used only for its intended purpose
3. Data minimization · collect only necessary data
4. Transparency · inform users how data is used
5. Accountability · organizations are responsible for data protection

Types of data: personal (name, age) · financial (bank details) · health records · biometric · academic.
Privacy risks: identity theft · data misuse · unauthorized access.
Regulations: GDPR (Europe) · HIPAA (healthcare) · local data protection laws (Nigeria).

Five data protection techniques

Threats and the controls that answer them

How data masking protects sensitive information: it replaces or hides part of a sensitive value while leaving the record usable · a card number displayed as xxx 1234 still identifies the card to its owner but is useless to anyone who sees it, whether a call-centre agent, a developer testing on real data, or an attacker who obtains the screen. Unlike encryption it is not reversed for the viewer · the hidden digits are simply never shown.

The four components of a database

Why several tables? · the class explanation

Given Table 1 (Age, Name, MatNo), Table 2 (LGA, State), Table 3 (Religion, Country), Table 4 (PA, Sex, Height) · the schema separates the data into tables that are related because common attributes exist in a selected pair of the tables. Because of those common attributes we can combine two or more tables to get the complete data of each student. That is exactly what a JOIN does.

The DBMS

The DBMS is made up of (a) the database, (b) the DBMS itself, (c) the application program · what the user interacts with. Its primary goal is to provide a mechanism where data can be stored and information retrieved from the database.

  User
    ↓
┌───────────────────────────────┐
│  Application Process          │
│ ┌───────────────────────────┐ │      ┌──────────┐
│ │ Transaction Processing    │─┼─────▶│ DB       │
│ │ Data Management           │ │      │ metadata │
│ └───────────────────────────┘ │      └──────────┘
└───────────────────────────────┘  DBS

The seven services of the DBMS

The file-based system was developed in response to industry's need for more efficient data access, but it has three named limitations · this is a standard exam question:

1. Segregation and isolation of data · when data is isolated in separate files it is more difficult to access data that should be available; the difficulty is compounded when data is required from more than two files.
2. Duplication of data · because each department takes a decentralized approach, the FBS encourages uncontrolled duplication, and duplication is wasteful.
3. Data dependency · the physical structure and storage of files and records are defined in the application code, so changes to an existing structure are difficult to make.

Applications of a database (7): purchasing from the supermarket · using credit cards · booking a vacation with a travel agent · a computerized library system · using the internet · taking out insurance · renting a video.

The Book / Distributor relationship example

Book ──── 1:1 ──── Distributor
   given a book, its distributor is determined

Book ──── n:1 ──── Distributor
   what books does this distributor supply?

Book ──── n:n ──── Distributor
   combines both; readable from either side

The notes use this to make one point: the same real-world relationship can be depicted in several ways, and the model you choose decides which questions are cheap to answer.

The hierarchical example from class

                General Manager
        ┌───────────┬────────────┬──────────┐
      Sales     Treasury      Supply    Accounts
      Manager    Manager     Manager    Manager
        └───────────┴────────────┴──────────┘
                   Departments

Note the sentence the notes give for why the hierarchical model fell out of use: many of its limitations result from its overly restrictive view of relationships · a child may have only one parent, which real data often violates. That is precisely the gap the network model's n-n relationship was invented to fill.

The ANSI/SPARC three-level architecture

Used in most RDBMS · Postgres, MySQL, Oracle. Database architecture is how the database is structured · how data flows between its parts; the blueprint for storing, managing and accessing data efficiently.

Types of database architecture: client–server · three-tier.

┌──────────────────────────────┐
│ EXTERNAL   views · reports   │ ← what users see
├──────────────────────────────┤
│ CONCEPTUAL tables · relations│ ← what the DBA designs
├──────────────────────────────┤
│ INTERNAL   files · indices   │ ← how it is stored
└──────────────────────────────┘

The two independences · and why the levels exist

1. Defining data and information in the same words. Data has no meaning on its own; information is processed, meaningful and supports a decision.
2. Answering a scenario question with pure theory. Name the concept, then apply it to the hospital, supermarket or bank you were given.
3. Calling email or XML unstructured. They are semi-structured · they have tags or key–value pairs.
4. Saying indexing has no cost. It takes extra storage and slows down updates.
5. Claiming hashing is good for range queries. It is not · that is B-tree's advantage.
6. Confusing the two independences. Logical = change the logical schema, applications survive; physical = change the storage, the logical schema survives.

7. Mixing up the DBMS services. Concurrency control coordinates simultaneous transactions; recovery management handles failed ones.
8. Saying COMMIT can be undone. COMMIT makes changes permanent; ROLLBACK is what returns the transaction to its original state.
9. Putting DELETE under DDL. DELETE removes rows and is DML; DROP removes the table and is DDL.
10. Writing SQL clauses out of order or without a semicolon.
11. Giving the hierarchical model's n-n relationship. Many-to-many is the network model · hierarchical allows only one parent.
12. Listing three points where the question is worth 8 marks. Marks are per point · write the whole list.