Chapter 6 — Database and DBMS – An Introduction

What we mean by data

Data are bits of information: names, numbers, dates and short descriptions. Every time you note a student's name, a book title, or a price, you are creating data. Alone a single fact is small, but when many facts are collected they form a set that can be used to answer questions and make decisions.

Why organisation is needed

Organising data helps us find what we want quickly and avoid mistakes. Imagine hundreds of student names written on many loose sheets; finding one name would be slow and error-prone. If the same information is kept in a single organised table, searching, sorting and updating become fast and reliable. Organisation also helps prevent duplicate or contradictory information—for example, one correct address stored in one place rather than many different copies that might disagree.

Everyday benefits

Well-organised data saves time for teachers and librarians and makes school work smoother. It helps make lists (who is in class 7A?), performs simple counting (how many books are on loan?), and prepares neat printed reports. Organisation is useful in many subjects: maths uses tables, science uses lists of observations, and languages use vocabularies—each benefits from clear arrangement.

How we organise data

We place related facts together in a table where each column holds one type of information and each row holds all data about one item. Later topics explain how fields, records and tables work together and how software called a DBMS helps store and manage organised data safely when many people use it.

What is a file?

A file is a collection of data stored as one document on a computer, like a text file or a spreadsheet. Files are simple to create and are useful for small tasks. For example, a teacher might keep marks for Mathematics in one file and marks for Science in another. Each file stands alone and does not know about other files.

Limitations of file-based storage

As information grows, files become difficult to manage. If two people try to change the same file at the same time, one person's changes may be lost. Files also cause repeated information: a student’s name and address might be copied into many files, so if the address changes it must be updated in every file. This leads to inconsistency and extra work.

What a database does differently

A database stores related data together in structures like tables and uses a DBMS to manage access. A DBMS allows many users to read or change data at once without conflicts. It prevents duplicate storage by keeping each fact in one place and referencing it where needed. Databases also make searching and sorting much faster than scanning many files because they are organised for quick lookup.

When to use each

Files can be fine for personal notes or very small tasks. A database is better when information must be shared, updated often, or when you need reliable searches and reports. For a school office, library or any system with many records, a database reduces mistakes and saves time.

Real-life view

Think of files as many paper folders scattered on a desk, each containing some information. A database is like a filing cabinet where each drawer is labelled and documents are organised so anyone can find and update the right paper without making extra copies.

Definition and role

A DBMS—Database Management System—is software that helps create, manage and control access to a database. It provides the tools to store data in organised structures, search quickly, update safely, and protect information from loss or unauthorised use. A DBMS hides the technical complexities of how data are stored so users can work with simple tables and forms.

Key functions explained

• Storage: The DBMS keeps data in a way that is efficient and safe.
• Retrieval: It finds and returns requested information quickly, even from large datasets.
• Update: It allows changes while preventing conflicts when many users work together.
• Security: It controls who can view or change data using passwords and permissions.
• Backup and recovery: It supports copying and restoring data when something goes wrong.

How DBMS helps in schools

In a school, a DBMS can manage student records, library loans, attendance and exam marks. Teachers and clerks use the DBMS to enter or read data via simple screens. The DBMS makes sure that if two teachers update different parts of the record, their changes do not conflict. It also makes reports like class lists or library overdue lists easy to produce.

User view vs internal view

From the user’s view, a DBMS offers menus, forms and simple search boxes. Inside, the DBMS manages files, indexes and memory to make everything fast and reliable. Students at this level should learn what a DBMS does and why it is better than manual record-keeping; practical commands come in later classes.

Understanding table structure

A table is the main structure used to store related data in a database. It looks like a sheet with vertical columns and horizontal rows. Each column is a field and each row is a record. The table itself has a name that tells what type of data it stores: for example, 'Students' or 'Books'.

Fields (columns)

Fields are the named categories of information that apply to every record in the table. Examples of field names are Name, RollNo, DateOfBirth, and Class. Every record will have one value for each field. Choosing clear and meaningful field names helps users understand the data quickly.

Records (rows)

A record is one complete set of field values that describe an item. In a Students table a record holds the roll number, the student’s name, date of birth and class for one student. The record is a single row where each cell contains the value for the corresponding field.

Table name

The table name describes what the table contains and is used when searching or linking tables. For example, the Students table holds only student-related records. Keeping related data together avoids mixing different kinds of information in one table.

Why this arrangement is useful

With fields and records it becomes easy to search for specific values across many records. If we want to find all students in Class 7A, we look at the Class field in every record. This targeted search is much faster than scanning entire documents. The field/record/table structure also supports keys and links between tables, which prevent duplication and keep data accurate.

Practical tip

When designing tables, list the fields first and add sample records to test if your design captures all needed information. This practice helps find missing fields or fields that should be stored in separate tables.

What are data types?

A data type tells the database what kind of value a field will hold. Choosing the correct type helps the database store values efficiently and perform the correct operations. Simple common types are Text (for names and labels), Number (for whole numbers), Decimal (for values with fractions), and Date (for dates).

Common types and reasons

