Activity: DBMS Fundamentals

Brief Intro — DBMS Fundamentals

Every modern app, from social media to banking, relies on a Database Management System (DBMS) to store and organize massive amounts of information. Moving beyond simple lists, a DBMS provides a structured, secure, and multi-user way to manage the data that powers our digital lives.

In this activity, you'll explore different database types, the components of a database system, and the powerful relational model used by most modern software.

Prerequisites

• Basic understanding of computers and data storage.
• Familiarity with tables, rows, and columns (from earlier classes).
• Exposure to real-world systems like school records, hospital systems, and billing.

Data vs. Information

Activity 1.1 — Data or Information?

1. “23, 45, 67” Data (raw numbers)
2. “The average score is 45” Information (processed result)
3. “Delhi, Mumbai, Kolkata” Data (raw city names)
4. “5 students are from Delhi” Information (aggregated data)

What is a Database?

A database is a structured collection of data stored electronically. It allows organized storage, retrieval, and management of data.

Examples: School student records, hospital patient databases, electricity billing systems, library catalogs, phone contacts.

What is a DBMS?

A Database Management System (DBMS) is software that allows users to create, define, manipulate, and manage databases. It acts as an interface between the user and the database.

History of DBMS:

Four Main Types of DBMS:

Which is most widely used? Relational DBMS (RDBMS)

What is a Data Warehouse?

A data warehouse is an information system that contains historical and cumulative data from one or multiple sources. It facilitates a single version of truth for a company for decision making and forecasting. It simplifies the reporting and analysis process of the organization.

Flat File vs. Relational Databases

Centralized vs. Distributed Databases (Overview)

Compact Reference: All Database Types

Five Components of a Database System

1. Hardware — Physical electronic devices: computers, I/O devices, storage devices. Provides the interface between computers and real-world systems.
2. Software — Programs to manage and control the database: DBMS software, operating system, network software, and application programs.
3. Data — Raw and unorganized facts (numbers, characters, symbols, images) that need processing to become meaningful.
4. Procedure — Instructions and rules for using the DBMS: documented methods for designing, running, and managing the database.
5. Database Access Language — Language used to access, enter, update, and retrieve data (e.g., SQL). Users write commands and submit them to the database.

Advantages of DBMS

1. Offers a variety of techniques to store and retrieve data.
2. Efficient handler to balance the needs of multiple applications using the same data.
3. Uniform administration procedures for data.
4. Application programmers are never exposed to details of data representation and storage.
5. Uses powerful functions to store and retrieve data efficiently.
6. Offers Data Integrity and Security.
7. Imposes integrity constraints for high-level protection against unauthorized access.
8. Schedules concurrent access so that only one user accesses the same data at a time.
9. Reduced Application Development Time.

Disadvantages of DBMS

1. High cost — Hardware and software costs are quite high, increasing the organization’s budget.
2. Complexity — Most DBMS are complex; training is required for users.
3. Single point of failure — In some setups, all data in a single database can be lost due to power failure or corruption.
4. Data loss risk — Multiple concurrent users can sometimes lead to data loss.
5. Limited computation — DBMS cannot perform sophisticated calculations.

Tables, Attributes, Tuples & Fields

The relational model expresses data and relationships in the form of tables (also called relations).

• Table (Relation): A collection of related data organized in rows and columns (e.g., a Student table).
• Attribute (Column/Field name): A property or characteristic of the relation (e.g., Roll_No, Name, Department).
• Tuple (Row): A single real-world entity or record in a relation (e.g., one student’s record).
• Field (Data value): The actual data value in a specific row and column (e.g., “Ram” in the Name column).

Example — Student Relation:

Roll_No, Name, and Department are attributes. Each row is a tuple. Each cell value is a field.

Characteristics of the Relational Model

1. Each relation in a database must have a distinct (unique) name to separate it from others.
2. A relation must not have two attributes with the same name; each attribute must be distinct.
3. Duplicate tuples must not be present in a relation.
4. Each tuple must have exactly one data value for each attribute (atomic values).
5. Tuples in a relation do not need to follow a significant order — relations are not order-sensitive.
6. Attributes of a relation also do not need to follow a specific order.

Popular RDBMS

Keys are fundamental to the relational model. A key is a value (or set of values) used to identify a record in a table uniquely. Keys can also establish relationships among columns and tables.

Activity 5.1 — Types of Keys (Definitions & Examples)

Given the Student table: Student_ID, Roll_No, Name, Dept_ID, Phone

Activity 5.2 — Key Identification Practice

In the Employee table: Emp_ID, Emp_Name, PAN_Number, Dept_ID, Salary. Emp_ID is the primary key and PAN_Number is also unique.

1. Primary Key: Emp_ID
2. Candidate Keys: Emp_ID and PAN_Number
3. Alternate Key: PAN_Number (candidate key not chosen as primary)
4. Foreign Key: Dept_ID (references Department table)
5. Super Key: (Emp_ID, Emp_Name) — any superset of a candidate key

Integrity constraints are conditions that must hold for a relation to be valid. They are derived from real-world rules the database represents.

Three Main Categories

Four basic update operations performed on relational databases:

Important: Whenever any of these operations are applied, all integrity constraints specified on the database schema must never be violated.

Advantages of the Relational Model

1. Simplicity: Simpler than the hierarchical and network models.
2. Structural Independence: Only concerned with data, not with internal structure — improves performance.
3. Easy to Use: Tables with rows and columns are natural and simple to understand.
4. Query Capability: High-level query languages (like SQL) avoid complex database navigation.
5. Data Independence: Database structure can be changed without modifying applications.
6. Scalable: Can be enlarged by adding more records (rows) and fields (columns) to enhance usability.

Disadvantages of the Relational Model

1. Field length limits: Some relational databases have limits on field lengths that cannot be exceeded.
2. Complexity with growth: As data grows and relationships become more complicated, the database can become complex.
3. Isolated databases: Complex systems may lead to isolated databases where information cannot be shared across systems.

What to Expect in the Assessment:

• Differentiate between data, information, and databases with real examples.
• Explain DBMS, its history, and the four main types (Hierarchical, Network, Relational, Object-Oriented).
• Identify and describe all database types (Flat File, Personal, Cloud, NoSQL, Graph, OLTP, etc.).
• List the five components of a database system.
• List the advantages and disadvantages of DBMS.
• Understand the relational model: tables, attributes, tuples, fields, and its 6 characteristics.
• Distinguish between candidate, primary, super, alternate, foreign, and composite keys.
• Apply integrity constraints: key, domain, and referential integrity.
• Perform relational operations: insert, update, delete, select.
• Evaluate advantages and disadvantages of the relational model.

Key Points to Remember:

• Data is raw; information is processed data; a database is a structured collection of data.
• DBMS is the software; the database is the data itself — they are not the same thing.
• A primary key must be unique and NOT NULL; a foreign key references a primary key in another table.
• A super key contains candidate keys; an alternate key is a candidate key not chosen as primary.
• Integrity constraints enforce data validity: key (uniqueness), domain (valid values), referential (valid references).
• The relational model is the most widely used because of its simplicity and powerful query capabilities.
• A data warehouse is for historical/analytical data, not for day-to-day transactional data.