1.

Define (Any 5 terms – 2 Marks Total)

i) Data Abstraction
Data abstraction refers to hiding the complexity of the database and showing only
essential information to the user.
Levels of abstraction:

• Physical Level: How data is stored

• Logical Level: What data is stored
• View Level: How data is viewed by users

ii) DBMS (Database Management System)

DBMS is software that stores, retrieves, and manages data in databases.
It provides tools for data integrity, concurrency control, backup, and security.
Mostly used in Various Company and Institutions….(EX. Hospital, Website, Bank, etc)
Examples: MySQL, Oracle, MongoDB.

iii) Cardinality
Cardinality refers to the number of rows (tuples) in a table.
For example: A student table with 50 rows has a cardinality of 50.

iv) Degree
Degree refers to the number of columns (attributes) in a table.
For example: A student table with 4 columns (Roll No, Name, Age, Branch) has a degree of 4.

v) Relational Constraints(RULES)
Relational constraints are rules applied to the data in a relational database to ensure accuracy, validity, and consistency. These constraints are
enforced by the DBMS automatically. Types of Relational Constraints:
• Domain Constraint: Valid values for an attribute(column)

• Key Constraint: Enforces uniqueness in a relation.

2. Advantages, Characteristics, and Applications of DBMS (2 Marks)

Advantages of DBMS: Applications of DBMS:

• Data Redundancy Control Characteristics of DBMS: • Banking systems

• Data Consistency • Data Independence • College Management

System
• Improved Data Sharing • Concurrent Access
• Railway Reservation
• Data Security • Security and Authorization
• Online Shopping & E-
• Data Backup • ACID Properties
commerce
• Data Recovery • Data Abstraction
• Hospital and Health Records
• Query Support via SQL
3. File System vs Database Management System (2 Marks)

Feature File System DBMS

Redundancy High Low

Security Less High

Access Manual Query-based (SQL)

Relationship Hard to manage Easily handled using foreign keys

Integrity Not guaranteed Easily enforced

Backup/Recovery Manual Automatic

Data Consistency Difficult to maintain when same data Maintains consistency using
is in multiple files. constraints and rules.
Data Abstraction No abstraction. Users deal directly Supports abstraction through views,
with files. schemas, and levels.

4. Three-Level Architecture for Database System (4 Marks)

Three-Level Architecture for Database System, this model
defines three levels of abstraction:

1. External Level (View Level)

• High level for data abstraction
• Closest to end users
• Shows only relevant data to different users
• Hides all internal and logical details

2. Conceptual Level (Logical Level)

• Intermediate level — lies between External and
Internal levels
• Describes entire logical structure of the database
• Defines entities, relationships, and constraints
• Independent of physical storage
• Ensures consistency and data integrity

3. Internal Level (Physical Level)

• Lowest level — completely hidden from end users
• Describes how data is physically stored in memory
• Includes file organization, indexing, compression
• Optimized for performance and storage efficiency

Importance:

• Data Abstraction

• Data independence

• Improved security

• Multiple User Views

5. E.F. Codd’s 12 Rules (Any 8 – 4 Marks)

Codd's 12 rules are a set of thirteen rules (numbered zero to twelve) proposed by Edgar F. Codd, a pioneer of the
relational model for databases, designed to define what is required from database management system a in order for
it to be considered relational, i.e., a relational database management system RDBMS

Rule TRICK TO LEARN Rule Name Explanation (Short)

No.

Rule 0 Foundation Rule A system must qualify as a relational DBMS and It support all
rules, otherwise it is not relational.

Rule 1 Information Rule All data must be stored in tables (relations).

Rule 2 Guaranteed Access Rule Every data item must be accessible by table name, primary key
(row), and column name.

Rule 3 Systematic Treatment of NULLs NULLs must be handled uniformly (properly)

Rule 4 Dynamic Online Catalog Metadata (schema info) must be stored in relational form and
queried like data.

Rule 5 Comprehensive Data Sub- One language (like SQL) must support all data operations.
language

Rule 6 View Updating Rule Views should be updatable if logically possible.

Rule 7 Insert, Update, Delete Should support set-level operations, not just one row at a time.

Rule 8 Physical Data Independence Storage changes should not affect application.

Rule 9 Logical Data Independence Changes in table structure should not affect user apps.

Rule Integrity Independence Integrity rules (e.g., constraints) must be stored in the
10 database, not in app code.

Rule Distribution Independence DBMS must work the same even if data is distributed across
11 locations.

Rule Non-subversion Rule Low-level access must not bypass security or relational
12 integrity.

To be considered truly relational, a DBMS must follow Codd’s 12 rules. (Note: Most modern RDBMS follow many but not all 12 rules)
I, G, and S decided to DO Compare their Views, With a plan to Insert P, L, I, D, and N into the story of progress.