• Text (also called Char or Varchar): Use this for names, addresses or descriptions where the value is a label, not used in maths.
• Integer/Number: Use for whole numbers such as roll numbers, counts and integer marks.
• Decimal/Real: Use for prices, weights and measurements that need fractions.
• Date: Use specifically for dates to allow correct sorting and date calculations.

Benefits of correct types

If you store dates as text the database may not be able to correctly sort by date or calculate age. If you store numbers as text you cannot add or average them. Correct types also help save space: a field set as a small number type uses less storage than a long text field.

How to choose

Ask what you will do with the field. If you need to calculate or sort numerically, use Number. If the field is for reading or searching by words, use Text. If it records when something happened, use Date. Also think about the range: marks between 0–100 fit an integer, while prices might need two decimal places.

Practical advice for students

When planning a small database, list fields and write beside each the type you think fits. Test with sample data to see if operations like totals, averages or date filters work correctly. Adjust the type if you find problems during testing.

What is a primary key?

A primary key is a field, or a combination of fields, chosen so that every record in a table can be uniquely identified. It must always have a value and cannot be empty. Because each record has a unique primary key value, the database can find and update records quickly and without confusion.

Why uniqueness matters

If a table has no primary key, two or more records may look identical and it would be unclear which one should be changed or deleted. For example, many students can share the same name; using a RollNo as primary key makes each student uniquely recognisable even when names repeat.

Good choices for primary keys

• Use a number such as RollNo or StudentID that does not change over time.
• Avoid fields that may change, like address or phone number.
• If there is no single unique field, combine fields to make a composite primary key (for example, CourseCode + StudentID for course registrations).

How primary keys help relations

Primary keys are used by other tables to refer to a specific record. For example, BorrowedBooks can store BookID and StudentID to show which student borrowed which book. The BookID in BorrowedBooks matches the primary key BookID in Books, linking the two tables reliably.

Student practice

When designing tables, always choose a clear primary key. Test with sample data and try to insert two records with the same primary key value; the DBMS should reject the second insert. This simple check helps students see how uniqueness keeps data correct.

What is a foreign key?

A foreign key is a field in one table that stores the primary key value of a record in another table. It creates a link between the two tables so data can be connected without repeating the full details in each table. This keeps the database tidy and consistent.

Simple explanation with example

Imagine a Students table where RollNo is the primary key. A BorrowedBooks table records which student borrowed which book. Instead of copying the student's name into BorrowedBooks, we add a RollNo field to BorrowedBooks. That RollNo is a foreign key pointing to Students.RollNo. If the student's name changes, we only update it once in Students and all linked records remain correct.

Types of relationships

• One-to-many: One student can borrow many books. The student's RollNo appears in many BorrowedBooks records as the foreign key.
• One-to-one: Rare in simple school systems, but for example a Student and StudentIDCard where each student has exactly one card.
• Many-to-many: When many students join many clubs, a linking table Membership(StudentID, ClubID) holds pairs of keys to represent the relationship.

Why foreign keys are useful

Foreign keys prevent repeating information, make updates easy and allow queries that combine related data. Using them correctly supports clear reports like 'show student names with the titles of books they have borrowed' by joining tables on matching key values.

Class practice

Create two small tables and draw an arrow from the foreign key in one table to the primary key in the other. Practise writing sentences that describe the link, for example: 'BorrowedBooks.RollNo references Students.RollNo'.

Understanding CRUD

CRUD stands for Create, Read, Update and Delete—four basic operations you do with records in a database. These operations let you add new information, view existing information, make corrections, and remove data that is no longer needed. Together they form the core of working with any database.

Create (Add)

Creating means inserting a new record into a table. For example, when a new student joins school, we add a record with RollNo, Name, Class and DOB. Good practice is to make sure the primary key is unique when you create a record.

Read (View or Retrieve)

Reading means asking the database to show records that match a question. For example, list all students in Class 7A or show the title of a book by BookID. Reading often uses queries or filters to return just the required fields and rows.

Update (Modify)

Updating changes values in an existing record. If a student’s address or class changes, we update that record. Use the primary key to ensure you update the correct record and consider keeping a log of important changes for safety.

Delete (Remove)

Deleting removes a record when it is no longer needed, such as when a book is discarded. Always check before deleting because recovery may require a backup. Some systems keep a ‘soft delete’ flag instead of permanently removing records.

Safety and concurrency

A DBMS helps when many users perform CRUD actions at the same time by managing locks and transactions so edits do not conflict. Students should practise these operations on small sample tables and always keep backups before performing many deletions or updates.

What is a query?

A query is a question you ask the database to find or show certain records. It tells the database which table to look in, which fields to return, and which conditions the records must meet. Queries help answer useful questions such as 'Which students are in 7A?' or 'Which books are overdue?'.

Parts of a clear query

Think about a query in three parts: the table to search, the fields you want to see, and the condition that a record must meet. For example: Table = Students; Fields = Name, RollNo; Condition = Class = '7A'. Breaking queries into these parts makes them easier to plan and test.

Types of simple queries

• Select all records: return every row from a table, useful for full lists.
• Select specific fields: return only Name and RollNo if you do not need other details.
• Select with condition: return rows that satisfy a rule, such as Marks > 75 or DOB > 01-01-2010.

