Database Management System

Definition of Database Language

A database language is a specialized computer language provided by a Database Management System (DBMS) that allows users, application programmers, and administrators to specify database schemas, express queries, manipulate stored data instances, and enforce security authorizations.

The primary database language in modern relational systems is SQL (Structured Query Language), which encompasses sub-languages including:

• DDL (Data Definition Language): For defining schema structures (CREATE, ALTER, DROP).
• DML (Data Manipulation Language): For data retrieval and manipulation (SELECT, INSERT, UPDATE, DELETE).
• DCL (Data Control Language): For privilege management (GRANT, REVOKE).
• TCL (Transaction Control Language): For managing transaction states (COMMIT, ROLLBACK).

SQL Statement for UPDATE Operation

The SQL UPDATE statement is a Data Manipulation Language (DML) command used to modify existing data values in one or more columns of a table based on an optional filtering condition.

General Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Concrete Example:

-- Increase the salary of employees in department 10 by 10%
UPDATE Employee
SET salary = salary * 1.10
WHERE dept_id = 10;

Note: If the WHERE clause is omitted, all rows across the entire table will be updated.

Unary Relationship (Recursive Relationship)

A unary relationship (also known as a recursive relationship) is a relationship type in which the same entity set participates more than once in distinct roles.

       +------------------+
       |     EMPLOYEE     |
       +--------+---------+
          |           ^
 (subordinate)     (manager)
          |           |
          v           |
       +--------+---------+
       |     Manages      |
       +------------------+

Real-World Examples:

1. Employee Management: An Employee manages other Employees (Role 1: Manager, Role 2: Subordinate).
2. Course Prerequisites: A Course requires another Course as a prerequisite (Role 1: Main Course, Role 2: Prerequisite Course).
3. Bill of Materials: A Part contains other component Parts.

Four Types of Attributes in ER Model

In the Entity-Relationship (ER) model, attributes represent the properties or characteristics describing an entity set:

1. Simple (Atomic) Attribute: An attribute that cannot be divided into sub-parts (e.g., Age, Salary, Gender).
2. Composite Attribute: An attribute composed of multiple sub-attributes, each with its own semantic meaning (e.g., Name composed of First_Name, Middle_Name, and Last_Name).
3. Multivalued Attribute: An attribute that can hold more than one value for a single entity instance (e.g., Phone_Numbers, Degrees; represented by a double oval).
4. Derived Attribute: An attribute whose value is computed dynamically from other stored attributes or system variables (e.g., Age derived from Date_of_Birth; represented by a dashed oval).

Definition of Serializable Schedule

A concurrent execution schedule $S$ of multiple transactions is called a serializable schedule if its outcome (the final database state) is computationally equivalent to the outcome produced by some purely serial schedule $S'$ of the same transactions (where transactions execute strictly one after another without interleaving).

Serializability is the formal criterion of correctness for concurrent transaction processing:

• Conflict Serializability: The schedule can be transformed into a serial schedule by swapping non-conflicting adjacent operations. Tested via a cycle-free precedence (serialization) graph.
• View Serializability: The schedule produces the same final state and read/write dependencies as a serial schedule.

What is Big Data?

Big Data refers to datasets whose size, velocity of generation, and diversity of structure exceed the processing, storage, and querying capabilities of conventional relational database management systems (RDBMS) and standard analytical software tools.

It is formally defined by the 5 V’s Framework:

1. Volume: Immense data magnitude (petabytes to exabytes).
2. Velocity: Streaming data generation at real-time speeds.
3. Variety: Heterogeneous formats (structured tables, semi-structured JSON, unstructured video/text).
4. Veracity: Data cleanliness, noise, and authenticity.
5. Value: Actionable insights extracted through advanced analytics.

Deferred Data Update (NO-UNDO / REDO)

Deferred data update is a log-based database recovery technique in which all update operations of an active transaction are written only to the log file (in memory and disk) while the transaction is executing, and no modifications are written to the physical database on disk until the transaction successfully reaches its commit point.

Recovery Implication:

• NO-UNDO: Since uncommitted updates are never flushed to the physical database on disk, if a transaction aborts or the system crashes, no undo operations are necessary.
• REDO: Any transaction that committed before the crash must have its operations redone (REDO) from the log to ensure changes reach non-volatile physical storage.

