1

Unit 2: Distributed Database Design and Access Control

Top-Down Design Process, Distribution Design Issues, Fragmentation,

Allocation, Data Directory, View Management, Data Security, Semantic
Integrity Control.

1. Top-Down Design Process

The Top-Down Design Process in distributed databases involves designing the system in a
structured manner, starting from a global conceptual schema and breaking it down into
distributed components.

Steps in the process:

1. Global Conceptual Design: Defining the overall schema of the database.

2. Fragmentation: Dividing the database into smaller logical pieces (fragments).

3. Allocation: Assigning fragments to different sites.

4. Local Conceptual Design: Designing individual site schemas.

5. Physical Design: Optimizing storage, indexing, and query processing for each site.

2. Distribution Design Issues

Key decisions while designing a distributed database:

- How should data be distributed? (Horizontally or vertically)

- Which sites should store which data?

- How to minimize data transfer costs?

- How to maintain consistency across distributed sites?

3. Fragmentation in Distributed Databases

Fragmentation involves dividing a database into smaller, manageable fragments to optimize
performance and availability.

Types of Fragmentation
1. Horizontal Fragmentation: Divides tables into subsets of rows.

- Example: A customer table split by location (USA Customers, EU Customers).

- Benefit: Queries accessing a specific subset of data are faster.

2. Vertical Fragmentation: Divides tables into subsets of columns.

Er. Sital Prasad Mandal 1

2

- Example: Splitting customer data into (CustomerID, Name) and (CustomerID, Address).

- Benefit: Reduces unnecessary data transfer.

3. Hybrid (Mixed) Fragmentation: Combines horizontal and vertical fragmentation.

4. Derived Fragmentation: Fragments based on foreign key relationships.

4. Allocation of Data
After fragmentation, fragments must be allocated to different sites based on:

- Minimizing data transfer costs.

- Ensuring fault tolerance and replication.

- Load balancing to prevent server overload.

Data Allocation Strategies

1. Centralized: All data is stored at a single site.

2. Replicated: Copies of fragments are stored at multiple sites.

3. Partitioned: Different fragments are stored at different sites.

5. Data Directory & View Management

A Data Directory stores metadata about:

- Location of each data fragment.

- Replication information.

- Access permissions for users.

View Management ensures users interact with views rather than physical database tables,
providing data security by restricting access to certain data.

6. Data Security & Semantic Integrity Control

Data Security Mechanisms

1. Authentication: Verifying user identity.

2. Authorization: Granting or restricting access to data.

3. Encryption: Securing data from unauthorized users.

4. Audit Trails: Tracking access history for security monitoring.

Semantic Integrity Control

Ensures data consistency and correctness across distributed sites.

Er. Sital Prasad Mandal 2

3

Example: Preventing a customer from being registered in multiple sites without a unique
identifier.

1. Top-Down Design Process

The Top-Down Design approach starts with a global schema (overall structure of the
database) and breaks it down into smaller fragments that are distributed across different
locations.

Steps in Top-Down Design:

1. Requirement Analysis:
o Identify user needs, transactions, and security requirements.
2. Conceptual Design:
o Create a high-level ER (Entity-Relationship) Model.
3. Logical Design:
o Convert ER model into relational schemas.
4. Distribution Design:
o Decide how to fragment, replicate, and allocate data across sites.
5. Physical Design:
o Define indexing, query optimization, and storage strategies.

Example:

A bank with multiple branches may divide its customer database based on location:

Global Schema (All Customers)

|
--------------------------------
| | |
KTM BTM POK
Database Database Database

2. Distribution Design Issues

While designing a distributed database, the following issues must be addressed:

A. Transparency Issues

• Location Transparency: Users should not need to know where data is stored.
• Replication Transparency: Users should not worry about data copies.

Er. Sital Prasad Mandal 3

4

• Failure Transparency: The system should recover automatically from failures.

B. Performance Issues

• Minimize Data Transfer: Reduce network overhead by optimizing queries.

• Load Balancing: Distribute queries evenly across sites.

C. Security & Integrity Issues

• Ensuring confidentiality and preventing unauthorized access across distributed

sites.

3. Fragmentation

Fragmentation is the process of dividing a database into smaller parts (fragments)

and storing them across multiple locations to optimize performance and accessibility.

Types of Fragmentation:

1. Horizontal Fragmentation:
o Dividing a table by rows based on a condition.
o Example: A customer database is fragmented by city.
2. Customers in Delhi Customers in Mumbai Customers in
Bangalore
3. Vertical Fragmentation:
o Dividing a table by columns to store different attributes at different
locations.
o Example:
o Customer Personal Data (Name, Address, Phone) -> Site A
o Customer Transactions (Account Balance, Payments) -> Site B
4. Hybrid Fragmentation:
o Combination of horizontal and vertical fragmentation.
o Example: Banking data split by branch (horizontal) and further divided
into personal vs transaction data (vertical).

💡 Why Use Fragmentation?

• Improves performance by reducing data transfer.

• Increases availability by distributing workload.
• Enhances security by keeping sensitive data at secure locations.

Er. Sital Prasad Mandal 4

5

4. Allocation

Allocation refers to the placement of fragmented data at different sites based on factors
like access frequency, network cost, and security.

Types of Allocation:

1. Centralized Allocation:
o All data is stored in one location.
o Example: A company's head office database.
2. Replicated Allocation:
o Copies of data are stored at multiple sites for redundancy.
o Example: Cloud storage providers like Google Drive or Dropbox.
3. Partitioned Allocation:
o Different sites store different fragments of data.
o Example: Amazon warehouses store inventory data based on region.

💡 Factors Affecting Allocation:

• Access Frequency: Place data closer to users who access it frequently.

• Storage Costs: Keep less frequently accessed data in cheaper storage.
• Network Traffic: Minimize data transfer between sites.

5. Data Directory

A Data Directory (or Data Catalog) is a centralized or distributed metadata

repository that keeps track of where data is stored.

Functions of a Data Directory:

• Stores information about fragment locations.

• Maintains metadata about schemas, tables, and security policies.
• Ensures efficient query processing and transaction management.

Example:

A global company’s data directory might contain:

Customer Table: Fragmented by Region

- USA Customers -> Stored at New York Data Center

Er. Sital Prasad Mandal 5

6

- Europe Customers -> Stored at London Data Center

- Asia Customers -> Stored at Singapore Data Center

When a user queries customer data, the directory helps fetch data from the correct
location.

6. View Management

A View is a virtual table that provides a specific perspective of data without physically
storing it.

Functions of View Management in DDBMS:

• Controls data visibility (users see only relevant data).

• Simplifies complex queries by presenting a subset of data.
• Enhances security by restricting access to sensitive information.

Example:

A hospital database may have a Doctor's View and a Patient's View:

Doctor’s View (Accessible to doctors)

- Patient Name
- Diagnosis
- Prescriptions

Patient’s View (Accessible to patients)

- Patient Name
- Appointment Date

💡 Benefit: Prevents unauthorized users from accessing confidential medical data.

7. Data Security

Data Security in a distributed database ensures that only authorized users can access,
modify, or transfer data.

Er. Sital Prasad Mandal 6

7

Security Measures in DDBMS:

1. Authentication & Authorization:

o Usernames, passwords, and role-based access to restrict entry.
o Example: Employees at Branch A cannot access Branch B's database.
2. Encryption:
o Data is encrypted during transmission and storage.
o Example: Online banking transactions use encryption.
3. Firewalls & Intrusion Detection Systems (IDS):
o Prevent unauthorized access.
o Example: Banks and government databases use firewalls.

8. Semantic Integrity Control

Semantic Integrity ensures that data follows business rules and maintains consistency
across distributed sites.

Rules Enforced:

1. Domain Integrity:
o Ensures values fall within a valid range.
o Example: Age cannot be negative.
2. Referential Integrity:
o Foreign keys must reference valid primary keys.
o Example: An order cannot reference a non-existent customer.
3. Consistency Rules:
o Data updates should not cause conflicts.
o Example: A bank account balance should never go below zero.

Example:

In a university database:

Student Table: Student_ID (Primary Key)

Course Table: Course_ID (Primary Key), Student_ID (Foreign Key)

💡 If a student is deleted, their course enrollment must also be deleted.

Er. Sital Prasad Mandal 7

8

Conclusion

Key Takeaways from Unit 2:

✅ Top-Down Design creates a global schema and fragments it.

✅ Fragmentation & Allocation improve performance and availability.
✅ Data Directory & View Management ensure efficient access.
✅ Security Measures like encryption & access control protect data.
✅ Semantic Integrity maintains data consistency.

Er. Sital Prasad Mandal 8