BSc CSIT (TU) Science Database Management System (BSc CSIT, CSC260) Question Paper 2074 Nepal

Database Management System (DBMS)

A Database Management System (DBMS) is a collection of interrelated data together with a set of programs to access, define, manipulate and manage that data. It acts as an interface between the user/application programs and the physical database, providing facilities for data definition, storage, retrieval, security, concurrency control and recovery. Examples: MySQL, Oracle, PostgreSQL, SQL Server.

Three-Schema (ANSI/SPARC) Architecture

The three-schema architecture separates a database into three levels of abstraction to support data independence and to hide complexity from users.

1. Internal (Physical) Level

The lowest level. It describes how the data is physically stored on disk — file organization, indexes, storage allocation, access paths and data structures (B+ trees, hashing). Described by the internal schema.

2. Conceptual (Logical) Level

The middle level. It describes what data is stored and the relationships among the data for the whole database — entities, attributes, constraints, and relationships — without storage details. Described by the conceptual schema. There is exactly one conceptual schema.

3. External (View) Level

The highest level. It describes the part of the database relevant to a particular user/group through user views. Different users can see different external schemas of the same database. Provides security and customization.

  External View 1   External View 2   External View 3   <- External level
            \            |            /
             ---- Conceptual Schema ----                <- Conceptual level
                        |
                  Internal Schema                        <- Internal level
                        |
                Physical Database

Data Independence

Data independence is the capacity to change the schema at one level without altering the schema at the next higher level (and without changing application programs).

• Logical Data Independence: Ability to change the conceptual schema (e.g., add/remove an attribute or entity) without changing external schemas or application programs. Harder to achieve.
• Physical Data Independence: Ability to change the internal schema (e.g., change file organization, add an index) without changing the conceptual schema. Easier to achieve.

The mappings between levels (conceptual/internal and external/conceptual) are what make this independence possible.

Entity-Relationship (ER) Model

The ER model is a high-level conceptual data model proposed by P. P. Chen that describes the structure of a database in terms of entities, their attributes, and the relationships among them. It is represented graphically by an ER diagram and is used during the conceptual design phase, independent of the DBMS used.

ER Diagram for a University Database (described)

Notation: rectangles = entities, ellipses = attributes, diamonds = relationships, lines connect them.

 [STUDENT] ---<Enrolls>--- [COURSE] ---<Taught_by>--- [TEACHER]
     |                        |                            |
  RollNo(key)             CourseID(key)               TeacherID(key)
  Name, Address           Title, Credit               Name, Salary

 [DEPARTMENT] ---<Offers>--- [COURSE]
     |
  DeptID(key), DName

 [STUDENT] ---<Has>=== [DEPENDENT]   (DEPENDENT is a weak entity)

• A STUDENT Enrolls in many COURSEs (M:N).
• A DEPARTMENT Offers many COURSEs (1:N).
• A TEACHER Teaches COURSEs; DEPENDENT is a weak entity identified by STUDENT.

Types of Entities

• Strong (regular) entity: Has its own key attribute and exists independently (e.g., STUDENT). Drawn with a single rectangle.
• Weak entity: Cannot be uniquely identified by its own attributes; depends on an owner/identifying entity (e.g., DEPENDENT depending on STUDENT). Drawn with a double rectangle.

Types of Attributes

• Simple (atomic): cannot be divided, e.g., Age.
• Composite: can be split into sub-parts, e.g., Name → FirstName, LastName.
• Single-valued: one value, e.g., RollNo.
• Multi-valued: multiple values, e.g., PhoneNo (drawn with double ellipse).
• Derived: computed from others, e.g., Age from DOB (drawn with dashed ellipse).
• Key attribute: uniquely identifies an entity (underlined), e.g., RollNo.

Types of Relationships (by cardinality)

• One-to-One (1:1): e.g., a DEPARTMENT has one HEAD.
• One-to-Many (1:N): e.g., a DEPARTMENT offers many COURSEs.
• Many-to-One (N:1): inverse of above.
• Many-to-Many (M:N): e.g., STUDENT enrolls in many COURSEs and each COURSE has many students.

Relationships also have degree (unary, binary, ternary) and participation constraints (total = double line, partial = single line).

Reducing an ER Diagram to Relational Tables