Importance of Creating Views

A View is a virtual table defined by an underlying SQL query that provides powerful software engineering benefits:

1. Data Security & Row/Column Access Control: Hides sensitive columns (e.g., credit card numbers, passwords, salaries) from unauthorized users by exposing only permissible slices of tables.
2. Query Simplification: Encapsulates complex multi-table JOINs, GROUP BY aggregations, and subqueries into a simple virtual table query.
3. Logical Data Independence: Shields client applications from structural changes in underlying physical base tables.
4. Customized Data Representation: Provides tailored presentations and renamed columns for specific organizational user departments.

Third Normal Form (3NF)

A relation schema $R$ is in Third Normal Form (3NF) if:

1. It is already in Second Normal Form (2NF).
2. No non-prime attribute is transitively dependent on any candidate key of $R$.

Formal Definition (Codd / Date):

A relation schema $R$ is in 3NF if, for every non-trivial functional dependency $X \to Y$ that holds on $R$, at least one of the following conditions is satisfied:

1. $X$ is a superkey of $R$, OR
2. Each attribute in $Y$ is a prime attribute (a member of some candidate key of $R$).

Definition of Roles in ER Model

In an Entity-Relationship (ER) model, a Role is the explicit name or function that an entity plays when participating in a relationship type.

While role names are technically implicit in binary relationships connecting two distinct entity sets (e.g., Customer and Account), they are strictly required in recursive (unary) relationships where the same entity set participates multiple times.

• Example: In the relationship Manages on the entity set EMPLOYEE:
  • Entity occurrence 1 plays the role of Manager.
  • Entity occurrence 2 plays the role of Subordinate / Worker.

Shadow Paging Recovery Technique

Shadow paging is an alternative, non-log-based database recovery mechanism where database pages are maintained via two separate page directories:

               +------------------------------------+
               |           Database Root            |
               +-----------------+------------------+
                                 |
                 +---------------+---------------+
                 |                               |
                 v                               v
       +--------------------+          +--------------------+
       | Current Page Table |          |  Shadow Page Table |
       |   (In Volatile     |          |    (On Non-Volatile|
       |      Memory)       |          |         Disk)      |
       +---------+----------+          +---------+----------+
                 |                               |
                 +---------------+---------------+
                                 |
                                 v
                     +-----------------------+
                     | Physical Database     |
                     | Disk Pages            |
                     +-----------------------+

Operational Workflow:

1. Two Page Tables:
   • Shadow Page Table: Stored permanently on non-volatile disk; represents the database state at the start of the transaction. Never updated during transaction execution.
   • Current Page Table: Maintained in main memory; tracks live database page pointers during execution.
2. Write Operation (Copy-on-Write): When a page is modified, the DBMS allocates a new free physical disk block, writes the modified data to the new block, and updates the pointer in the Current Page Table to point to the new block. The original block remains unchanged.
3. Commit Point: At commit, the Current Page Table is flushed to disk, and the single database root pointer is atomically updated to point to the new table, making it the new Shadow Page Table.
4. Abort / Crash Recovery: If the transaction fails, the Current Page Table is discarded, and the root pointer remains pointed at the unaltered Shadow Page Table. Recovery is instantaneous with no UNDO and no REDO needed.

Limitations:

• Severe disk page fragmentation and garbage collection overhead.

Database Administrator (DBA) and Roles

Who is a DBA?

A Database Administrator (DBA) is a highly skilled technical professional or specialized team responsible for the centralized operational management, performance, configuration, security, integrity, and ongoing maintenance of an organization’s database systems.

Key Roles and Responsibilities of a DBA:

1. Schema Definition & Modification: Formulates the physical and logical database schemas, writes data definition statements, and implements schema alterations to support evolving business requirements.
2. Storage Structure & Access-Method Definition: Determines physical disk layouts, file organizations, block sizes, clustering, and index creation (B-trees, hash indexes) for optimal query throughput.
3. Granting User Authorizations & Security Control: Administers user accounts, assigns role-based permissions (GRANT / REVOKE), prevents unauthorized data access, and maintains data privacy.
4. Routine Backup and Disaster Recovery: Establishes automated daily/weekly backup procedures, monitors Write-Ahead Logs, and executes disaster recovery drills to ensure zero data loss.
5. Performance Tuning & Optimization: Analyzes query execution plans, identifies bottlenecks, monitors server hardware utilization, and tunes database cache buffers.

