ER diagrams, database models, and security fundamentals
Grade XII • Computer Science ⏱️ ~25 minBrief Intro — Data Modeling & Database Security
Before a single row of data is stored, engineers use data modeling to create a blueprint of how information is related and organized. These diagrams act as a map for the database, while layered security ensures that the information remains private and protected.
In this activity, you'll master the symbols of the ER model, explore different relationship types, and learn how to defend a database against modern security threats.
Prerequisites
By the end of this activity, you will be able to:
Real-World Application: ER modeling is the first step in building any real-world system — from hospital management to e-commerce. Database security is critical for protecting sensitive data like financial records, health information, and personal identities.
The Entity-Relationship (ER) model provides a visual way to represent the data and relationships that form the backbone of a database.
Peter Chen introduced the Entity-Relationship (ER) Model in 1976. An ER diagram is a blueprint of a database that shows entities (things we store data about), their attributes (properties), and the relationships between them.
ER Diagram Notation Reference:
| Symbol | Represents | Example |
|---|---|---|
| Rectangle | Entity set | Student, College |
| Ellipse / Oval | Attribute | Name, Age |
| Diamond | Relationship set | Studies_In |
| Double Ellipse | Multivalued attribute | Phone numbers |
| Dashed Ellipse | Derived attribute | Age (from DOB) |
| Double Rectangle | Weak entity set | Bank Account |
| Double Line | Total participation | Every College must have a Student |
Mastering the specific types of entities, attributes, and participation rules allows you to build more precise and useful database blueprints.
1.1 — Entities
An entity is an object or concept about which data is stored. It is represented as a rectangle in an ER diagram.
1.2 — Total Participation
Total participation means every entity in an entity set must have at least one relationship in a relationship set. It is shown with a double line in the ER diagram.
Example: In a Student–College relationship, if every College must have at least one Student, then College has total participation.
Activity: If every employee must belong to exactly one department, which entity has total participation? Employee (total participation in the Belongs_To relationship)
1.3 — Attribute Types
Identify the attribute type for each example:
Match the Symbols:
1.4 — Relationship Types
Identify the relationship type for each scenario:
1.5 — ER Diagram Practice
In the ER diagram for Student and College:
Question: What would change if we wanted to show that every student must study in a college? Add a double line from Student to Studies_In (total participation)
The hierarchical model organizes data in a rigid tree structure, representing the earliest attempt at systematic data management.
2.1 — Tree Structure
The hierarchical model was one of the earliest database models. It stores data in a tree-like structure where:
Real-World Example (from notes): Windows Registry is a hierarchical database. Directory & file systems also follow this model.
2.2 — Employee-Computer Example
| Employee Table (Parent) | ||
|---|---|---|
| Emp_Number | Emp_Name | Emp_Salary |
| 1 | Ram | 60000 |
| 2 | Shyam | 15000 |
| 3 | Geeta | 70000 |
| Computer Table (Child) | ||
|---|---|---|
| Serial_Number | Type | Emp_Number (FK) |
| A100001 | PC | 1 |
| B930002 | PC | 2 |
| C101703 | PC | 3 |
The Employee table is the parent; the Computer table is the child. Each computer points to exactly one employee via the foreign key.
2.3 — Disadvantages of Hierarchical Model
The network model improved on earlier designs by allowing more flexible relationships, where a single child can have multiple parents.
3.1 — Improvement Over Hierarchical
The network model was created to address the shortcomings of the hierarchical model. The key improvement: a child can have multiple parents.
3.2 — Advantages
3.3 — Disadvantages
Choosing between a single central location or a distributed network of sites impacts a database's speed, cost, and resilience to failure.
4.1 — Centralized Database
A centralized database is stored, maintained, and managed at a single location. Users from different locations access it via a network (LAN, WAN, Internet).
| Advantages | Disadvantages |
|---|---|
| Easier to access and coordinate data (single location) | Data traffic is higher (all requests go to one place) |
| Minimal data redundancy | Single point of failure — system failure can destroy all data |
| Cheaper to set up and maintain | Harder to scale as the organization grows |
4.2 — Distributed Database
A distributed database consists of multiple databases spread across different physical locations, connected by a computer network. Each location manages its data independently.
| Advantages | Disadvantages |
|---|---|
| Easy to expand (data already spread across locations) | Costly to set up and maintain |
| Accessible from different networks | Complex to manage and administer |
| More secure (no single point of failure) | Difficult to provide a uniform view to all users |
4.3 — Quick Comparison
| Feature | Centralized | Distributed |
|---|---|---|
| Location | Single site | Multiple sites |
| Cost | Cheaper | More expensive |
| Security | Single point of failure | Data spread out, more resilient |
| Scalability | Harder to scale | Easier to expand |
| Data Redundancy | Minimal | Can exist (but managed) |
| Complexity | Simple | Complex |
Database security requires a layered defense strategy to protect sensitive information from human error, software flaws, and malicious attacks.
5.1 — Why Data Security Matters
Database security refers to the tools, controls, and measures designed to establish and preserve database confidentiality, integrity, and availability. A data breach can cause:
5.2 — Common Security Threats
| Threat | Description |
|---|---|
| Insider threats | Malicious insiders (intentional harm), negligent insiders (errors), or infiltrators (outsiders who obtain credentials via phishing). |
| Human error | Accidents, weak passwords, password sharing. Causes nearly 49% of all reported data breaches. |
| Software vulnerabilities | Hackers exploit unpatched flaws in DBMS software. Vendors release security patches regularly. |
| SQL/NoSQL injection | Malicious SQL or NoSQL code inserted into queries served by web applications. |
| Buffer overflow | Writing more data to a memory block than it can hold; excess data in adjacent memory is used to launch attacks. |
| DoS / DDoS attacks | Overwhelming the database server with requests so legitimate users cannot access it. DDoS comes from multiple servers. |
| Malware | Software written to exploit vulnerabilities or cause damage to the database. |
| Attacks on backups | Backup data that is not protected with the same controls as the live database. |
5.3 — Security Solutions (Layered Approach)
Database security extends far beyond the database itself. Consider each layer:
5.4 — Types of Controls
| Control Type | Purpose | Examples |
|---|---|---|
| Administrative | Govern installation, change, and configuration management | Policies, procedures, role assignments |
| Preventative | Stop unauthorized access before it happens | Access controls, encryption, tokenization, masking |
| Detective | Monitor and alert on suspicious activity | Activity monitoring, data loss prevention tools |
1. In an ER diagram, a double rectangle represents:
2. A Customer can place many Orders, but each Order belongs to one Customer. What type of relationship is this?
3. Which database model allows a child to have multiple parents?
4. Which is a disadvantage of a centralized database?
5. Approximately what percentage of all reported data breaches are caused by human error?
What to Expect in the Assessment:
Key Points to Remember:
Ready to test your knowledge?
Take the Assessment →