.C. Entity set ii.B. Automated conversion of ERD into relational schema iii).B. In a row of a relational table, an attribute can have more than one value.

1. Reduced data redundancy A database reduces unnecessary duplication of data. For example, borrower information can be stored once and referenced by loan records. 2. Data integrity A DBMS provides constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK to ensure that stored data remains accurate and consistent. 3. Concurrent access A database system allows multiple users to access and update the library information at the same time while providing transaction and concurrency control. Other acceptable reasons include: Better security and access control. Easier backup and recovery. Easier data sharing. Easier data querying and reporting.

Two tables can be logically connected using a Primary Key (PK) and a Foreign Key (FK). For example: STUDENT ---------------- studentID PK name LOAN ---------------- loanID PK studentID FK bookID The studentID in the LOAN table is a foreign key that references the studentID primary key in the STUDENT table. Referential integrity requires that a foreign-key value must either: Match an existing primary-key value in the referenced table, or Be NULL if the foreign-key column allows NULL values. For changes, rules such as the following can be applied: ON UPDATE CASCADE ON DELETE CASCADE or deletion can be restricted when the parent record is still referenced.

Rows in a database table are uniquely identified using a Primary Key. The rules of entity integrity require that: Every row must have a unique primary-key value. A primary-key value cannot be NULL. The primary key should uniquely identify each row. Guidelines for selecting a primary key: It should be unique. It should not normally change. It should contain the minimum number of attributes necessary. It should reliably identify each record. Example: STUDENT ----------------- studentID PK name phone Here, studentID is a suitable primary key.

CREATE TABLE Patient ( patientNo VARCHAR(10) PRIMARY KEY, patName VARCHAR(100) NOT NULL, patAddr VARCHAR(200), DoB DATE NOT NULL, Gender CHAR(1) NOT NULL, CONSTRAINT CK_Patient_Gender CHECK (Gender IN ('F', 'M')) );

CREATE TABLE Ward ( wardNo VARCHAR(10) PRIMARY KEY, wardName VARCHAR(100) NOT NULL, CONSTRAINT UQ_Ward_WardName UNIQUE (wardName), wardType VARCHAR(50), noOfBeds INT ); The following first record will be accepted: INSERT INTO Ward VALUES ('W01', 'Male Ward', 'Private', 12); The second record: INSERT INTO Ward VALUES ('W02', 'Male Ward', 'General', 20); will produce a UNIQUE constraint violation because Male Ward already exists.

CREATE TABLE Contains ( patientNo VARCHAR(10), wardNo VARCHAR(10), admissionDate DATE, PRIMARY KEY (patientNo, wardNo), CONSTRAINT FK_Contains_Patient FOREIGN KEY (patientNo) REFERENCES Patient(patientNo) ON UPDATE CASCADE, CONSTRAINT FK_Contains_Ward FOREIGN KEY (wardNo) REFERENCES Ward(wardNo) ON UPDATE CASCADE ); Explanation: FOREIGN KEY ensures that the patient and ward numbers must exist in their respective parent tables. ON UPDATE CASCADE means that if patientNo or wardNo changes in the parent table, the change is automatically reflected in Contains. We do not use ON DELETE CASCADE, because the question requires deletion of the referenced parent record to fail.

CREATE TABLE Drug ( drugNo VARCHAR(10) PRIMARY KEY, drugName VARCHAR(100) NOT NULL, costPerUnit DECIMAL(10,2) NOT NULL, CONSTRAINT CK_Drug_Cost CHECK (costPerUnit BETWEEN 5000 AND 20000) ); Explanation: NOT NULL ensures every drug has a drug name and cost. The CHECK constraint ensures: 5000 <= costPerUnit <= 20000

CREATE VIEW PatientDrug AS SELECT p.patName, p.DoB, p.Gender, d.drugName, d.costPerUnit, pr.unitsPerDay, pr.startDate, pr.finishDate FROM Patient p JOIN Prescribed pr ON p.patientNo = pr.patientNo JOIN Drug d ON pr.drugNo = d.drugNo;

ALTER TABLE Emp ADD eAddress VARCHAR(30);

ALTER TABLE Emp ALTER COLUMN DoB DATE;

SELECT e.ename FROM Emp e JOIN Works w ON e.eid = w.eid WHERE w.did = 10; Explanation: The Emp and Works tables are joined using eid, and WHERE w.did = 10 selects employees working in department 10.

UPDATE Emp SET salary = salary * 1.10 WHERE salary < 500000; Explanation: A 10% increase means: New salary = Old salary + 10% of Old salary New salary = Old salary × 1.10

SELECT e.ename, d.dname FROM Emp e JOIN Dept d ON e.eid = d.managereid; Explanation: Dept.managereid contains the employee ID of the manager. Therefore, it is joined with Emp.eid.

SELECT e.ename FROM Emp e WHERE e.salary > ALL ( SELECT d.budget FROM Works w JOIN Dept d ON w.did = d.did WHERE w.eid = e.eid ); Explanation: > ALL means the employee's salary must be greater than the budget of every department in which that employee works.

SELECT DISTINCT e.ename FROM Emp e JOIN Dept d ON e.eid = d.managereid WHERE NOT EXISTS ( SELECT 1 FROM Dept d1 WHERE d1.managereid = e.eid AND d1.budget <= 1000000 ) AND EXISTS ( SELECT 1 FROM Dept d2 WHERE d2.managereid = e.eid AND d2.budget < 5000000 ); Explanation: The query has two conditions: 1. No department managed by the manager has a budget <= 1,000,000 and 2. At least one department managed by the manager has a budget < 5,000,000 Therefore, the manager satisfies: All managed departments > 1,000,000 AND At least one managed department < 5,000,000