Timestamp Ordering Concurrency Control Technique

The Timestamp Ordering (TO) protocol is a non-locking, pessimistic concurrency control technique that ensures conflict serializability by assigning each transaction a globally unique timestamp $TS(T)$ when it begins. Conflicting operations are executed strictly in timestamp order.

System Timestamps Maintained:

For every database data item $Q$, the DBMS maintains two timestamp values:

• $W\text{-timestamp}(Q)$: The largest timestamp of any transaction that successfully wrote $Q$.
• $R\text{-timestamp}(Q)$: The largest timestamp of any transaction that successfully read $Q$.

---

Basic Timestamp Ordering Protocol Rules:

1. Transaction $T$ issues $\text{Read}(Q)$:

   • If $TS(T) < W\text{-timestamp}(Q)$, then $T$ is attempting to read an overwritten value. Reject and rollback $T$ (abort $T$ and restart with a newer timestamp).
   • If $TS(T) \ge W\text{-timestamp}(Q)$, execute $\text{Read}(Q)$ and set:
     $$R\text{-timestamp}(Q) = \max(R\text{-timestamp}(Q), TS(T))$$

2. Transaction $T$ issues $\text{Write}(Q)$:

   • If $TS(T) < R\text{-timestamp}(Q)$, then $T$ is attempting to produce a value that should have been read by a younger transaction. Reject and rollback $T$.
   • If $TS(T) < W\text{-timestamp}(Q)$, then $T$ is attempting to overwrite a newer value. Reject and rollback $T$ (or ignore the write under the Thomas Write Rule).
   • Otherwise, execute $\text{Write}(Q)$ and set:
     $$W\text{-timestamp}(Q) = TS(T)$$

Major Advantage:

• Guarantees freedom from deadlocks, as transactions never wait on locks.

Mapping Composite Attributes into Relational Schema

Conceptual Rule:

In the ER-to-Relational mapping algorithm, a composite attribute is not directly mapped as a single column. Instead, it is flattened by replacing the composite attribute with its simple, atomic component attributes. The composite attribute itself is dropped from the relational schema.

---

Step-by-Step Illustration:

Consider an entity CUSTOMER in an ER model with:

• Primary key: Customer_ID
• Simple attribute: Email
• Composite attribute: Full_Name (components: First_Name, Middle_Name, Last_Name)
• Composite attribute: Address (components: Street_No, City, Postal_Code)

   [CUSTOMER]
       |--- Customer_ID (Key)
       |--- Email
       |--- Full_Name (Composite)
       |       |--- First_Name
       |       |--- Middle_Name
       |       |--- Last_Name
       |--- Address (Composite)
               |--- Street_No
               |--- City
               |--- Postal_Code

Resulting Relational Schema:

$$\text{Customer}(\underline{\text{Customer\_ID}}, \text{Email}, \text{First\_Name}, \text{Middle\_Name}, \text{Last\_Name}, \text{Street\_No}, \text{City}, \text{Postal\_Code})$$

SQL DDL Implementation:

CREATE TABLE Customer (
    customer_id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    first_name VARCHAR(30) NOT NULL,
    middle_name VARCHAR(30),
    last_name VARCHAR(30) NOT NULL,
    street_no VARCHAR(50),
    city VARCHAR(50),
    postal_code VARCHAR(10)
);

Constraints on Generalization and Specialization

In Extended Entity-Relationship (EER) modeling, generalization and specialization hierarchies are governed by two independent structural constraints:

                           EER Constraints
                                  |
              +-------------------+-------------------+
              |                                       |
    Disjointness Constraint                 Completeness Constraint
              |                                       |
        +-----+-----+                           +-----+-----+
        |           |                           |           |
     Disjoint    Overlap                      Total      Partial
       (d)         (o)                     (Double Line)(Single Line)

---

1. Disjointness Constraint

