Prerequisites
- Familiarity with basic database concepts (data, information, DBMS, tables).
- Understanding of primary keys and foreign keys.
- Basic knowledge of ER diagram symbols (from the DBMS Concepts activity).
Objectives Learning Goals & Outcomes
By the end of this activity, you will be able to:
- Describe entities, attributes, and their sub-types in ER diagrams.
- Differentiate between relationship types (1:1, 1:M, M:1, M:N) with real-world examples.
- Read and interpret ER diagram notation symbols.
- Explain the hierarchical and network database models and their trade-offs.
- Compare centralized vs. distributed databases with advantages and disadvantages.
- Identify major database security threats and describe layered security solutions.
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.
Concept Overview ER Model at a Glance
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 |
Part 1 ER Model Deep Dive
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.
- Regular entity: Has its own attributes and can be uniquely identified. Example: Student (with Stu_Id, Stu_Name, Stu_Addr).
- Weak entity: Cannot be uniquely identified by its own attributes alone — it depends on a relationship with another entity. Represented by a double rectangle. Example: A Bank Account cannot be uniquely identified without knowing which bank it belongs to.
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:
- Key attribute — uniquely identifies an entity; represented by an underlined oval. Example: Stu_Id uniquely identifies a student.
- Composite attribute — an attribute composed of sub-attributes. Example: Address is made up of Pin Code, State, and Country.
- Multivalued attribute — can hold more than one value; represented by a double oval. Example: A person can have multiple Phone Numbers.
- Derived attribute — value is calculated from another attribute; represented by a dashed oval. Example: Age is derived from Date of Birth.
Match the Symbols:
- Underlined oval = Key attribute
- Oval with sub-ovals = Composite attribute
- Double oval = Multivalued attribute
- Dashed oval = Derived attribute
1.4 — Relationship Types
Identify the relationship type for each scenario:
- One-to-One (1:1): A Person has one Passport, and a Passport belongs to one Person.
1:1
- One-to-Many (1:M): A Customer can place many Orders, but an Order belongs to one Customer.
1:M
- Many-to-One (M:1): Many Students study in one College, but a Student cannot study in multiple Colleges.
M:1
- Many-to-Many (M:N): Students can work on many Projects, and a Project can have many Students.
M:N
1.5 — ER Diagram Practice
In the ER diagram for Student and College:
- Entities: Student (rectangle), College (rectangle)
- Relationship: Studies_In (diamond) — M:1 (many students in one college)
- Student attributes: Stu_Id (key, underlined), Stu_Name, Stu_Addr
- College attributes: Col_ID (key, underlined), Col_Name
- Line types: Single lines connect attributes to entities; a line connects each entity to the diamond
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)
Part 2 Hierarchical Database Model
2.1 — Tree Structure
The hierarchical model was one of the earliest database models. It stores data in a tree-like structure where:
- A parent node can have multiple children.
- A child node has exactly one parent.
- Records are the nodes; fields are the branches.
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
- Rigid structure: If a new node or relationship needs to be added, it may disrupt the entire existing structure.
- Cannot show Many-to-Many relationships easily (a child has only one parent).
- Limited flexibility: Adding new relationships requires restructuring the tree.
Part 3 Network Database Model
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.
- Parent nodes are called owners.
- Child nodes are called members.
- Example: D2 has two owners (C1 and C2); C3 has two owners (B1 and B2).
3.2 — Advantages
- Supports Many-to-Many (M:N) relationships — unlike the hierarchical model.
- More flexible data representation than the hierarchical model.
- Can model complex real-world relationships more naturally.
3.3 — Disadvantages
- Complex: Much more complicated than the hierarchical model; difficult to handle and maintain.
- Flexibility problems: Not all relationships can be cleanly represented as owner-member pairs.
- Programmer burden: The developer must understand the structure well to implement or modify it.
Part 4 Centralized vs. Distributed Database
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 |
Part 5 Database Security
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:
- Compromised intellectual property: Trade secrets and proprietary information may be stolen, destroying competitive advantage.
- Damage to brand reputation: Customers lose trust if their data is not protected.
- Business continuity disruption: Some businesses cannot operate until the breach is resolved.
- Fines and penalties: Non-compliance with data privacy regulations can result in millions of dollars in fines.
- Breach repair costs: Forensic investigation, crisis management, system repair, and customer notification are all expensive.
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:
- Physical Physical security: Secure, climate-controlled environment for servers (or rely on cloud provider).
- Network Administrative & network access controls: Minimum necessary access for users and network permissions.
- User End user account & device security: Monitor who accesses the database; secure all connecting devices.
- Encrypt Encryption: Protect all data at rest and in transit using strong encryption.
- Patch Database software security: Use the latest version and apply all patches immediately.
- App Application / web server security: Ongoing security testing for any application that interacts with the database.
- Backup Backup security: Apply the same (or stronger) security controls to backups as to the live database.
- Audit Auditing: Record all logins, log operations on sensitive data, and perform regular security audits.
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 |
Challenge Quiz: Test Your Knowledge
1. In an ER diagram, a double rectangle represents:
- Regular entity
- Weak entity
- Multivalued attribute
- Total participation
Show answer
Weak entity — an entity that cannot be uniquely identified by its own attributes and depends on another entity.
2. A Customer can place many Orders, but each Order belongs to one Customer. What type of relationship is this?
- One-to-One (1:1)
- One-to-Many (1:M)
- Many-to-One (M:1)
- Many-to-Many (M:N)
Show answer
One-to-Many (1:M) — one Customer to many Orders.
3. Which database model allows a child to have multiple parents?
- Hierarchical model
- Network model
- Flat file model
- Relational model
Show answer
Network model — it was designed to overcome the hierarchical model's limitation of single-parent children.
4. Which is a disadvantage of a centralized database?
- Harder to expand
- Higher data redundancy
- Less secure (single point of failure)
- More costly than distributed
Show answer
Less secure (single point of failure) — a system failure at the central location can destroy all data.
5. Approximately what percentage of all reported data breaches are caused by human error?
Show answer
49% — accidents, weak passwords, and password sharing are major contributors.
Assessment Prep Ready for the Interactive Assessment
What to Expect in the Assessment:
- Identify entity types (regular vs. weak) and attribute types (key, composite, multivalued, derived) in ER diagrams.
- Classify relationship types (1:1, 1:M, M:1, M:N) using real-world scenarios.
- Read and interpret ER diagram notation symbols correctly.
- Explain the hierarchical and network models, including their advantages and disadvantages.
- Compare centralized and distributed databases with clear reasoning.
- List database security threats and describe layered security solutions.
Key Points to Remember:
- A weak entity (double rectangle) depends on another entity for identification.
- Derived attributes (dashed oval) change over time and are calculated from other attributes.
- The hierarchical model is tree-based (one parent per child); the network model allows multiple parents.
- Centralized = single location; Distributed = multiple locations, more resilient.
- Security is layered: physical, network, user, encryption, patching, application, backup, and auditing.
- Human error causes 49% of data breaches — strong passwords and training matter.