Database Management System Exercise Solutions | KalviGavani

A database is an organised collection of related data that can be easily accessed, managed, and updated. Data in a database is structured in tables, records, and fields.

A DBMS (Database Management System) is software that provides tools to create, store, retrieve, update, and manage data in a database. It ensures data integrity, security, and concurrency.

Examples of DBMS software: 1. MySQL — open-source, widely used for web applications 2. Oracle — enterprise-grade, used by large organisations 3. Microsoft SQL Server — used in Windows-based enterprise environments 4. SQLite — lightweight, embedded in mobile and desktop applications

The hierarchical model organises data in a tree-like structure where:

• Each node represents a record type
• Parent nodes can have multiple child nodes
• Each child node has exactly one parent node
• Relationships are one-to-many (1:N)

Example: A college database:

In this structure:

• College is the root (parent)
• Departments are children of College
• Teachers and Courses are children of their respective Departments
• A Teacher belongs to only one Department (one parent)

Advantages: Simple structure, fast data access through predefined paths Disadvantages: Cannot represent many-to-many relationships easily; adding new relationships requires restructuring the entire hierarchy

SQL (Structured Query Language) is a standard language used to interact with relational databases. It allows users to create, read, update, and delete data.

SQL sub-languages:

Normalization is the process of organising data in a database to reduce redundancy and eliminate insert, update, and delete anomalies.

1NF (First Normal Form):

• Every column contains atomic (indivisible) values
• No repeating groups or arrays
• Each row is unique (has a primary key)

2NF (Second Normal Form):

• Must be in 1NF
• No partial dependency — every non-key attribute must depend on the entire primary key (not just part of it)
• Relevant only for tables with composite primary keys

3NF (Third Normal Form):

• Must be in 2NF
• No transitive dependency — non-key attributes must not depend on other non-key attributes
• Every non-key attribute depends only on the primary key

A data model defines how data is structured, stored, and related in a database. The main data models are:

• Data is organised in a tree structure (parent-child)
• Each parent can have multiple children; each child has one parent
• Supports one-to-many relationships
• Navigation: top-down through predefined paths
• Example: IBM IMS (Information Management System)

Advantages: Simple, fast for known queries Disadvantages: Rigid structure, hard to represent many-to-many relationships

• Data is organised as a graph (nodes and edges)
• A child can have multiple parents (many-to-many relationships)
• More flexible than hierarchical model
• Uses sets to represent relationships
• Example: IDMS (Integrated Database Management System)

Advantages: Handles many-to-many relationships, efficient data access Disadvantages: Complex structure, difficult to design and maintain

• Data is stored in tables (relations)
• Each table has rows (tuples) and columns (attributes)
• Relationships are established through primary and foreign keys
• Most widely used model
• Example: MySQL, Oracle, PostgreSQL

Students Table:

Advantages: Simple, flexible, powerful query language (SQL), data integrity Disadvantages: Can be slower for complex hierarchical queries

• Data is stored as objects with attributes and methods
• Supports complex data types (inheritance, encapsulation, polymorphism)
• Example: ObjectDB, db4o

The relational model was introduced by E.F. Codd in 1970. It organises data into tables (called relations) with rows and columns. Relationships between tables are established through keys.

Key Terminology:

• Relation (Table) — a named structure with rows and columns
• Tuple (Row/Record) — a single data entry in a table
• Attribute (Column/Field) — a named column with a specific data type
• Domain — the set of all valid values for an attribute
• Degree — the number of columns in a table
• Cardinality — the number of rows in a table
• Schema — the structure definition of a table (column names and data types)

Example — School Database:

Students Table:

Marks Table:

Relationship: The Roll_No in Marks table is a foreign key referencing the Roll_No in Students table. This links each mark to the corresponding student.

Properties of the Relational Model: 1. Each column has a unique name (attribute name) 2. Each column contains values of the same data type (domain) 3. Each row is unique (enforced by primary key) 4. The order of rows and columns does not matter 5. Each cell contains only one atomic value (1NF)

Advantages:

• Simple tabular structure is easy to understand
• Powerful query language (SQL)
• Data integrity through constraints (primary key, foreign key, NOT NULL, UNIQUE)
• Data independence — changes to storage don't affect queries
• Supports multiple users with concurrency control

Keys are crucial in relational databases for identifying records and establishing relationships.

• Uniquely identifies each record in a table
• Cannot contain NULL values
• Cannot have duplicate values
• Only one primary key per table

Example: In a Students table, Roll_No can be the primary key because each student has a unique roll number.

CREATE TABLE Students (
    Roll_No INT PRIMARY KEY,
    Name VARCHAR(50),
    Class VARCHAR(10)
);

• A column in one table that references the primary key of another table
• Creates a relationship between two tables
• Can contain duplicate values
• Can contain NULL values (if allowed)

Example: Roll_No in the Marks table is a foreign key referencing Roll_No in the Students table.

CREATE TABLE Marks (
    Mark_ID INT PRIMARY KEY,
    Roll_No INT,
    Subject VARCHAR(30),
    Marks INT,
    FOREIGN KEY (Roll_No) REFERENCES Students(Roll_No)
);

• A column (or set of columns) that can uniquely identify each record
• A table can have multiple candidate keys
• One candidate key is chosen as the primary key

Example: In a Students table, both Roll_No and Admission_No can uniquely identify students. Both are candidate keys; one is chosen as the primary key.

• A candidate key that is NOT chosen as the primary key
• Acts as a backup unique identifier

Example: If Roll_No is chosen as the primary key, then Admission_No becomes the alternate key.

• A primary key made up of two or more columns
• Used when no single column can uniquely identify records

Example: In a Marks table, the combination of Roll_No and Subject can form a composite primary key.

• A set of one or more columns that can uniquely identify a record
• May contain extra columns beyond what is necessary

Example: {Roll_No, Name} is a super key because Roll_No alone is sufficient, but adding Name still maintains uniqueness.

Normalization is the step-by-step process of organising data to reduce redundancy and eliminate anomalies.

First Normal Form (1NF): A table is in 1NF if:

• Every column contains atomic (indivisible) values
• There are no repeating groups
• Each row is unique (has a primary key)

Unnormalised table (NOT in 1NF):

The "Subjects" column contains multiple values — this violates 1NF.

After 1NF:

Now every column has atomic values.

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

• It is in 1NF
• There is no partial dependency — every non-key attribute depends on the entire primary key

1NF table with partial dependency (NOT in 2NF):

Primary key: (Student_ID, Subject) — composite key

Partial dependency: Student_Name depends only on Student_ID, not on the full composite key. This is a partial dependency.

After 2NF (split into two tables):

Students:

Subjects:

Now every non-key attribute depends on the entire primary key.

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

• It is in 2NF
• There is no transitive dependency — non-key attributes do not depend on other non-key attributes

2NF table with transitive dependency (NOT in 3NF):

Transitive dependency: City depends on Zip_Code, and Zip_Code depends on Student_ID. So City transitively depends on Student_ID through Zip_Code.

After 3NF (split into two tables):

Students:

Cities:

Now there are no transitive dependencies.

Summary:

This creates a table named "Students" with four columns:

• Roll_No — integer type, set as primary key
• Name — variable character (up to 50 characters), cannot be NULL
• Class — variable character (up to 10 characters)
• Marks — integer type

This removes the entire table structure along with all data — it cannot be rolled back.