ITI (Trade - COPA) Day-12

ITI COPA (Computer Operator & Programming Assistant) – Basic Notes

Topic- Database Concepts

---

1. Introduction to Database Concepts

In today’s digital age, data is one of the most valuable resources. Every organization such as banks, schools, hospitals, government offices, industries, and IT companies stores large amounts of data. Managing this data efficiently, securely, and accurately is very important. This is where Database Management Systems (DBMS) are used.

A database is an organized collection of related data stored electronically in a structured format so that it can be easily accessed, managed, and updated. Database concepts form the backbone of modern computer applications and information systems, making them a vital topic for ITI COPA students.

---

2. Concept of DBMS (Database Management System)

What is DBMS?

A Database Management System (DBMS) is a software system that allows users to define, create, store, retrieve, update, and manage data in a database.

Examples of DBMS

• MySQL

• Oracle

• Microsoft SQL Server

• MS Access

• PostgreSQL

Functions of DBMS

• Data storage and retrieval

• Data insertion, deletion, and modification

• Data security and access control

• Backup and recovery

• Data integrity enforcement

• Multi-user access control

Advantages of DBMS

• Reduces data redundancy

• Improves data consistency

• Provides data security

• Allows data sharing

• Ensures data integrity

• Easy data maintenance

Disadvantages of DBMS

• High initial cost

• Requires skilled personnel

• Complex system

• Performance overhead for small applications

---

3. Concept of RDBMS (Relational Database Management System)

What is RDBMS?

An RDBMS is a type of DBMS that stores data in the form of tables (relations) consisting of rows and columns. It is based on the relational model proposed by E.F. Codd.

Key Features of RDBMS

• Data stored in tables

• Each table has rows (records) and columns (fields)

• Uses keys (Primary Key, Foreign Key)

• Supports relationships between tables

• Uses Structured Query Language (SQL)

Examples of RDBMS

• MySQL

• Oracle

• SQL Server

• PostgreSQL

Difference Between DBMS and RDBMS

DBMS	RDBMS
Data stored as files	Data stored as tables
Less secure	More secure
No relationship support	Supports relationships
Single-user oriented	Multi-user oriented

---

4. Data Models

What is a Data Model?

A data model defines how data is structured, stored, and related within a database. It acts as a blueprint for database design.

Types of Data Models

1. Hierarchical Data Model

• Data organized in tree structure

• Parent-child relationship

• One-to-many relationship

• Example: File system

Advantages

• Simple structure

• Fast data access

Disadvantages

• Complex relationships

• Data redundancy

---

2. Network Data Model

• Data organized as graph

• Many-to-many relationships

• Each child can have multiple parents

Advantages

• More flexible than hierarchical

• Supports complex relationships

Disadvantages

• Complex design

• Difficult to maintain

---

3. Relational Data Model

• Data stored in tables

• Uses keys for relationships

• Most popular model

Advantages

• Simple structure

• Easy to understand

• High data integrity

---

4. Entity-Relationship (ER) Model

• Uses entities, attributes, and relationships

• Graphical representation

• Used in database design phase

---

5. Concept of DBA (Database Administrator)

Who is a DBA?

A Database Administrator (DBA) is a person responsible for managing, maintaining, and securing the database system.

Roles and Responsibilities of DBA

• Database installation and configuration

• User access control

• Backup and recovery

• Performance tuning

• Data security management

• Monitoring database health

• Enforcing integrity constraints

Importance of DBA

• Ensures smooth operation of databases

• Prevents data loss

• Maintains data security

• Improves system performance

---

6. Database Users

Database users are individuals who interact with the database in different ways.

Types of Database Users

1. Database Administrator (DBA)

2. Application Programmers

3. End Users

   • Casual users

   • Naive users

   • Sophisticated users

4. System Analysts

Each user has different access levels and responsibilities.

---

7. Database Schema

What is Database Schema?

A database schema is the logical structure of a database. It defines how data is organized, including tables, fields, relationships, constraints, and views.

Types of Schema

1. Physical Schema

   • Defines how data is physically stored

2. Logical Schema

   • Defines tables, fields, and relationships

3. View Schema

   • Defines user-specific views of data

Importance of Schema

• Provides structure to the database

• Helps in database design

• Improves data organization

---

8. Database Design and Normalization

What is Database Design?

Database design is the process of defining the structure of a database to store data efficiently and minimize redundancy.

Normalization

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.

---

Normalization Rules (Normal Forms)

1. First Normal Form (1NF)

• No repeating groups

• Atomic values only

• Each field contains only one value

2. Second Normal Form (2NF)

• Must be in 1NF

• No partial dependency

• All non-key attributes depend on full primary key

3. Third Normal Form (3NF)

• Must be in 2NF

• No transitive dependency

• Non-key attributes depend only on primary key

Advantages of Normalization

• Reduces data redundancy

• Improves data consistency

• Saves storage space

• Easier maintenance

---

9. Data Types

What are Data Types?

Data types define the type of data that can be stored in a database field.

Common Data Types

Numeric Data Types

• INT

• FLOAT

• DOUBLE

• DECIMAL

Character Data Types

• CHAR

• VARCHAR

• TEXT

Date and Time Data Types

• DATE

• TIME

• DATETIME

Boolean Data Type

• BOOLEAN

---

10. Data Integrity

What is Data Integrity?

Data integrity ensures accuracy, consistency, and reliability of data stored in a database.

Types of Data Integrity

1. Entity Integrity

   • Primary key must be unique and not null

2. Referential Integrity

   • Foreign key must match primary key

3. Domain Integrity

   • Data must be within defined domain

---

11. SQL Statements: DDL, DML, and DCL

DDL (Data Definition Language)

Used to define and modify database structure.

