Database Management System

(c) Tuples

In relational database terminology, a row of a table is called a record or a tuple, while its columns are called fields (attributes).

(d) SELECT name, address FROM students WHERE address = "Kathmandu" OR address = "Bhaktapur";

It selects only the name and address columns as asked, and gives a complete condition for each value. Option (b) fails because OR "Bhaktapur" is not a comparison — it is just a non-empty string, which is treated as true, so every row of the table would be displayed.

Comparison of the hierarchical and relational database models

Two advantages of the hierarchical model

1. Simple, easy-to-understand structure, and very fast retrieval along the predefined parent-child paths.
2. Good data integrity, because every child record is tied to its parent (a child cannot exist without its parent).

Two advantages of the relational model

1. Flexible and independent of physical storage: tables can be changed or linked without rewriting programs (data independence).
2. Easy and powerful querying with SQL, with reduced redundancy and inconsistency through keys and normalization.

Second Normal Form (2NF)

Definition: A relation is in 2NF if it is already in 1NF and every non-key (non-prime) attribute is fully functionally dependent on the whole primary key, not on a part of it. In other words, 2NF removes partial dependency — a dependency of a non-key attribute on only a portion of a composite primary key. A relation in 1NF with a single (non-composite) primary key is automatically in 2NF.

Rule: For a relation to be in 2NF, there must be no partial dependency of any non-prime attribute on any candidate key.

Example — relation not in 2NF:

Consider a relation STUDENT_SUBJECT that records marks of students in subjects, with a composite primary key (StudentID, SubjectID):

Here the primary key is (StudentID, SubjectID). The dependencies are:

• StudentID → StudentName (partial dependency)
• SubjectID → SubjectName (partial dependency)
• (StudentID, SubjectID) → Marks (full dependency)

StudentName depends only on StudentID, and SubjectName depends only on SubjectID — both are parts of the composite key. These are partial dependencies, so the relation is not in 2NF.

Converting to 2NF: Decompose the relation so that each non-key attribute depends on the full key. Split it into three relations:

STUDENT

SUBJECT

MARKS

Now in each relation every non-key attribute depends on the whole primary key: StudentName on StudentID, SubjectName on SubjectID, and Marks on (StudentID, SubjectID). Partial dependencies are removed, so all three relations are in 2NF. This also reduces redundancy — "Ramesh" and "Computer" are no longer repeated for every row.

Normalization

Normalization is the process of organizing the fields and tables of a relational database to minimize data redundancy and eliminate undesirable characteristics like insertion, update, and deletion anomalies. It involves dividing large tables into smaller, well-structured tables and defining relationships between them, following a series of rules called normal forms (1NF, 2NF, 3NF, BCNF, etc.).

The main objectives of normalization are:

• To remove redundant (duplicate) data.
• To ensure data dependencies make sense.
• To protect the database from insertion, deletion, and update anomalies.
• To ensure data consistency and integrity.

---

Second Normal Form (2NF)

A relation is in Second Normal Form (2NF) if:

1. It is already in First Normal Form (1NF) (i.e., all attributes are atomic — no repeating groups or multivalued attributes), and
2. It has no partial dependency — every non-prime (non-key) attribute is fully functionally dependent on the entire primary key, not on just a part of a composite primary key.

In simple words, 2NF removes attributes that depend on only part of a composite primary key and places them in a separate table.

Example

Consider a relation:

STUDENT (Roll_No, Subject_Code, Student_Name, Subject_Name, Marks)

Here the composite primary key is (Roll_No, Subject_Code).

Problems:

• Student_Name depends only on Roll_No (part of the key) → partial dependency.
• Subject_Name depends only on Subject_Code (part of the key) → partial dependency.

This relation is in 1NF but not in 2NF.

Converting to 2NF

Decompose into three tables so that every non-key attribute depends on the whole key:

STUDENT (Roll_No, Student_Name)

SUBJECT (Subject_Code, Subject_Name)

MARKS (Roll_No, Subject_Code, Marks)

Now each table is in 2NF, as all non-key attributes are fully dependent on the whole primary key.

DDL Commands (Data Definition Language)

