DBMS Solved Answers: File Systems, Three-Schema Architecture, Constraints, ER-to-Relational Mapping and Relational Algebra

Posted on Oct 14, 2026 in Management Assistance

Worked for 36s

DBMS – Solved Answers

1. File Processing System vs DBMS

Limitations of File Processing System

1. Data redundancy and inconsistency
2. Difficulty in accessing data
3. Data isolation
4. Integrity problems
5. Security problems
6. Concurrent-access problems
7. Difficult backup and recovery

2(a). Three-Schema Architecture

The three levels are:

1. External/View Level
   • Highest level.
   • Provides different user views of the database.
   • Hides unnecessary details.
2. Conceptual/Logical Level
   • Describes the complete logical structure of the database.
   • Contains entities, relationships, constraints, etc.
   • Independent of physical storage.
3. Internal/Physical Level
   • Lowest level.
   • Describes how data is physically stored: files, indexes, blocks, etc.

Data Independence

Physical data independence: Changes in physical storage do not affect the conceptual schema.
Example: changing from sequential files to indexed files.

Logical data independence: Changes in the conceptual schema do not affect external views/programs.
Example: adding a new attribute to a relation without changing existing user views.

Structure

External Views → Conceptual Schema → Internal Schema → Physical Storage

2(b). Constraints in Database Design

(i) Cardinality Ratio / Mapping Cardinality

Specifies how many entities of one set can be associated with entities of another.

• 1:1 — One employee manages one department.
• 1:N — One department has many employees.
• M:N — Many students enroll in many courses.

(ii) Participation Constraint

Specifies whether participation in a relationship is mandatory.

• Total participation: Every entity must participate.
• Partial participation: Participation is optional.

Example: Employee ── Works_For ── Department. If every employee must belong to a department → total participation of Employee.

(iii) Integrity Constraints

These maintain correctness and consistency of data.

• Domain constraint: Attribute values must belong to a valid domain.
• Key constraint: Primary key must be unique.
• Entity integrity: Primary key cannot be NULL.
• Referential integrity: Foreign key must refer to an existing primary-key value.

3. ER Diagram → Relational Model

Relations

• P(P1, P2)
  PK: P1
• M(M1, M2, M3, P1)
  PK: M1
  FK: P1 → P(P1)

The relationship R1 is 1:N and has no attributes, so it can be represented by adding P1 as a foreign key in M.

For the weak entity N:

• N(P1, N1, N2)
  PK: (P1, N1)
  FK: P1 → P(P1)

N1 is the partial key of weak entity N.

Thus the minimum number of relations = 3.

4(a). Relational Algebra

Given Relations

Department(DeptID, DeptName, Location)

Employee(EmpID, EmpName, Department, Salary)

(a) Employees with salary > 70,000, displaying EmpID and EmpName

$$\pi_{EmpID,EmpName}(\sigma_{Salary>70000}(Employee))$$

Result: Charlie, Diana.

(b) Employees working in departments located in New York

$$\pi_{EmpName}\left(\sigma_{Location=’New\ York’}(Employee \bowtie_{Employee.Department=Department.DeptName} Department)\right)$$

Result: Alice.

(c) Employees in IT but not HR

$$\pi_{EmpName}(\sigma_{Department=’IT’}(Employee)) – \pi_{EmpName}(\sigma_{Department=’HR’}(Employee))$$

Result: Bob, Charlie.

(d) Employee–Department combinations where department names match

$$Employee \bowtie_{Employee.Department=Department.DeptName} Department$$

Result: Alice–HR, Bob–IT, Charlie–IT, Diana–Marketing.

4(b). Relational Algebra

Schema

• Author(Authorname, citizenship, birthyear)
• Book(isbn, title, authorname)
• Topic(isbn, subject)
• Branch(isbn, city)
• Instock(isbn, libname, quantity)

(a) Authors born after 1940

$$\sigma_{birthyear>1940}(Author)$$

(b) Names of libraries in Sydney

$$\pi_{libname}\left(\sigma_{city=’Sydney’}(Branch)\right)$$

(c) Titles of books written by Smith

$$\pi_{title}\left(\sigma_{Authorname=’Smith’}(Book)\right)$$

(d) Titles of books whose subject is DBMS or OS

$$\pi_{title}\left(Book \bowtie_{Book.isbn=Topic.isbn} \sigma_{subject=’DBMS’ \lor subject=’OS’}(Topic)\right)$$

These are the exam-ready solutions to all the visible questions in the uploaded paper.