6. Relational, Network & Hierarchical Models (4 Marks) (Types of model:---)

Relational Database Model

i. Data stored in tables (relations)

• Example: STUDENT(RollNo, Name, Age)
ii. Tables have rows (tuples) and columns (attributes)
• Each row = one record, column = one field
iii. Primary Key uniquely identifies each row
• Example: RollNo in STUDENT table
iv. Constraints ensure data integrity
• Types: Primary Key, Foreign Key, NOT NULL(Compulsory)
v. Normalization reduces redundancy
• Example: Separate STUDENT and DEPARTMENT tables
vi. Used in modern RDBMS software
• Example: MySQL, PostgreSQL, Oracle, SQLite
 Network Database Model
Structure & Relationships
• Uses graph-based structure for data organization.
Example: A Customer can place multiple Orders, and an
Order can belong to multiple Customers.
• Supports many-to-many relationships between records.
Example: A Store can have many Salesmen, and a
Salesman can work in many Stores.

Access & Navigation

• Follows navigational access method.

You move from Manager → Salesman → Customer → Order. (WORKFLOW)

• Requires detailed knowledge to navigate data.

Programmers must know all link paths between entities.

Data Access & Performance

• Provides faster access than hierarchical model.

Direct pointer access improves speed in Customer-Order-Item networks.

• Maintains data integrity using set relationships.

Ensures Order can’t exist without a linked Customer and Salesman.

Hierarchical Database Model

Structure & Relationships

• Data is organized in tree structure (parent-child).
Example: SHOES (Root) → WOMEN SHOES, MEN SHOES
• Each parent has multiple child nodes.
WOMEN SHOES (parent)→ (child) High Heels, Bellies
MEN SHOES (parent)→ (child) Sports Shoes, Sneakers
• Follows a 1-to-many relationship model.
One Shoe Category can have many Subtypes.
• Only one parent per child is allowed.
High Heels is child of Women Shoes only.

Data Access & Navigation

• Uses navigational access from top to bottom.
To access Bellies, navigate: Shoes → Women Shoes → Bellies
• Data is accessed in a predefined path.
Fixed order makes traversal predictable but rigid.

Data Integrity & Performance

• Provides fast data retrieval for hierarchical data.

Ideal when categories are fixed and repetitive.

• Ensures data integrity through parent-child linkage.

No child can exist without a parent node.

Complexity & Limitations

• Difficult to re-structure the hierarchy.

Adding new subtypes like Loafers needs schema change.
• Redundancy if similar data exists under different parents.
If Sneakers exist under both Men and Kids, it must be repeated.
 Feature Relational Model Network Model Hierarchical Model

Structure Table Graph Tree

Relationships Many-to-many Many-to-many One-to-many

Flexibility High Medium Low

Data Access SQL Navigation using pointers Parent-to-child traversal

Example MySQL (Oracle, Xampp Server) Integrated Data Store (IDS) IBM IMS

Ease of Use Easy to design and modify Complex to manage Rigid and difficult to update

1. Keys and Types of Keys (2 Marks)

Key: A key is an attribute or a set of attributes that

uniquely identifies a record (tuple) in a table.

Types of Keys:

1. Primary Key – Uniquely identifies each record

(e.g., RollNo in Student table).

2. Candidate Key – All possible keys that can act

as primary key.

3. Super Key – A set of attributes that uniquely

identifies a tuple.

4. Foreign Key – A key used to link two tables using Primary key.

5. Unique Key – Ensures unique non-duplicate values, allows one NULL.

6. Composite Key: Combination of multiple columns.

2. Types of Data Integrity Constraints (4 Marks)

Data Integrity ensures the accuracy, consistency, and validity of data in a database.

Types of Integrity Constraints(Rules):

1. Domain Integrity
Ensures values in a column are of the correct data type and within an acceptable range.
Example: Age must be an integer between 0 and 120.
CREATE TABLE student (
2. Entity Integrity
roll_no INT PRIMARY KEY,
Ensures that the Primary Key is unique and not NULL (Compulsory) to identify each record.
Example: Student_ID in Student table cannot be NULL or duplicate. name VARCHAR(50),

3. Referential Integrity marks INT NOT NULL,

Maintains consistency between related tables using Foreign Keys.
city VARCHAR(30)
Example: Course_ID in Enrollment table must exist in Course table.
);
 4. User-defined Integrity
Business rules enforced by users, beyond system-defined rules.
Example: Discount must not exceed 50%.

3. ER Diagram for Hospital System (4

Marks)

Entities:

• Hospital

• Patient

• Doctor

• Medical_Record

4. Define Normalization. Explain 2NF with Example (2 Marks)

What is Normalization?
Normalization is the process of organizing data in a database to:
• Remove duplicate (repeating) data 2705
• Ensure that data is stored logically and efficiently
It breaks large tables into smaller ones and sets rules (called normal forms) to keep the data clean.