ER-to-relational mapping converts entities, attributes and relationships into a set of relation schemas (tables). The general rules with examples follow.

1. Strong (Regular) Entity

Create a table with all simple attributes; choose the key attribute as the primary key.

STUDENT(RollNo PK, Name, Address)

2. Weak Entity

Create a table including the partial key of the weak entity plus the primary key of the owner entity as a foreign key. The primary key = owner's PK + partial key.

Weak entity DEPENDENT (partial key DName) of STUDENT → DEPENDENT(RollNo FK, DName, Relation, PK = {RollNo, DName})

3. Multi-valued Attribute

Create a separate table containing the attribute and the PK of the owner entity; PK = both columns together.

STUDENT with multi-valued Phone → STUDENT_PHONE(RollNo FK, Phone, PK = {RollNo, Phone})

4. Composite Attribute

Include only the simple component attributes as columns (ignore the composite parent). Name(First,Last) → columns FName, LName in STUDENT.

5. 1:1 Relationship

Add the primary key of one entity as a foreign key in the table of the other (prefer the side with total participation), or merge into a single table.

EMPLOYEE manages DEPARTMENT (1:1) → add MgrEmpID FK into DEPARTMENT.

6. 1:N Relationship

Put the primary key of the entity on the "1" side as a foreign key in the table of the entity on the "N" side. No new table is needed.

DEPARTMENT (1) offers COURSE (N) → COURSE gets DeptID FK: COURSE(CourseID PK, Title, DeptID FK)

7. M:N Relationship

Create a new relationship table containing the primary keys of both participating entities (as foreign keys) plus any relationship attributes. The combined foreign keys form the primary key.

STUDENT enrolls COURSE (M:N), attribute Grade → ENROLLS(RollNo FK, CourseID FK, Grade, PK = {RollNo, CourseID})

8. N-ary (e.g., Ternary) Relationship

Create one table with the PKs of all participating entities as foreign keys; their combination forms the primary key, plus any descriptive attributes.

Summary Table

File System vs DBMS

In short: a file system stores data as isolated, application-dependent files with high redundancy and no central control, whereas a DBMS stores data centrally with reduced redundancy, integrity, security, concurrency control and recovery.

Roles of a Database Administrator (DBA)

The DBA is the person/group responsible for the overall control, management and maintenance of the database system. Key roles:

1. Schema definition: Defines the conceptual schema and creates the database structure using the DDL.
2. Storage structure and access-method definition: Decides physical storage, file organization and indexing for good performance.
3. Granting authorization / security: Creates user accounts and grants/revokes access privileges to control who can access what data.
4. Integrity-constraint specification: Defines and enforces integrity rules to keep data valid and consistent.
5. Backup and recovery: Plans periodic backups and restores the database after failures.
6. Performance monitoring and tuning: Monitors usage, tunes queries/indexes and reorganizes data to maintain efficiency.
7. Concurrency and routine maintenance: Manages concurrent access, applies patches/upgrades and handles day-to-day administration.
8. Schema and physical-organization modification: Changes the design to improve performance or reflect new requirements.

Types of Database Users

1. Database Administrator (DBA): Has central control over the database; defines schema, grants authorization, handles security, backup, recovery and tuning.
2. Naive / End Users: Unsophisticated users who interact through pre-written application programs or simple menus/forms (e.g., a bank teller, railway-reservation clerk). They do not write queries directly.
3. Application Programmers: Computer professionals who write application programs (in host languages with embedded SQL) that the naive users use to interact with the database.
4. Sophisticated Users: Users such as analysts, engineers and scientists who interact directly with the database by writing their own SQL queries or using query tools, without writing full programs.
5. Specialized Users: Sophisticated users who write specialized applications such as CAD systems, expert systems, knowledge bases or systems storing complex data types.

(Some texts also list temporary/casual users who occasionally access the database via a query language.)

Primary Key vs Candidate Key vs Foreign Key

• Candidate Key: A minimal set of attribute(s) that can uniquely identify each tuple in a relation. A relation may have several candidate keys, none of which can be NULL. Example: in STUDENT(RollNo, Email, Name), both RollNo and Email are candidate keys.

• Primary Key: The one candidate key chosen by the designer to be the main identifier of the relation. It is unique, NOT NULL, and there is exactly one primary key per table. Example: RollNo chosen as the primary key of STUDENT. (The remaining candidate keys become alternate keys.)