Commands

• CREATE

• ALTER

• DROP

• TRUNCATE

---

DML (Data Manipulation Language)

Used to manipulate data in tables.

Commands

• INSERT

• UPDATE

• DELETE

• SELECT

---

DCL (Data Control Language)

Used to control access permissions.

Commands

• GRANT

• REVOKE

---

12. Primary Key and Foreign Key

Primary Key

• Uniquely identifies each record

• Cannot be NULL

• Only one per table

Foreign Key

• Refers to primary key of another table

• Maintains relationship between tables

Enforcing Keys

• Ensures data integrity

• Prevents invalid data entry

---

13. Adding Indices

What is an Index?

An index is a database object used to speed up data retrieval operations.

Advantages of Index

• Faster search operations

• Improved query performance

Disadvantages

• Extra storage space

• Slower insert and update operations

---

14. Importance of Database Concepts for COPA Students

• Essential office skill

• Required for backend operations

• Used in application development

• Improves employability

• Widely used in IT industry

---

MCQ Questions (40 Questions – CBT Exam)

1. DBMS stands for:

A) Data Base Machine System
B) Data Backup Management System
C) Database Management System
D) Data Business Management System
Ans: C

2. RDBMS stores data in the form of:

A) Files
B) Trees
C) Tables
D) Graphs
Ans: C

3. Who proposed the relational model?

A) Bill Gates
B) Charles Babbage
C) E.F. Codd
D) Dennis Ritchie
Ans: C

4. Which is an example of RDBMS?

A) MS Word
B) MySQL
C) Windows
D) Linux
Ans: B

5. DBMS reduces:

A) Data security
B) Data redundancy
C) Data accuracy
D) Data size only
Ans: B

6. DBA stands for:

A) Data Backup Agent
B) Database Administrator
C) Database Analyst
D) Data Business Analyst
Ans: B

7. Who is responsible for database security?

A) End user
B) DBA
C) Programmer
D) Operator
Ans: B

8. A data model defines:

A) Hardware structure
B) Software code
C) Data organization
D) User interface
Ans: C

9. Which data model uses tables?

A) Hierarchical
B) Network
C) Relational
D) Tree
Ans: C

10. ER model consists of:

A) Files and folders
B) Entities and relationships
C) Tables and queries
D) Rows and columns
Ans: B

11. Database schema defines:

A) Data values
B) Logical structure
C) Backup
D) Reports
Ans: B

12. Which schema shows how data is physically stored?

A) Logical
B) View
C) Physical
D) External
Ans: C

13. Normalization is used to:

A) Increase redundancy
B) Improve data integrity
C) Increase file size
D) Reduce security
Ans: B

14. 1NF eliminates:

A) Keys
B) Repeating groups
C) Tables
D) Rows
Ans: B

15. 2NF removes:

A) Partial dependency
B) Transitive dependency
C) Redundancy only
D) Foreign keys
Ans: A

16. 3NF removes:

A) Partial dependency
B) Transitive dependency
C) Primary key
D) Tables
Ans: B

17. INT is a:

A) Character type
B) Numeric type
C) Date type
D) Boolean type
Ans: B

18. VARCHAR stores:

A) Numbers
B) Fixed characters
C) Variable characters
D) Date
Ans: C

19. Data integrity ensures:

A) Data duplication
B) Data accuracy
C) Data deletion
D) Data backup
Ans: B

20. Primary key cannot be:

A) Unique
B) Null
C) Indexed
D) Numeric
Ans: B

21. Foreign key is used to:

A) Uniquely identify records
B) Store large data
C) Link tables
D) Create index
Ans: C

22. Entity integrity is related to:

A) Foreign key
B) Domain
C) Primary key
D) Index
Ans: C

23. Referential integrity is related to:

A) Primary key
B) Foreign key
C) Index
D) Domain
Ans: B

24. CREATE command belongs to:

A) DML
B) DCL
C) DDL
D) TCL
Ans: C

25. INSERT command belongs to:

A) DDL
B) DML
C) DCL
D) View
Ans: B

26. GRANT command is part of:

A) DDL
B) DML
C) DCL
D) TCL
Ans: C

27. SELECT command is used to:

A) Insert data
B) Delete data
C) Retrieve data
D) Create table
Ans: C

28. DROP command is used to:

A) Remove records
B) Remove table structure
C) Update data
D) Grant access
Ans: B

29. Index is used to:

A) Increase redundancy
B) Improve query speed
C) Reduce security
D) Delete data
Ans: B

30. Index requires:

A) No space
B) Extra storage
C) No CPU
D) No memory
Ans: B

31. Which user designs database structure?

A) End user
B) DBA
C) Operator
D) Guest
Ans: B

32. End users mainly:

A) Design database
B) Maintain database
C) Use database
D) Secure database
Ans: C

33. Data redundancy means:

A) Data accuracy
B) Data repetition
C) Data security
D) Data integrity
Ans: B

34. Which DB object speeds up searching?

A) View
B) Index
C) Trigger
D) Procedure
Ans: B

35. Which is NOT a DML command?

A) INSERT
B) UPDATE
C) DELETE
D) CREATE
Ans: D

36. Database users include:

A) Only DBA
B) Only programmers
C) DBA, programmers, end users
D) Hardware users
Ans: C

37. Logical schema represents:

A) Physical storage
B) User view
C) Table structure
D) Backup
Ans: C

38. Domain integrity restricts:

A) Duplicate values
B) Data type and range
C) Table relationship
D) Key values
Ans: B

39. A table row is also called:

A) Field
B) Attribute
C) Record
D) Column
Ans: C

40. Database normalization mainly improves:

A) Hardware speed
B) Data quality
C) Network speed
D) Memory size
Ans: B