Specifies whether an entity instance can belong to more than one specialized subclass simultaneously:

• Disjoint (d): Subclasses must be mutually exclusive. An entity can belong to at most one subclass.
  • Example: In a VEHICLE superclass, an instance can be a CAR or a TRUCK, but not both simultaneously.
• Overlapping (o): Subclasses are not mutually exclusive. An entity can belong to multiple subclasses at the same time.
  • Example: In a university, a PERSON can be both an EMPLOYEE and a STUDENT.

---

2. Completeness (Mandatory / Optional) Constraint

Specifies whether an entity in the superclass must participate in at least one subclass:

• Total Specialization (Double Line): Every entity in the superclass must belong to at least one subclass.
  • Example: An ACCOUNT must be either a SAVINGS_ACCOUNT or a CHECKING_ACCOUNT.
• Partial Specialization (Single Line): An entity in the superclass does not have to belong to any subclass.
  • Example: In an EMPLOYEE superclass with subclass ENGINEER, some employees may be accountants or managers who do not belong to ENGINEER.

Importance of Database Security

Database security is of critical strategic importance because modern enterprise databases store confidential proprietary information, intellectual property, personal identifiable information (PII), and financial transaction records.

Key Reasons for Database Security:

1. Preserving the CIA Triad:
   • Confidentiality: Protects sensitive data from unauthorized interception and unauthorized disclosure.
   • Integrity: Prevents unauthorized, improper, or accidental modification or deletion of vital data.
   • Availability: Guarantees authenticated users uninterrupted access to database services during hardware faults or DoS attacks.
2. Protection Against Cyber Threats: Prevents destructive external attacks such as SQL Injection (SQLi), brute force authentication attacks, privilege escalation, and ransomware encryption.
3. Regulatory & Legal Compliance: Ensures adherence to strict national and international data protection laws (e.g., GDPR, HIPAA, Nepal Electronic Transactions Act) to prevent crippling financial penalties.
4. Reputational and Financial Safeguards: A data breach causes devastating loss of customer trust, corporate valuation drops, and severe legal liabilities.

Four Types of Database Users

Database users are classified into distinct categories based on their technical proficiency, access requirements, and the interfaces they use to interact with the DBMS:

+-------------------------------------------------------------------------+
|                          Database User Spectrum                         |
+-------------------+-------------------+-----------------+---------------+
|      DBA          | Database Designer | Application Dev |   End Users   |
| High Privilege    | Schema Modeling   | DML Programming | Daily Queries |
+-------------------+-------------------+-----------------+---------------+

---

1. Database Administrator (DBA)

• Description: The chief authority possessing supreme administrative privileges over the entire database system.
• Core Responsibilities:
  • Installing and configuring DBMS software across hardware servers.
  • Creating schemas, storage layouts, and system indexes.
  • Managing user accounts, assigning permissions (GRANT/REVOKE), and enforcing security policies.
  • Scheduling automated backups, monitoring system performance, and executing disaster recovery procedures.

---

2. Database Designers

• Description: Information architects responsible for identifying enterprise data requirements and engineering database models prior to database deployment.
• Core Responsibilities:
  • Interviewing business stakeholders to capture entities, attributes, and business rules.
  • Constructing conceptual schemas using Entity-Relationship (ER/EER) diagrams.
  • Converting conceptual schemas into normalized relational schemas (1NF through BCNF) to prevent update anomalies.

---

3. Application Programmers / Software Developers