• Foreign Key: An attribute (or set of attributes) in one table that refers to the primary key of another (or the same) table, used to enforce referential integrity and link tables. It may be NULL and can contain duplicate/repeating values. Example: DeptID in COURSE referencing DeptID in DEPARTMENT.

Weak Entity Set

A weak entity set is an entity set that does not have a primary key (sufficient key attributes) of its own to be uniquely identified. Its existence depends on another entity, called the owner / identifying (strong) entity set, to which it is connected by an identifying relationship.

Key characteristics:

• It has only a partial key (discriminator) that distinguishes its instances within one owner.
• It is uniquely identified by combining the owner's primary key with its partial key.
• It always has total participation in the identifying relationship.
• ER notation: drawn with a double rectangle, the identifying relationship with a double diamond, and the partial key dashed-underlined.

Example: Consider entity EMPLOYEE (PK = EmpID) and DEPENDENT (children/spouse). A dependent's Name is not unique across the company, so DEPENDENT cannot stand alone — it is a weak entity identified by its owner EMPLOYEE.

• Owner: EMPLOYEE(EmpID PK, Name, ...)
• Weak entity: DEPENDENT(EmpID FK, DepName, Relationship, PK = {EmpID, DepName})

Here DepName is the partial key, and the full primary key of DEPENDENT is (EmpID, DepName).

View in SQL

A view is a virtual table whose contents are defined by a stored SQL query. It does not store data physically (except materialized views); instead it derives its rows from one or more base tables each time it is queried.

CREATE VIEW HighPaidEmp AS
SELECT EmpID, Name, Salary
FROM Employee
WHERE Salary > 50000;

The view can then be queried like a table: SELECT * FROM HighPaidEmp;

Advantages of Views

1. Security / access control: Restricts users to specific rows/columns, hiding sensitive data (e.g., hide salary columns).
2. Simplicity: Hides complex joins/queries behind a simple name, simplifying user queries.
3. Logical data independence: Underlying base tables can change while the view interface stays the same for applications.
4. Customization: Different users can be presented different views of the same data.
5. Consistency / reusability: A common, frequently used query is defined once and reused, reducing errors and duplication.

DELETE vs DROP vs TRUNCATE

Examples:

DELETE FROM Student WHERE RollNo = 5;  -- removes one row, can rollback
TRUNCATE TABLE Student;                -- removes all rows, keeps table
DROP TABLE Student;                    -- removes the whole table

Summary: DELETE removes specific rows and is recoverable; TRUNCATE quickly removes all rows but keeps the empty table; DROP deletes the entire table definition along with its data.

Aggregate Functions in SQL

Aggregate functions operate on a set of values (a column or a group of rows) and return a single summary value. They are commonly used with the GROUP BY clause; HAVING filters groups.

Examples (table Employee(EmpID, DeptID, Salary)):

SELECT COUNT(*) FROM Employee;                 -- total employees
SELECT SUM(Salary) FROM Employee;              -- total salary paid
SELECT AVG(Salary) FROM Employee;              -- average salary
SELECT MAX(Salary), MIN(Salary) FROM Employee; -- highest & lowest salary

-- with GROUP BY: average salary per department
SELECT DeptID, AVG(Salary)
FROM Employee
GROUP BY DeptID
HAVING AVG(Salary) > 40000;

Note: Except for COUNT(*), aggregate functions ignore NULL values.

Trigger

A trigger is a special stored procedure that is automatically executed (fired) by the DBMS in response to a specified data-modification event (INSERT, UPDATE, or DELETE) on a table. Triggers are used to enforce complex integrity constraints, business rules, auditing, and automatic value derivation.

Classification:

• By timing: BEFORE or AFTER the event.
• By granularity: ROW-level (per affected row) or STATEMENT-level.

Example: Maintain an audit log whenever a salary is updated.

CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON Employee
FOR EACH ROW
BEGIN
    INSERT INTO SalaryLog(EmpID, OldSalary, NewSalary, ChangedOn)
    VALUES (OLD.EmpID, OLD.Salary, NEW.Salary, NOW());
END;

Here OLD refers to the value before the update and NEW to the value after. Whenever an employee's salary is updated, the trigger automatically records the change — no application code is required.