Database Management Systems (DBMS): Tables, Keys, SQL, Relationships and MS Access डेटाबेस मैनेजमेंट सिस्टम (DBMS): टेबल, की, SQL, रिलेशनशिप और MS Access

Database Management Systems (DBMS)

Schools, libraries and offices need organised records of students, books, employees and examination applications. A database organises this data. A DBMS is the software used to create, search and manage databases.

1. Data, Information, Database and DBMS

Important distinction: A database is the collection of data; a DBMS is the software that manages it. They are not the same thing.

2. Main Functions of a DBMS

• Creating database and table structures.
• Adding, changing and deleting records.
• Retrieving information that satisfies specified conditions.
• Enforcing data rules and relationships.
• Providing appropriate access to authorised users.
• Managing work by concurrent users.
• Providing backup and recovery facilities.

Benefit: Suitable database design reduces unnecessary duplication and helps maintain consistent data.

3. Tables, Fields and Records

Relational databases organise data into tables. Consider this example Employees table:

• Table: A collection of related records.
• Field/Column: A particular characteristic, such as EmployeeName.
• Record/Row: Details about one entity, such as the complete record for employee 101.
• Attribute: The relational term for a column characteristic.
• Tuple: The relational term for a row.

This example contains 3 records and 3 fields. The header row is not a data record.

Degree: The number of attributes in a relation. Cardinality: The number of tuples in a relation. Both are 3 in this example.

4. DBMS, RDBMS and Data Models

RDBMS stands for Relational Database Management System. It is a DBMS based on the relational model, where tables and relationships are important.

Relationship: Every RDBMS is a DBMS, but not every DBMS must be relational.

MySQL, PostgreSQL, Oracle Database and Microsoft SQL Server are examples of relational database systems.

5. Primary Key and Other Important Keys

A key can consist of one or more columns used to identify records or establish relationships.

Primary key rules: Its values must be unique and cannot be NULL. A table has one primary key, but that key can contain multiple columns.

Example: EmployeeID can identify an employee. A name alone may be unsuitable because different employees can share the same name.

Composite example: If a student can enrol in each course only once, the combination of StudentID and CourseID can identify an Enrolments record.

6. Foreign Keys and Referential Integrity

A foreign key is a column or set of columns referencing a primary key or suitable unique key. A reference can also point to the same table.

Example: DepartmentID identifies departments in a Departments table. DepartmentID in Employees can reference the department where an employee works.

• Foreign key values can repeat because several employees can belong to the same department.
• A foreign key can allow NULL unless another rule, such as NOT NULL, prevents it.
• Referential integrity: Maintaining valid relationships, such as requiring a recorded employee department to exist in the referenced department list.

7. Relationships Between Tables

A many-to-many relationship is commonly represented through a junction table, such as Enrolments between Students and Courses.

8. Data Types and NULL

• Integer: Whole numbers, such as the number of books.
• Decimal: Values with specified decimal precision, such as amounts.
• Text/Character: Names, addresses or identification codes.
• Date/Time: Dates and times.
• Boolean/Yes-No: True–false states; naming and storage depend on the product.

NULL generally represents an unknown or unavailable value. It is not the same as zero or empty text.

Example: A Marks value of 0 can mean zero marks. NULL can mean that the marks are not yet available.

9. SQL and Important Commands

SQL stands for Structured Query Language. It is used to retrieve and change data and work with structures in relational databases. SQL itself is not the name of a DBMS.

10. SELECT, WHERE and ORDER BY

Assume a Candidates table contains CandidateID, CandidateName and Marks.

SELECT CandidateName, Marks
FROM Candidates
WHERE Marks >= 60
ORDER BY Marks DESC, CandidateID ASC;

• SELECT: Specifies the columns to display.
• FROM: Identifies the source table.
• WHERE: Selects records satisfying a condition.
• ORDER BY: Specifies the result order.
• DESC: Descending order; higher marks first.
• ASC: Ascending order; smaller CandidateID first among equal marks.

Important fact: Without ORDER BY, a fixed result order should not be assumed. Having a primary key does not itself guarantee SELECT output order.

Testing NULL: Use WHERE Marks IS NULL to find unavailable marks. Marks = NULL is not the correct ordinary test.

11. Aggregate Functions and JOIN

Example: For Marks values of 50, NULL and 70, COUNT(*) = 3, COUNT(Marks) = 2 and AVG(Marks) = 60.

GROUP BY groups records with common values, such as counting employees by department. HAVING applies conditions to groups.

A JOIN combines related data in a query result:

• INNER JOIN: Returns matching records from both sides according to the join condition.
• LEFT JOIN: Retains all left-side records and matching right-side records; unmatched right-side values appear as NULL.

A JOIN does not permanently turn the original tables into one table.

12. DELETE, TRUNCATE and DROP

13. Redundancy, Normalization, Indexes and Views

