Activity 1.
Hierarchical Diagram
```
School
┌───────────┼───────────┐
│ │ │
Department A Department B Department C
│ │ │
┌─────┼─────┐ ┌───┴───┐ ┌─┴─┐
│ │ │ │ │ │ │
Teacher1 Teacher2 Teacher3 Teacher4 Teacher5
│ │ │ │ │
ClassA ClassB ClassC ClassD ClassE
```
**Explanation of the structure:**
- **School** is the root (top-level parent)
- **Departments** are direct children of School (one-to-many)
- **Teachers** are children of Departments (one department can have many teachers)
- **Classes** are children of Teachers (one teacher can have many classes, but each class has only one
teacher)
### 2. Two advantages of using a hierarchical data model in this school
1. **Clear and natural parent-child organization**
The structure matches the real-world administrative hierarchy of the school (School → Departments →
Teachers → Classes), making it intuitive to understand and easy to navigate.
2. **Efficient for queries that follow the hierarchy**
Questions like “Which classes does a particular teacher teach?”, “Which teachers belong to the Science
Department?”, or “How many classes are there in the whole school?” can be answered very quickly and
efficiently by traversing only the relevant branches.
### 3. One limitation of this model in a real school environment
**Lack of flexibility / difficulty handling many-to-many relationships**
In a real school, many classes are taught by more than one teacher (e.g. team teaching, subject
teachers, or practical classes with lab assistants). The strict one-to-many rule (each class belongs to only
one teacher) makes it very difficult or impossible to accurately represent this common situation without
duplicating data or breaking the model.
Other common real-school examples that are hard to model include:
- A student belonging to multiple subjects/classes
- A teacher temporarily teaching in another department
These situations usually require a different model (network or relational) to handle them properly.
Activity 2:
Here is a clear and structured answer to your question:
### 1. Explain why a hierarchical model is not suitable for this scenario
A **hierarchical model** organizes data in a strict **tree structure** with one-to-many (parent-to-
child) relationships only, and each child record can have **only one parent**.
In this manufacturing company scenario, the relationships are **many-to-many**, not one-to-many:
- One **employee** can work on **multiple projects** at the same time
- One **project** can involve **multiple employees**
- One **project** can belong to **multiple departments**
- One **department** can be responsible for **multiple projects**
In a hierarchical model, it would be impossible to represent these relationships naturally without:
- **Data duplication** (e.g. duplicating employee records under every project they work on)
- **Artificial intermediate levels** that do not reflect reality
- **Very difficult or impossible queries** (e.g. “List all employees working on Project X” or “Which
projects does Department A share with Department B?”)
Because of these **many-to-many relationships** on multiple sides, a hierarchical (tree) structure is
**not suitable** — it becomes very inefficient, redundant, and rigid.
### 2. Network data model diagram
The **network model** (also called CODASYL model) allows **many-to-many relationships** using
owner-member sets and supports multiple parents for the same record.
Here is a simplified **network data model** diagram for Employees, Projects, and Departments:
```
┌──────────────┐
│ DEPARTMENT │
└───────┬──────┘
(manages / is managed by)
┌──────────┼──────────┐
│ │ │
┌─────────────┐ ┌─────────────┐ ┌─────────────┐
│ PROJECT P1 │ │ PROJECT P2 │ │ PROJECT P3 │
└──────┬──────┘ └──────┬──────┘ └──────┬──────┘
│ │ │
│ (works on / has workers) │
│ │ │
┌──────┼──────┐ ┌────────┼────────┐ ┌──────┼──────┐
│ Emp │ Emp │ │ Emp │ Emp │ Emp│ │ Emp │ Emp │
│ E01 │ E03 │ │ E02 │ E03 │ E05│ │ E04 │ E06 │
└──────┴──────┘ └────────┴────────┘ └──────┴──────┘
▲ ▲ ▲
│ │ │
(belongs to) (belongs to) (belongs to)
│ │ │
┌─────────────┐ ┌─────────────┐ ┌─────────────┐
│ EMPLOYEE │◄─┼─ EMPLOYEE │◄─┼─ EMPLOYEE │
│ E01 │ │ E02 │ │ E03 │
└─────────────┘ └─────────────┘ └─────────────┘
▲ ▲ ▲
└───────┬──────────┴──────────┬───────┘
│ │
└───────► many-to-many relationships ◄───────┘
```
**Text summary of relationships:**
- **DEPARTMENT → PROJECT** : many-to-many
(a project can be managed by / belong to multiple departments)
- **PROJECT → EMPLOYEE** : many-to-many
(a project has many employees, an employee works on many projects)
In network model terms, we usually define **sets**:
- Set: DEPARTMENT – manages – PROJECT
- Set: PROJECT – has workers – EMPLOYEE
- Set: EMPLOYEE – works on – PROJECT (inverse relationship)
### 3. Two advantages of using the network data model here
1. **Naturally supports many-to-many relationships**
The network model can directly represent that one employee works on multiple projects and one
project involves multiple employees — without duplication or artificial restructuring. This matches the
real-world situation in the company very closely.
2. **Efficient navigation and query performance for complex relationships**
Using owner-member sets and pointers, the model allows fast traversal in multiple directions, for
example:
- Find all employees working on a specific project
- Find all projects an employee is assigned to
- Find all departments involved in a project
These kinds of queries are much more efficient than in a hierarchical model and often faster than early
relational systems without proper indexes.
Activity 3:
Here is a clear and structured answer to your question about the **Relational Data Model** in the
hospital context:
### 1. Identify the primary key in each table
- **PATIENT**
Primary Key: **PatientID**
(Uniquely identifies each patient)
- **VISIT**
Primary Key: **VisitID**
(Uniquely identifies each hospital visit)
- **BILL**
Primary Key: **BillID**
(Uniquely identifies each bill)
### 2. Identify the foreign key(s)
- **PATIENT** table: No foreign keys
- **VISIT** table:
Foreign Key: **PatientID**
(References **PatientID** in the **PATIENT** table)
- **BILL** table:
Foreign Key: **PatientID**
(References **PatientID** in the **PATIENT** table)
These foreign keys create relationships:
- One **Patient** → many **Visits**
- One **Patient** → many **Bills**
### 3. Explain how the relational data model reduces data redundancy in this hospital
The relational model reduces data redundancy through **normalization** and proper use of **primary
keys** and **foreign keys**.
**How it works in this hospital example:**
- Patient details (Name, Gender) are stored **only once** in the **PATIENT** table, under a unique
**PatientID**.
- Instead of repeating the patient’s name and gender in every visit record and every bill record, the
**VISIT** and **BILL** tables only store the **PatientID** (a short number or code).
- This means:
- If a patient changes their name (e.g., due to marriage), you update it **only once** in the PATIENT
table — not in every visit or bill record.
- If a patient has 20 visits and 15 bills, their name and gender are **not duplicated 35 times**.
**Result:**
→ Much less storage space is used
→ Fewer chances of inconsistent data (e.g., "John Ade" in one record and "J. Ade" in another)
→ Easier to maintain and update information
This elimination of repeated data is the main goal of **normalization** (especially 2NF and 3NF) in the
relational model.
### 4. State two reasons why this model is widely used in industry
1. **Strong data integrity and consistency**
- Primary keys ensure every record is unique
- Foreign keys enforce valid relationships (referential integrity)
- You cannot accidentally create a bill or visit for a non-existent patient
→ This is critical in industries like healthcare, banking, and inventory where accuracy and correctness
are essential.
2. **Flexibility and ability to answer complex questions easily**
- You can combine data from multiple tables using **JOIN** operations
- Examples of powerful queries:
- Total amount spent by each patient
- Number of visits per patient in the last 6 months
- List of patients who visited more than 5 times and their total bills
- The same database structure supports many different reports and analytics without changing the
data storage design.
**Bonus reason (often mentioned):**
The relational model has very mature tools, languages (SQL), and systems (MySQL, PostgreSQL, Oracle,
SQL Server) that are well understood, reliable, and supported worldwide.
Activity 4:
**Most suitable data model for a hospital: Relational Data Model**
### Justification (two clear reasons)
1. **Excellent support for normalization and elimination of data redundancy**
A patient’s personal details (name, date of birth, address, phone, etc.) are stored **only once** in the
PATIENT table. Every visit, diagnosis, prescription, lab result, or bill simply stores the PatientID as a
foreign key.
→ If the patient moves house or changes phone number, you update it in **one place only**.
→ This is critical in healthcare where the same patient can have hundreds of records over years.
2. **Powerful and flexible querying of complex relationships**
Hospitals constantly need to answer questions such as:
- “Show all visits, diagnoses and total bill amount for patient XYZ in the last 12 months”
- “Which doctors treated the most patients with diabetes last quarter?”
- “List patients who had surgery but no follow-up visit within 30 days”
The relational model handles these easily with **JOINs**, **GROUP BY**, and **aggregate
functions**. No other classic data model makes such ad-hoc, multi-table queries as natural and efficient.
### Least suitable data model: Hierarchical Data Model
**Reason:**
Hospitals are full of **many-to-many relationships** that a strict tree (one-parent-only) structure
cannot represent without severe problems:
- One patient can be treated by many doctors → one doctor treats many patients
- One patient can have many diagnoses and many treatments
- One ward can contain patients from many departments
- One medication can be prescribed to many patients, and one patient can receive many medications
In a hierarchical model you would be forced to either:
- Duplicate entire patient records under every doctor/treatment (massive redundancy and update
nightmares), or
- Create artificial intermediate levels that do not reflect reality
This makes the model extremely rigid, error-prone, and almost unusable for real hospital operations.
(For comparison: The **Network model** could technically handle the many-to-many links, but it is far
more complicated to design, navigate, and maintain than the relational model, which is why virtually
every modern hospital information system uses relational databases.)
**Summary**
→ **Relational** is the clear winner for hospitals because it balances structure, flexibility, integrity, and
ease of querying.
→ **Hierarchical** is the worst fit because the real-world relationships in healthcare are simply too
interconnected for a tree structure.
Activity 5:
**Industry chosen: Telecom**
**1. Most suitable data model: Relational Data Model**
**2. Justification (two clear reasons)**
- **Handles enormous volumes of transactional data with excellent integrity**
A telecom company generates millions or even billions of records every day (call detail records, SMS
logs, data usage sessions, roaming events, etc.). The relational model, using tables, primary keys, foreign
keys and normalization, stores each piece of data exactly once while enforcing rules such as “a customer
cannot be charged twice for the same call”. This guarantees billing accuracy and prevents revenue
leakage — something that is business-critical in telecom.
- **Supports complex, ad-hoc queries and analytics very easily**
Business users constantly ask questions such as:
• “Which customers on the 10 GB plan used more than 12 GB last month?”
• “What is the average revenue per user (ARPU) by region and plan type?”
• “List customers who have not made any outgoing calls in 60 days (churn risk)?”
All of these are straightforward with SQL JOINs, GROUP BY, sub-queries, and window functions —
without redesigning the database.
**3. Least suitable data model: Hierarchical Data Model**
**Why it fails badly in telecom:**
Telecom is full of **many-to-many relationships** that a strict parent–child tree cannot represent
cleanly:
- One customer can have many phone numbers, many plans, many devices
- One plan can be used by many customers
- One cell tower can serve many customers at the same time
- One customer can roam across many networks/countries
If you tried to force this into a hierarchical structure you would have to:
- Duplicate entire customer records under every plan/device they own (massive redundancy)
- Or create artificial “intermediate” nodes that do not exist in reality
- Make simple queries (e.g. “Show all active plans for customer XYZ”) extremely slow or impossible
without traversing multiple unrelated branches
That is why **no modern telecom operator** uses a hierarchical database for its core systems. The
model is simply too rigid for the highly interconnected nature of telecom data.
**Summary**
**Relational** wins because it combines rock-solid data integrity, massive scalability, and the flexibility
needed for both operational billing and advanced analytics.
**Hierarchical** is the worst possible choice because the real-world relationships in telecom are
fundamentally many-to-many, not tree-like.