What is 2NF (Second Normal Form)?

A table is in 2NF if:
1. It is already in 1NF (no repeating groups, only atomic values).
2. There are no partial dependencies, meaning:
o All non-key columns must depend on entire primary key, not just part of it.

Easy Real-Life Example

Imagine a student marks record:
Table (Not in 2NF):
StudentID StudentName Subject Marks
101 Rahul Math 85
101 Rahul Science 90
102 Priya Math 80

• Primary Key = (StudentID, Subject)

• Problem: StudentName depends only on StudentID, not on the whole key.

After converting to 2NF:

Student Table:
StudentID StudentName
101 Rahul
Now:
102 Priya • StudentName is in its own table.
Marks Table: • All non-key attributes in Marks Table
StudentID Subject Marks
depend on the full key(StudentID)
101 Math 85
101 Science 90
→ 2NF
102 Math 80
 Composite Key:
Combination of
multiple columns.
5. Functional Dependencies & Types (4 Marks)

Functional Dependency (FD):

A relationship where one attribute uniquely determines another.

If A → B, then B is functionally dependent on A.

Types of Functional Dependencies:

1. Trivial FD: A → A or A → part of A

E.g., {RollNo, Name} → RollNo

2. Non-trivial FD: A → B, where B is not a subset of A

E.g., RollNo → Name

3. Partial FD: In a composite key, a non-key depends on part of the key

E.g., (StudentID, Subject) → StudentName

4. Full FD: Non-key depends on the whole composite key

E.g., (StudentID, Subject) → Marks

5. Transitive FD: A → B and B → C implies A → C

E.g., RollNo → Class, Class → HOD ⇒ RollNo → HOD

6. Define ACID Properties (2 Marks)

ACID ensures reliable processing of database transactions.

1. Write syntax for creating and renaming a table. (2M) CREATE TABLE student (

roll_no INT PRIMARY KEY,

Create Table Syntax:
name VARCHAR(50),
CREATE TABLE table_name (
marks INT NOT NULL,
column1 datatype [constraint],
city VARCHAR(30)
column2 datatype [constraint],
);
...

);

Rename Table Syntax:

RENAME TABLE Student To STU;
RENAME TABLE old_table_name TO new_table_name;

2. State and explain any 4 DDL commands. (2M)

DDL (Data Definition Language):
It is a set of SQL commands used to define and modify the structure of database objects like tables, schemas, and indexes.

Command Description Example

CREATE Creates a new table/database. CREATE TABLE dept (...);

ALTER Modifies table structure. ALTER TABLE student ADD dob DATE;

DROP Deletes a table/database. DROP TABLE stud;

RENAME Changes table name. RENAME TABLE old TO new;

All these commands define or alter the schema of a database.

3. Write SQL queries for following: (4M) CREATE TABLE student (

i) roll_no INT PRIMARY KEY,
Create table student with following attributes: name VARCHAR(50),
• Roll no. as Primary Key
• Name marks INT NOT NULL,
• Marks as NOT NULL
• City city VARCHAR(50)

);
Creates a table with constraints like PRIMARY KEY and NOT NULL.

ii) Add column Date of Birth in above student table:

ALTER TABLE student
ADD dob DATE;
Adds a new column for storing birth dates.

iii) Increase the size of the attribute name by 10 in the above student table:
ALTER TABLE student
MODIFY name VARCHAR(60);
Increases character limit of name from 50 to 60.

iv) Change name of Student table to stud:

RENAME TABLE student TO stud;
Changes table name while keeping data intact.
 GRANT INSERT, UPDATE ON login_system.users TO
4. Write SQL queries for following: (4M)
'user1'@'localhost';
i) Create user named 'user1' having Password '1234':

CREATE USER 'user1'@'localhost' IDENTIFIED BY '1234'; This command creates a new user called user1 that
can connect from the local machine with the password
'1234'.
Example: You’re setting up access for a new student to the MySQL server.

ii) Assign 'INSERT' and 'UPDATE' Privileges to 'user1':

GRANT INSERT, UPDATE ON *.* TO 'user1'@'localhost'; This allows user1 to insert and update records in all
databases (*.*) on the server.
Example: Let’s say you want user1 to manage records in the students table—they now can add and modify data.

iii) Remove 'UPDATE' Privilege assigned to 'user1':

REVOKE UPDATE ON *.* FROM 'user1'@'localhost'; This removes just the UPDATE permission, while keeping
INSERT still allowed.
Example: You trust user1 to add data but no longer want them to make changes to existing entries.

The *.* means:

• The first * (before the dot) refers to all
databases.
• The second * (after the dot) refers to all tables in
those databases.
So, *.* means:
"All tables in all databases"