DDL commands define or modify the structure of database objects such as tables. Two common DDL commands are:

1. CREATE — creates a new database object such as a table.

CREATE TABLE table_name (
    column1 datatype [constraint],
    column2 datatype [constraint],
    ...
);

Example:

CREATE TABLE student (
    roll  INT PRIMARY KEY,
    name  VARCHAR(30),
    marks INT
);

2. ALTER — changes the structure of an existing table (add, modify or drop a column).

ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name MODIFY column_name datatype;

Example:

ALTER TABLE student ADD address VARCHAR(40);

---

DML Commands (Data Manipulation Language)

DML commands work on the data stored inside tables. Three common DML commands are:

1. INSERT — adds a new record (row) into a table.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example:

INSERT INTO student (roll, name, marks)
VALUES (1, 'Aarav', 85);

2. UPDATE — modifies existing records in a table.

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

Example:

UPDATE student
SET marks = 90
WHERE roll = 1;

3. DELETE — removes records from a table.

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM student
WHERE roll = 1;

---

Note: DDL commands change the table structure and are auto-committed (cannot be rolled back), while DML commands change the table data and can be rolled back using ROLLBACK.

(a) To uniquely identify a record

A primary key is a field (or set of fields) whose value is unique for every record in a table, so it is used to identify each record uniquely. It also cannot be NULL and does not allow duplicate values.

(b) ALTER

The ALTER TABLE command is used to modify the structure of an existing table, including adding a new column (e.g. ALTER TABLE employee ADD email VARCHAR(50);). CREATE makes a new table, SELECT retrieves data and UPDATE changes existing records.

DDL vs DML — three differences

Examples

DDL:

CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(30),
    marks INT
);
ALTER TABLE student ADD address VARCHAR(40);
DROP TABLE student;

DML:

INSERT INTO student VALUES (1, 'Rita', 450);
UPDATE student SET marks = 480 WHERE id = 1;
DELETE FROM student WHERE id = 1;
SELECT * FROM student;

So DDL defines what the table looks like, while DML decides what data the table holds.

Normalization is the process of organizing the fields and tables of a relational database to minimize data redundancy and eliminate insertion, deletion and update anomalies. It works by dividing large tables into smaller related tables and linking them through keys. Each stage of this process is called a normal form (1NF, 2NF, 3NF, BCNF, etc.).

Second Normal Form (2NF) A relation is in 2NF if:

1. It is already in 1NF (all attributes are atomic), and
2. There is no partial dependency — every non-key attribute must depend on the whole primary key, not on a part of it.

Partial dependency can only occur when the primary key is composite. For example, in the relation Result(RollNo, SubjectID, SubjectName, Marks) with composite key (RollNo, SubjectID), the attribute SubjectName depends only on SubjectID (part of the key), so it is a partial dependency. To convert to 2NF, split it into Subject(SubjectID, SubjectName) and Result(RollNo, SubjectID, Marks).

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

1. It is in 2NF, and
2. There is no transitive dependency — no non-key attribute should depend on another non-key attribute.

For example, in Student(RollNo, Name, DeptID, DeptName) where RollNo is the primary key, DeptName depends on DeptID, which is a non-key attribute. This is a transitive dependency (RollNo → DeptID → DeptName). To convert to 3NF, split it into Student(RollNo, Name, DeptID) and Department(DeptID, DeptName).

In short, 2NF removes partial dependency on a composite key, while 3NF removes transitive dependency among non-key attributes.

(b) Distributed database system

A distributed database stores data across multiple sites (often in different regions), so each user is served by a nearby node, reducing network delay and improving performance for geographically dispersed users.

(a) Select * from emp where copid = 103;

This is the only statement with valid SQL syntax: SELECT * FROM table WHERE condition. The others are wrong — (b) omits the column list after SELECT, (c) places the table name after the condition instead of in the FROM clause, and (d) uses "table = emp" as a condition instead of a FROM clause.

Advantages of DBMS over Traditional File-Based Data Storage Systems

A DBMS stores data in a single, centrally controlled repository and provides software to manage it. Compared with the traditional file-based approach — where each application keeps its own separate files — it offers the following advantages:

1. Controlled redundancy (no unnecessary duplication) In a file system, every application maintains its own file, so the same data (e.g. a customer's address) is stored many times. A DBMS keeps one integrated copy and, where duplication is unavoidable, records and controls it. This saves storage space and prevents different versions of the same fact existing side by side.

2. Data consistency and integrity Because there is a single copy of each data item, an update made once is seen by all users, so the data cannot become contradictory. A DBMS also enforces integrity constraints — domain checks, key constraints, referential integrity — which a file system cannot do, as it has no central definition of the data.

3. Data sharing and concurrent access Many users and applications can use the same database at the same time. The DBMS uses concurrency-control techniques such as locking and transactions to ensure that simultaneous operations do not corrupt data — something impossible to guarantee with independent files.

4. Better data security A DBMS provides authorization and access control: user accounts, privileges (SELECT, INSERT, UPDATE, DELETE) and views that hide sensitive columns. File systems depend only on the operating system's file permissions, which are coarse and easily bypassed by anyone with file access.

5. Data independence The logical structure of the data is separated from the programs that use it (program–data independence) and from its physical storage (physical data independence). In a file system, changing the record layout forces every program that reads the file to be rewritten; in a DBMS the schema can change with little or no change to application programs.

6. Backup and recovery A DBMS maintains logs and checkpoints and can restore the database to a consistent state after a crash or power failure. File systems offer no built-in recovery mechanism, so data lost in a failure is usually lost permanently.

7. Enforced standards and easier querying The DBA can enforce naming conventions, formats and organisational standards across the whole database. Users can also retrieve data with a high-level language such as SQL, including ad hoc queries, without writing a new program each time.

8. Reduced development and maintenance cost Since data management, security and recovery are handled by the DBMS, application programs become shorter and simpler, cutting development time and the cost of maintaining many separate file-handling programs.

Conclusion: A DBMS overcomes the redundancy, inconsistency, poor security, weak concurrency and difficult recovery of file-based storage, at the cost of higher initial software/hardware investment and greater complexity — a trade-off that is worthwhile for any organisation with shared, growing data.

1NF vs 2NF

First Normal Form (1NF) is the first level of normalisation. A relation is in 1NF if:

• Every attribute holds only atomic (indivisible) values — no multivalued or composite values in a single cell.
• Each column has a unique name, and each row is uniquely identified by a primary key.
• There are no repeating groups or arrays within a row.

1NF removes repeating groups and makes the table flat, but it does not look at how non-key attributes depend on the primary key. So a 1NF table may still contain partial dependencies.

Second Normal Form (2NF) is the next level. A relation is in 2NF if:

• It is already in 1NF, and
• Every non-key (non-prime) attribute is fully functionally dependent on the whole primary key — i.e. there is no partial dependency on a part of a composite key.

So the essential difference is: 1NF deals with the structure of values (atomicity and no repeating groups), while 2NF deals with the relationship between non-key attributes and the primary key (removal of partial dependencies). A table can be in 1NF without being in 2NF, but a table in 2NF is always in 1NF. Note that 2NF is only relevant when the primary key is composite; a table with a single-column primary key is automatically in 2NF if it is in 1NF.

Example

Consider a table in 1NF:

Primary key = (StudentID, CourseID). Here StudentName depends only on StudentID, and CourseName depends only on CourseID — these are partial dependencies, so the table is in 1NF but not 2NF. Splitting it into three tables — Student(StudentID, StudentName), Course(CourseID, CourseName), and Enroll(StudentID, CourseID, Marks) — brings it to 2NF.

Key benefits of achieving 2NF

1. Removes data redundancy — details like student name or course name are stored once, not repeated for every enrolment.
2. Avoids update anomalies — a change (e.g. renaming a course) is made in one place only, so no inconsistent copies remain.
3. Prevents insertion anomalies — a new course can be added even if no student has enrolled in it yet.
4. Prevents deletion anomalies — deleting the last enrolment of a student does not erase the student's or course's information.
5. Improves data integrity and consistency — each fact is stored in exactly one place, making the database easier to maintain and query.

In short, 1NF makes data flat and atomic, while 2NF goes further by removing partial dependencies on a composite key, giving a cleaner, more consistent and reliable database design.

(c) SELECT name, height FROM Player WHERE height > 6;

The question asks for two columns — name and height — so both must appear in the SELECT clause, and "taller than 6 feet" means a strict comparison, height > 6. Option (a) selects only one column, (b) uses >= which also includes players exactly 6 feet tall, and (d) uses < which gives shorter players.

(d) Primary Key

A primary key is a field (or set of fields) whose value is unique for every record in a table, so it uniquely identifies each row. Fields, records and rows do not guarantee uniqueness.

Importance of a Database

A database is an organised collection of related data stored in a structured form so that it can be easily accessed, managed, and updated. Its importance in modern information systems can be described as follows:

1. Reduces data redundancy and inconsistency – In a file-processing system the same data is often stored in many separate files. A database stores data centrally, so duplication is minimised and conflicting copies of the same data are avoided.

2. Ensures data integrity and accuracy – Integrity constraints (primary keys, foreign keys, validation rules) can be applied so that only correct and valid data enters the database. This keeps the data reliable.

3. Provides data security – A DBMS allows different users to be given different access rights (read, write, delete). Sensitive data can be protected through authentication, authorisation, and encryption, which is not possible in ordinary file systems.

4. Supports data sharing and multi-user access – Many users and applications can use the same database at the same time. Concurrency control mechanisms ensure that simultaneous transactions do not corrupt the data.

5. Provides data independence – The logical and physical structure of the data is separated from the application programs. A change in the storage structure does not require rewriting the programs, which greatly reduces maintenance cost.

6. Enables easy and fast retrieval of data – Data can be searched, filtered, sorted, and joined using query languages such as SQL. Indexing makes retrieval very fast even when the volume of data is large.

7. Ensures backup and recovery – A DBMS provides facilities for taking backups and for recovering the database to a consistent state after a failure (power loss, crash, or corruption), so that data is not permanently lost.

8. Maintains data integrity through concurrent control and transactions – Transactions follow the ACID properties (Atomicity, Consistency, Isolation, Durability), so that either all operations of a transaction are completed or none are, keeping the database consistent.

9. Supports decision making – Organised data can be queried and summarised to produce reports and statistics, which help managers and organisations take correct decisions.

10. Reduces cost of data storage and maintenance – Central storage avoids repeated storage of the same data in many files and reduces the manpower needed to maintain separate file systems.

In short, a database is important because it provides a controlled, secure, consistent, and shared store of data that can be efficiently managed and used by many users and applications at the same time.

Relational Data Model

The relational data model, proposed by Dr. E. F. Codd in 1970, is the most widely used data model for organising and managing data in a database. In this model, data is stored in the form of relations (tables), and the relationships among data are represented by common values in different tables rather than by physical pointers.

Basic Concepts

1. Relation (Table): A relation is a two-dimensional table consisting of rows and columns. Each relation has a unique name and represents an entity (such as Student, Employee, Book).

2. Tuple (Row/Record): A single row of a relation is called a tuple. It represents one instance or occurrence of the entity. For example, one row in a Student table represents one student.

3. Attribute (Column/Field): A column of a relation is called an attribute. It represents a property or characteristic of the entity, such as Roll_No, Name, or Address. Each attribute has a defined domain, i.e., the set of permitted values.

4. Degree: The number of attributes in a relation is its degree. A relation with 4 columns has degree 4.

5. Cardinality: The number of tuples (rows) in a relation is its cardinality. It changes as records are inserted or deleted.

6. Domain: The set of all possible values that an attribute can take. For example, the domain of Gender may be {Male, Female, Other}.

7. Relation Schema: The structure or description of a relation, written as R(A1, A2, …, An). For example: Student(Roll_No, Name, Address, Marks).

8. Relation Instance: The actual set of tuples present in a relation at a particular moment.

Keys in the Relational Model

• Primary Key: An attribute (or set of attributes) that uniquely identifies each tuple in a relation. It cannot be NULL.
• Candidate Key: Any attribute that is capable of becoming a primary key.
• Alternate Key: A candidate key that is not chosen as the primary key.
• Foreign Key: An attribute in one relation that refers to the primary key of another relation, used to establish a link between the two tables.
• Composite Key: A primary key made up of two or more attributes together.

Example

Student Table

Here, Roll_No is the primary key; the table has degree 3 and cardinality 3.

Properties of a Relation

1. Each relation has a unique name.
2. Each cell contains a single (atomic) value.
3. Each attribute has a unique name within a relation.
4. All values in a column are of the same domain.
5. Each tuple is unique (no duplicate rows).
6. The order of rows and columns is insignificant.

Advantages

• Simple and easy to understand, since data is viewed as tables.
• Provides data independence (physical and logical).
• Reduces data redundancy and inconsistency through normalization.
• Powerful querying using relational languages such as SQL and relational algebra.
• Ensures data integrity through keys and constraints.

Disadvantages

• Requires powerful hardware and software for large databases.
• Performance may degrade with very large tables and complex joins.
• Poor design can still lead to redundancy and anomalies.

In short, the relational data model organises data into tables related through keys, and forms the theoretical foundation of modern RDBMS software such as MySQL, Oracle, and SQL Server.

(b) Select

DML (Data Manipulation Language) statements are used to insert, update, delete and retrieve data in a table — Select, Insert, Update and Delete. Create, Alter and Drop are DDL statements, which work on the structure of database objects.

(b) mysqli_connect()

It is the PHP function that opens a connection to a MySQL database by taking the host name, username, password and database name as arguments, e.g. mysqli_connect("localhost", "root", "", "school").

Relational Data Model

The relational data model, proposed by E. F. Codd in 1970, is the most widely used data model for organising data in a database. In this model, data is stored in the form of relations (tables), and the relationships between data are represented by common values in the tables rather than by physical pointers.

Basic Concepts (Terminology)

Key Features

1. Data is stored in tables — each table has a unique name and a fixed set of columns.
2. Each row is unique — no two tuples are identical; uniqueness is enforced by a primary key.
3. Order is not significant — the sequence of rows and columns does not matter.
4. Atomic values — each cell holds a single, indivisible value (no repeating groups).
5. Relationships through keys — a foreign key in one table refers to the primary key of another table, linking the two.
6. Data independence — the logical view (tables) is separate from physical storage.
7. Integrity constraints — domain, entity and referential integrity rules keep data valid.

Example

Consider a school database with two tables: STUDENT and MARKS.

STUDENT

MARKS

Here:

• Roll is the primary key of STUDENT (uniquely identifies each student).
• In MARKS, Roll is a foreign key that refers to Roll of STUDENT.
• The two tables are related through the common attribute Roll. For example, the row (101, Maths, 85) tells us that the student with Roll 101 (Sita) scored 85 in Maths.

Advantages

• Simple and easy to understand (tables are familiar).
• Powerful query languages such as SQL can be used.
• Reduces data redundancy and inconsistency through normalisation.
• Provides strong data integrity and security.

Limitations

• Not efficient for very complex or unstructured data (images, videos).
• Requires powerful hardware and software for large databases.
• Performance may degrade with a large number of tables and joins.

SQL DDL commands for the schema student_info (regno integer, name character(25), class integer, gender character(1), address character(5))

Create the table

CREATE TABLE student_info (
    regno   INTEGER,
    name    CHAR(25),
    class   INTEGER,
    gender  CHAR(1),
    address CHAR(5)
);

Other common DDL commands on this table

-- add a new column
ALTER TABLE student_info ADD phone VARCHAR(10);

-- remove a column
ALTER TABLE student_info DROP COLUMN phone;

-- delete all rows but keep the structure
TRUNCATE TABLE student_info;

-- delete the whole table
DROP TABLE student_info;

CREATE, ALTER, TRUNCATE and DROP are DDL commands because they define or change the structure of the table, not the data stored in it.

(a) DELETE

The DELETE statement removes rows (records) from a table, e.g. DELETE FROM student WHERE id = 5;. DROP removes the whole table structure, while REMOVE and CLEAR are not SQL statements.

(a) Primary Key

A primary key is a field (or set of fields) whose value is unique for every record in a table, so it identifies each row distinctly. It cannot be duplicate or null.

Difference between Centralized and Distributed Database System

In short: A centralized database keeps all data at one site and is simple but has a single point of failure, while a distributed database spreads data over many sites, giving better reliability and performance at the cost of complexity.

Purposes of Normalization

Normalization is the process of organizing the attributes and tables of a relational database to reduce data redundancy and eliminate data anomalies. Its main purposes are:

1. To minimize data redundancy — The same piece of data is stored in only one place, so it is not repeated across many rows or tables. This saves storage space and keeps the data consistent.

2. To eliminate insertion anomalies — Without normalization, we may be forced to leave some fields empty or repeat data when inserting a new record. Normalization ensures every fact can be inserted independently.

3. To eliminate deletion anomalies — In an unnormalized table, deleting one record may unintentionally destroy other useful information. Normalization separates data so that deleting one fact does not destroy another.

4. To eliminate update anomalies — If a value is stored in many places, updating it in one place but missing another leads to inconsistent data. Normalization stores each fact once, so a single update keeps the database consistent.

5. To ensure data integrity and consistency — By removing partial and transitive dependencies, normalization enforces logical relationships between attributes and prevents conflicting data.

6. To make the database easier to maintain and extend — Smaller, well-defined tables with clear relationships are easier to modify, query, and manage.

Example of 2NF

A relation is in Second Normal Form (2NF) if it is in 1NF and every non-key attribute is fully functionally dependent on the whole primary key (i.e., there is no partial dependency on part of a composite key).

Unnormalized (1NF but not 2NF) relation:

Here the primary key is the composite key (StudentID, CourseID). But:

• StudentName depends only on StudentID (partial dependency)
• CourseName depends only on CourseID (partial dependency)

So this relation is not in 2NF.

Converting to 2NF — decompose into three tables so that every non-key attribute depends on the full key:

Student

Course

Enrollment

Now in the Enrollment table the only non-key attribute Marks depends on the whole composite key (StudentID, CourseID), and the partial dependencies have been removed. Hence the tables are in 2NF.

(a) First normal form

First normal form (1NF) requires every field to hold a single atomic value, with no repeating groups or multi-valued attributes. Higher forms deal with dependencies, not atomicity.

(d) Firewall

Backup, recovery and rollback are database-level techniques that protect data against loss, failure or incomplete transactions. A firewall is a network-level security device that filters traffic and is not a technique of the database itself.

A bank with many branches, ATMs and online channels would generally prefer a distributed database, usually with a strong central control. The reasons are:

1. Local access and speed: each branch keeps its customers' data close by, so everyday transactions are processed quickly without depending on one distant server.
2. Reliability and availability: if one site or the central server fails, other sites continue to work, so banking services are not stopped completely.
3. Scalability: new branches, ATMs or regions can be added as new nodes without redesigning the whole system or overloading one machine.
4. Reduced communication load and cost: most transactions are local, so less data travels over the network, and the load is shared among several servers.

A centralized database is simpler to secure and keep consistent, but it is a single point of failure and a bottleneck. For this reason, a bank chooses distribution, using replication, backups and strict security to keep data consistent and safe.

Businesses favor the relational model because it stores data in simple tables (relations) of rows and columns and offers many practical benefits.

1. Simplicity: data in tables is easy to understand, design and use, even for non-programmers.
2. Powerful querying: SQL lets users retrieve, insert, update and delete data with simple statements, without knowing how the data is physically stored.
3. Data integrity: primary keys, foreign keys and constraints keep data accurate and consistent and prevent duplicate or orphan records.
4. Reduced redundancy: normalization removes repeated data, which saves storage and avoids update anomalies.
5. Flexibility: tables can be linked through keys, so one-to-one, one-to-many and many-to-many relationships are handled easily and the structure can be changed without rewriting programs.
6. Security and standardization: access rights can be given per table or user, and the model is backed by a standard language and mature products (MySQL, Oracle, SQL Server), so skilled staff and support are easy to find.