DBMS Exercise – Data Abstraction and Views

DBMS Exercise – Data Abstraction and Views

This exercise covers the concept of data abstraction and views in a DBMS. It includes theoretical and practical SQL questions with solutions.

Question 1: Explain the three levels of data abstraction in a DBMS with examples.

Answer:

  • Physical Level: How data is stored on disk. Example: Employee table stored in disk blocks with indexes.
  • Logical Level: Structure of data (tables, columns, relationships). Example: Employee(Emp_id, Name, Department, Salary).
  • View Level: What end-users see. Example: A view showing only IT employees’ Name and Salary.

Question 2: Create a view for IT employees showing only Name and Salary.

Answer (SQL):

CREATE VIEW IT_Employees AS
SELECT Name, Salary
FROM Employee
WHERE Department='IT';

Question 3: Identify which level of abstraction is visible to different users.

Answer:

  • End-users: View Level
  • Database Administrators (DBAs): Physical Level
  • Application Developers: Logical Level

Question 4: Benefits of using views

Answer:

  • Simplifies data access for end-users.
  • Restricts sensitive data to authorized users.
  • Provides logical independence from physical storage.
  • Enforces consistency and data integrity through abstraction.

Question 5: Using views to restrict HR and IT staff access

Answer:

  • Create a view HR_View showing only Name and Salary for HR staff.
  • Create a view IT_View showing only Emp_id and Department for IT staff.
  • Grant select privileges on each view to the respective users without giving access to the full Employee table.

Question 6: Practical SQL Exercise

Answer (SQL):

-- a) Create Employee table
CREATE TABLE Employee(
    Emp_id INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Department VARCHAR(50) NOT NULL,
    Salary DECIMAL(10,2)
);

-- b) Insert sample records
INSERT INTO Employee VALUES (101, 'Arifa', 'IT', 55000);
INSERT INTO Employee VALUES (102, 'Laboni', 'HR', 40000);
INSERT INTO Employee VALUES (103, 'Nusrat', 'Finance', 48000);
INSERT INTO Employee VALUES (104, 'Tania', 'IT', 60000);

-- c) Create HR view
CREATE VIEW HR_View AS
SELECT Name, Salary
FROM Employee
WHERE Department='HR';

-- d) Query HR view
SELECT * FROM HR_View;

কোন মন্তব্য নেই:

একটি মন্তব্য পোস্ট করুন

এতে সদস্যতা: মন্তব্যসমূহ (Atom)