• Data redundancy: Unnecessary repetition of the same fact.
• Data inconsistency: Conflicting values for the same fact in different records.
• Normalization: Organising tables and relationships to reduce unnecessary repetition and update-related problems.
• Index: An auxiliary structure that can speed up suitable searches, with additional storage and maintenance costs.
• View: A named representation based on a query; an ordinary view does not store a separate permanent copy of its result.

Example: Keeping department names in Departments and referencing DepartmentID from Employees can reduce repeated department information in employee records.

Remember: An index does not mean records will be displayed in ascending order. Use ORDER BY to specify the result order.

14. Transactions, ACID and DBA

A transaction is a logical unit of related database operations. For example, issuing a library book may require both adding an issue record and reducing the available-copy count.

COMMIT accepts a transaction's changes. ROLLBACK reverses uncommitted changes within its applicable scope.

DBA: Database Administrator. Responsibilities include permissions, security, backups, recovery and performance monitoring.

A backup is a recoverable copy of data. Recovery restores the database to an appropriate state after a problem. Creating backups alone is insufficient; testing restoration is also important.

15. Microsoft Access and Quick Revision

Microsoft Access is a desktop database application. A typical modern Access database uses .accdb; the older format uses .mdb.

• Row = Record; Column = Field.
• A primary key is unique and non-NULL.
• Foreign key values can repeat.
• WHERE selects records; ORDER BY sorts the result.
• COUNT(*) counts all rows; COUNT(Column) excludes NULL.
• DELETE removes rows; DROP TABLE removes the table object.
• Forms help with data entry; reports provide organised output.

16. Practice Questions

These are original practice questions, not verified previous-year questions. Each question has one correct answer.

Question 1. What is a database row containing the complete details of one employee called?

A. Field
B. Record
C. Data Type
D. Index

View answer and explanation

Correct answer: B. Record

A record contains details about one entity. A field represents a particular characteristic.

Question 2. A relation has 6 attributes and 25 tuples. What is its degree?

A. 25
B. 150
C. 6
D. 31

View answer and explanation

Correct answer: C. 6

Degree is the number of attributes. Cardinality is the number of tuples.

Question 3. Which statement about a primary key is correct?

A. It uniquely identifies records and does not allow NULL.
B. Duplicate values are compulsory.
C. It always consists of exactly one column.
D. It can be created only on text data.

View answer and explanation

Correct answer: A. It uniquely identifies records and does not allow NULL.

A primary key can consist of one column or a combination of columns.

Question 4. DepartmentID in Employees references a valid department identifier in Departments. Which key does this illustrate?

A. Alternate Key
B. Candidate Key
C. Super Key
D. Foreign Key

View answer and explanation

Correct answer: D. Foreign Key

A foreign key enforces a valid relationship with referenced records.

Question 5. One department has several employees, and each employee belongs to one department. What is the department-to-employee relationship?

A. One-to-One
B. One-to-Many
C. Many-to-Many
D. No relationship

View answer and explanation

Correct answer: B. One-to-Many

One department record is associated with multiple employee records.

Question 6. Which clause displays SELECT results in descending order of marks?

A. ORDER BY Marks DESC
B. WHERE Marks ASC
C. GROUP BY Marks DESC
D. SELECT DESC Marks

View answer and explanation

Correct answer: A. ORDER BY Marks DESC

ORDER BY specifies the result order, and DESC means descending.

Question 7. Marks contains 50, NULL and 70. What does COUNT(Marks) return?

A. 3
B. 120
C. 2
D. 60

View answer and explanation

Correct answer: C. 2

COUNT(Marks) counts non-NULL values. There are two: 50 and 70.

Question 8. Which condition correctly finds records whose Marks value is unavailable?

A. WHERE Marks = 0
B. WHERE Marks = NULL
C. WHERE Marks = 'NULL'
D. WHERE Marks IS NULL

View answer and explanation

Correct answer: D. WHERE Marks IS NULL

IS NULL tests for NULL. NULL is neither zero nor the text 'NULL'.

Question 9. Which command removes records satisfying a WHERE condition without removing the table structure?

A. DROP TABLE
B. DELETE
C. CREATE TABLE
D. GRANT

View answer and explanation

Correct answer: B. DELETE

DELETE can use WHERE to remove selected rows.

Question 10. Which process helps reduce unnecessary data repetition and related update problems?

A. Normalization
B. Slide Transition
C. Mail Merge
D. Defragmentation

View answer and explanation

Correct answer: A. Normalization

Normalization organises tables and relationships to reduce unnecessary duplication.

Question 11. Which transaction property represents the “all or nothing” principle?

A. Durability
B. Indexing
C. Atomicity
D. Sorting

View answer and explanation

Correct answer: C. Atomicity

Atomicity means the related changes take effect as a complete unit or do not take effect.

Question 12. Which MS Access object is suitable for presenting a monthly employee list in an organised printable format?

A. Field
B. Primary Key
C. Relationship
D. Report

View answer and explanation

Correct answer: D. Report

A report provides organised output suitable for presentation and printing.

SELECT CandidateName, Marks
FROM Candidates
WHERE Marks >= 60
ORDER BY Marks DESC, CandidateID ASC;