• Description: Technical professionals who write application programs that interact with the database engine.
• Core Responsibilities:
  • Writing code in high-level programming languages (Java, C#, Python, JavaScript).
  • Embedding SQL Data Manipulation Language (DML) statements via APIs, ODBC/JDBC drivers, or ORM frameworks.
  • Building intuitive web and mobile user interfaces, error-handling routines, and transactional business logic.

---

4. End Users (Parametric and Casual Users)

• Description: Non-technical or business professionals who query and update the database during daily business operations.
• Subcategories:
  • Naive / Parametric Users: Represent the vast majority of users. They interact exclusively through pre-built graphical user interfaces, web forms, or POS terminals without knowing SQL (e.g., bank tellers, airline ticketing agents, retail cashiers).
  • Sophisticated Users: Business analysts, researchers, and data scientists who write ad-hoc SQL queries and use analytics tools to extract custom managerial reports.

How Normalization Helps Design a Good Database & Second Normal Form (2NF)

How Normalization Helps in Database Design:

Normalization is a formal mathematical process developed by E.F. Codd to analyze relational schemas based on their functional dependencies (FDs) and primary keys:

1. Elimination of Data Redundancy: Prevents repetitive recording of identical facts, drastically saving disk storage.
2. Prevention of Modification Anomalies: Completely eradicates insertion, deletion, and update anomalies.
3. Data Integrity & Consistency: Ensures changes made to an entity update a single tuple, preventing contradictory database states.
4. Scalability: Produces lean, modular tables that easily accommodate future schema additions without breaking existing relationships.

---

Second Normal Form (2NF) Definition:

A relation schema $R$ is in Second Normal Form (2NF) if and only if:

1. It is already in First Normal Form (1NF).
2. No non-prime attribute is partially dependent on any candidate key of $R$. That is, every non-prime attribute must be fully functionally dependent on the whole candidate key.

Note: Partial dependency can only exist when the candidate key is a composite key (composed of two or more attributes).

---

Step-by-Step Example of 2NF Decomposition:

Consider the relation STUDENT_COURSE_ENROLLMENT:

• Attributes: (Student_ID, Course_ID, Student_Name, Course_Name, Grade)
• Composite Primary Key: {Student_ID, Course_ID}

Functional Dependencies (FDs):

1. (Student_ID, Course_ID) -> Grade (Full Dependency on Key)
2. Student_ID -> Student_Name (Partial Dependency: depends on part of PK)
3. Course_ID -> Course_Name (Partial Dependency: depends on part of PK)

Anomalies Present:

• Insertion Anomaly: Cannot insert a new course until a student enrolls in it.
• Deletion Anomaly: Deleting the only student enrolled in a course deletes the course name.
• Update Anomaly: If a student changes their name, multiple enrollment rows must be updated.

2NF Decomposition:

To achieve 2NF, decompose the table into three separate relations by isolating partial dependencies:

1. STUDENT Relation:
   • Schema: Student(Student_ID [PK], Student_Name)
2. COURSE Relation:
   • Schema: Course(Course_ID [PK], Course_Name)
3. ENROLLMENT Relation:
   • Schema: Enrollment(Student_ID [FK], Course_ID [FK], Grade, PRIMARY KEY(Student_ID, Course_ID))

Result: Every non-key attribute is now fully dependent on its table’s primary key. The schema is in 2NF.

Four Relational Database Constraints with Examples

Database constraints are declarative assertions and rules enforced by the DBMS data integrity engine to guarantee that data entry, modifications, and deletions do not corrupt database consistency.

+----------------------------------------------------------------------+
|                     Core Database Constraints                        |
+-------------------+--------------------+-----------------------------+
| Constraint        | Scope              | Rule Enforced               |
+-------------------+--------------------+-----------------------------+
| 1. Domain         | Single Attribute   | Valid data types & ranges   |
| 2. Entity Int.    | Primary Key        | Uniqueness & NOT NULL       |
| 3. Referential    | Foreign Key        | Value must exist in parent  |
| 4. Key / UNIQUE   | Candidate Keys     | No duplicate values allowed |
+-------------------+--------------------+-----------------------------+

---

1. Domain Constraint

• Concept: Specifies that every value stored in an attribute must be an atomic element belonging to the defined domain/data type, including range, format, and NOT NULL rules.
• Example:

CREATE TABLE Employee (
    emp_id INT,
    gender CHAR(1) CHECK (gender IN ('M', 'F', 'O')),
    salary DECIMAL(10, 2) CHECK (salary >= 10000)
);

---

2. Entity Integrity Constraint

• Concept: Specifies that no primary key value can be NULL because primary key values are used to identify individual tuples in a relation.
• Example:

CREATE TABLE Department (
    dept_id INT PRIMARY KEY,  -- Automatically enforces UNIQUE and NOT NULL
    dept_name VARCHAR(50) NOT NULL
);

---

3. Referential Integrity Constraint

• Concept: Maintained between two relations to preserve consistency between tuples. A foreign key in a referencing relation must either match a valid primary key value in the referenced relation or be NULL.
• Example:

CREATE TABLE Employee (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    dept_id INT,
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id)
        REFERENCES Department(dept_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

---

4. Key Constraint (UNIQUE Constraint)

• Concept: Specifies that all values in a designated column or set of columns must be distinct across all rows in the relation, while permitting optional NULL values.
• Example:

CREATE TABLE UserAccount (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE
);

Desirable Properties of Transaction (ACID Properties)

A database transaction is a sequence of read and write operations treated as a single logical unit of processing. To guarantee data reliability, especially during concurrent execution and unforeseen system failures, a transaction must adhere to the ACID properties:

---

1. Atomicity (“All or Nothing”)

• Principle: A transaction must be an indivisible unit of work. Either all operations of the transaction succeed and commit, or in the event of an error, the entire transaction is rolled back and has no effect on the database.
• Enforcing Component: The Recovery Manager using undo log records.
• Example: During an online banking transfer, if money is debited from Sender A but a network failure prevents crediting Receiver B, atomicity forces the database to undo the debit from Sender A.

---

2. Consistency (State Validity)

• Principle: A transaction must take the database from one valid consistent state to another valid consistent state, maintaining all schema constraints, business rules, and cascade invariants.
• Enforcing Component: The Application Program and the DBMS Integrity Subsystem (Primary Keys, Foreign Keys, CHECK constraints).
• Example: The total sum of money across all bank accounts before a transfer must equal the total sum after the transfer completes.

---

3. Isolation (Independence)

• Principle: The execution of a transaction must not be interfered with by any other concurrently executing transactions. Uncommitted intermediate changes must not be visible to other transactions.
• Enforcing Component: The Concurrency Control Manager using locking mechanisms (2PL) or Timestamp Ordering.
• Isolation Levels (ANSI SQL): Read Uncommitted, Read Committed, Repeatable Read, Serializable.

---

4. Durability (Permanence)

• Principle: Once a transaction commits successfully, its modifications are permanently recorded in non-volatile storage and will survive any subsequent system crash, power failure, or operating system reboot.
• Enforcing Component: The Recovery Manager using Write-Ahead Logging (WAL) and non-volatile storage caching.

---

Summary Matrix:

Property	Focus	Guaranteed By
Atomicity	Complete execution or complete rollback	Log File / UNDO Mechanism
Consistency	Validity of integrity constraints	Application Logic & DBMS Constraints
Isolation	Concurrency without cross-talk	Two-Phase Locking / Serializability
Durability	Survival across system crashes	Write-Ahead Logging (WAL) / REDO

Comprehensive ER Diagram Design: Hospital Management System

1. Requirements and Scope Analysis

A modern Hospital Management System requires modeling patient admissions, doctor assignments, departmental organizations, medical appointments, inpatient room allocations, and billing.

---

2. Identification of Entity Sets and Attributes

1. PERSON (Superclass Entity):
   • Attributes: person_id (PK), name (Composite: first_name, last_name), phone, email, address, dob, gender
2. PATIENT (Subclass of Person):
   • Attributes: patient_id (PK / FK), blood_group, emergency_contact, medical_history
3. DOCTOR (Subclass of Person):
   • Attributes: doctor_id (PK / FK), specialization, license_no, consultation_fee
4. DEPARTMENT:
   • Attributes: dept_id (PK), dept_name, location_floor, contact_extension
5. APPOINTMENT:
   • Attributes: appointment_id (PK), app_date, app_time, status (Scheduled/Completed/Cancelled)
6. ROOM / WARD:
   • Attributes: room_no (PK), room_type (General/ICU/Deluxe), daily_charge, status (Occupied/Vacant)
7. BILL:
   • Attributes: bill_id (PK), total_amount, payment_date, payment_status (Paid/Pending)

---

3. Extended ER (EER) Features & Mapping Cardinalities

• Specialization / Generalization Hierarchy:
  • PERSON is specialized into PATIENT and DOCTOR.
  • Constraints: Disjoint (d) and Partial Specialization (a person can also be administrative staff).
• Relationships and Mapping Cardinalities:
  • DEPARTMENT — Belongs_To — DOCTOR: $1:N$. A department employs many doctors; each doctor belongs to exactly one department (Total participation on Doctor).
  • PATIENT — Books — APPOINTMENT: $1:N$. A patient can book multiple appointments.
  • DOCTOR — Attends — APPOINTMENT: $1:N$. A doctor attends multiple appointments.
  • PATIENT — Admitted_To — ROOM: $N:1$. Multiple patients may occupy beds over time; at any instant, an admitted patient is assigned to at most one room.
  • PATIENT — Generates — BILL: $1:N$. A patient incurs bills for consultations and treatments.

---

4. Architectural Textual ER Diagram

                        +---------------------------+
                        |          PERSON           |
                        | (person_id, name, phone)  |
                        +-------------+-------------+
                                      |
                                    / d \  [Disjoint Specialization]
                                   +-----+
                                    /   \
            +----------------------+     +----------------------+
            |       PATIENT        |     |        DOCTOR        |
            | (patient_id, blood)  |     | (doctor_id, license) |
            +---+--------------+---+     +----------+-----------+
                |              |                    |
                | 1            | 1                  | 1
             (Books)      (Admitted_To)         (Attends)
                |              |                    |
                v N            v N                  v N
        +-------+-------+  +---+-----------+  +-----+---------+
        |  APPOINTMENT  |  |     ROOM      |  |  DEPARTMENT   |
        | [app_id, date]|  | [room_no, fee]|  | [dept_id, name|
        +---------------+  +---------------+  +---------------+
                |
             (Generates) [1:N]
                v
        +---------------+
        |     BILL      |
        | [bill_id, amt]|
        +---------------+

---

5. Converted Relational Schema

1. Person(person_id [PK], first_name, last_name, phone, email, address, dob, gender)
2. Patient(patient_id [PK/FK references Person], blood_group, emergency_contact)
3. Department(dept_id [PK], dept_name, location_floor)
4. Doctor(doctor_id [PK/FK references Person], specialization, license_no, consultation_fee, dept_id [FK])
5. Room(room_no [PK], room_type, daily_charge, status)
6. Appointment(appointment_id [PK], app_date, app_time, status, patient_id [FK], doctor_id [FK])
7. Admission(admission_id [PK], admission_date, discharge_date, patient_id [FK], room_no [FK])
8. Bill(bill_id [PK], total_amount, payment_date, payment_status, patient_id [FK], appointment_id [FK])

SQL Solutions for Vehicle-Owner Relational Database

Given Relational Database Schema:

• Car(reg_no, model, price, color)
• Person(pid, name, address, phone)
• owns_by(reg_no, pid, date)

---

i) Insert a tuple {1, Ram Bdr, kathmandu} in person relation:

INSERT INTO Person (pid, name, address, phone)
VALUES (1, 'Ram Bdr', 'kathmandu', NULL);

Note: Since the phone attribute is not provided in the tuple, it is explicitly set to NULL (or omitted from the column list).

---

ii) Display the model number with the highest price:

SELECT model
FROM Car
WHERE price = (SELECT MAX(price) FROM Car);

Alternative using ORDER BY and LIMIT (standard ANSI/PostgreSQL/MySQL):

SELECT model
FROM Car
ORDER BY price DESC
LIMIT 1;

---

iii) Increase the price of each car by 15%:

UPDATE Car
SET price = price * 1.15;

---

iv) Find name of person who owns red colored car:

SELECT DISTINCT p.name
FROM Person p
JOIN owns_by o ON p.pid = o.pid
JOIN Car c ON o.reg_no = c.reg_no
WHERE LOWER(c.color) = 'red';

Subquery Formulation:

SELECT name
FROM Person
WHERE pid IN (
    SELECT pid
    FROM owns_by
    WHERE reg_no IN (
        SELECT reg_no
        FROM Car
        WHERE LOWER(color) = 'red'
    )
);

---

v) Display those records of car whose model contains at most 4 characters:

SELECT *
FROM Car
WHERE LENGTH(model) <= 4;

Alternative using CHAR_LENGTH / LIKE pattern:

SELECT *
FROM Car
WHERE CHAR_LENGTH(TRIM(model)) <= 4;