Thinking about results

Consider whether the result should be sorted (for example by Name or RollNo) and whether duplicates must be removed. Planning the desired output helps choose the right fields and conditions. Practice forming queries in plain language before using any software—write the question as a sentence and then mark the parts that name the table, fields and conditions.

Classroom practice

Use sample tables to practise queries: list students in a class, find books by an author, or show students with marks above a threshold. Writing both the plain-language question and the expected result helps confirm that the query is correct.

Begin with the real-world problem

Design starts by asking what the database must do. For a school library, list the facts you need: book titles, authors, book IDs, student names, roll numbers and which student borrowed which book. Group facts about the same kind of thing into a table.

Steps to design

• Identify entities: things to record, such as Students, Books, Clubs.
• For each entity, list needed fields: Students need RollNo, Name, Class; Books need BookID, Title, Author.
• Choose primary keys that uniquely identify records (RollNo, BookID).
• Find relationships and add foreign keys: Borrowed(BookID, RollNo, IssueDate) links Books and Students.

Avoiding duplication

Do not repeat full student details in Borrowed; store only the RollNo and use it to find student details when needed. This keeps data consistent and reduces work when updating information. For many-to-many relationships, like students and clubs, create a linking table Membership(StudentID, ClubID) that stores pairs of keys.

Practical checking

Create sample records and try to answer common questions: Who borrowed book B101? Which students are in 7A? If these questions are easy to answer, the design is likely good. Also check if any field should be moved to another table because it is repeated too often.

Documentation and testing

Write short notes explaining each table and its fields. Testing with a few records helps find mistakes early. A clear plan makes it easier for others to use or extend your database later.

Relational model in simple words

The relational approach organises data into separate tables that are connected using keys. Each table holds one kind of thing, and relations describe how items in different tables relate. This setup reflects real-world connections and keeps information tidy.

One-to-many

One-to-many is the most common relationship in school databases. For example, one Class can have many Students. The Class table has ClassID as primary key and Students table stores ClassID as a foreign key. This lets us list all students for a class without repeating class details in every student record.

One-to-one

A one-to-one relation exists where one record in a table relates to exactly one record in another table. For example, a Student and StudentIDCard might be one-to-one when each student has exactly one card. Often such data can remain in the same table unless there is a special reason to separate them.

Many-to-many and linking tables

Many-to-many occurs when many records in one table relate to many in another. For example, many students can join many clubs. We handle this by creating a linking table, such as Membership(StudentID, ClubID), where each row records one student’s membership in one club. The linking table holds two foreign keys that together form a composite key.

Why relations help

Relations prevent repeated data and make updates simpler: change a student’s name once in Students and every related table that shows the student will reflect the change when joined. Practise drawing relationships using arrows and test simple joins with sample data to see how related information is combined.

Why protect data?

Data loss or unauthorised changes can cause serious problems. Backups let us restore data after accidental deletions, hardware failure or errors. Access control ensures that only authorised people can view or change sensitive information. Both are simple but essential safety steps for any database.

Backups explained

A backup is a copy of your database stored separately. Backups should be taken regularly, for example weekly or monthly depending on how often data change. Keep at least one copy off the main computer, such as on a USB drive or cloud storage. Test the backup by restoring a small part of the data to be sure the copy works.

Access control and permissions

Access control uses user accounts and passwords to limit who can do what. For example, teachers may have rights to add or change student marks, while students may only have view access. Limit administrative rights to a few responsible people and change passwords if someone leaves.

Other safety steps

Keep one read-only copy of important records for audit purposes. Use simple logs to record who changed major information and when. When deleting records, consider a soft-delete flag so data can be recovered if needed rather than permanent deletion immediately.

Student guidelines

Even for classroom projects, save copies of your work, use passwords if sharing files, and avoid giving write access to everyone. These good habits protect your work and teach responsibility for real-world data handling.

Clear naming rules

Good names for tables and fields make a database easy to read and share. Use short, meaningful names such as Students, Books, RollNo, DateOfBirth. Avoid spaces in names; use underscores or camel case if needed (Date_of_Birth or DateOfBirth). Be consistent with abbreviations and capitalization so other users understand your design quickly.

Why naming matters

When names are clear, queries and reports are easier to write and maintain. Ambiguous names cause errors: a field called 'Value' could mean price, marks, or point score. A descriptive name removes doubt and helps anyone who reads your database.

Forms for data entry

Forms are simple screens or paper layouts used to add or edit records. A form shows one record at a time with labelled boxes for each field, making it easy to enter data correctly. For example, a student entry form would show boxes for RollNo, Name, Class and DOB in a clear order. Forms can hide technical fields like primary keys so users focus on readable information.

Reports for presentation

Reports are organised lists or summaries produced from the database. They might be a class list, an overdue book list or a sales summary. Reports should show only the needed fields, with clear headers and useful sorting, so readers can understand the information quickly.

Practical habits

Document your table names and field meanings in a short note. Test forms and reports with sample data to make sure they display correct information. Use consistent naming, tidy forms, and clear reports to make databases useful for everyone.