Database Concepts

Written and reviewed by Rohit Singh, Computer Science & Commerce faculty at UniDrill (MCA, Osmania University · 6 years teaching). Based on NCERT Informatics Practices Class XI, Chapter 7.

Why does the attendance register keep going wrong?

A class teacher marks P or A for 50 students on each of 26 working days. That is 1,300 entries a month, written by hand. A name is misspelled in March, a page is lost in April, and a total is added wrongly in May. Meanwhile the office keeps its own list of students and guardians, so the same names live in two places and nobody notices when they stop matching.

This chapter explains how a database fixes those problems, and then teaches you the vocabulary of the relational model, which is the most common design for databases and the base for all of SQL.

Files first: what goes wrong

Moving records from paper to computer files helps. You can copy the student list to a new file when the class is promoted, search for a student, add, edit and delete records. A file is simply a container for data, and the data can be text, CSV, pictures, audio or a web page. But to read data from a file inside a program, someone has to write code for it, and trouble starts as the files multiply.

Take two files from the school: a STUDENT file kept by the office (roll number, name, date of birth, guardian name, phone, address) and an ATTENDANCE file kept by the teacher (date, roll number, name, status).

Why redundancy causes inconsistency. Redundancy is the cause and inconsistency is the effect. If a fact is stored once, it can only be wrong in one way. If it is stored twice, it can be right in one place and wrong in another. Students often learn the two as synonyms; keep them apart: redundancy is duplication, inconsistency is disagreement.

Pause and predict: two siblings, roll numbers 3 and 5, live with the same guardian, Himanshu Shah. In the single STUDENT file, how many times is his phone number stored, and what goes wrong if it changes?

Check your answer: Twice, once per sibling. If the office updates only one row, the file now holds two different phone numbers for the same person. That is redundancy leading to inconsistency.

The database management system (DBMS)

A database is a collection of logically related data organised so that it can be stored and managed efficiently. A database management system (DBMS) is the software used to create and manage databases: to store, update and retrieve data. Examples are MySQL, Oracle, PostgreSQL, SQL Server, Microsoft Access and MongoDB.

A DBMS hides how data is physically stored and gives users an abstract view of it. It sits between the data and the people (or programs) using it, so the teacher and the office staff can work on one shared, central copy at the same time. Asking the database for information is called querying, and you do it with a query language (SQL, in the next chapter).

Databases run banking (customers, accounts, loans, transactions), online shopping (items, users, orders), inventory systems, crop loan records, and employee and payroll systems in organisations.

A DBMS is not free of drawbacks. It needs costly hardware and software, trained users and regular backups. It is more complex to run. And because the data is kept centrally, a hardware or software failure can halt work for everyone.

From files to a database

To convert the school files into a database, three changes are made. First, student name is removed from ATTENDANCE, since it can be fetched from STUDENT using the roll number. Second, the guardian details are moved into a separate GUARDIAN table, so each guardian is stored once. Third, a new column GUID (guardian ID) is added, with a unique value for each guardian, and the same column is also placed in STUDENT to link the two tables.

Why a new ID rather than the guardian's name? Two guardians can share a name. Why not the phone number? Phone numbers can change, so they do not reliably identify a person.

The result is three tables: STUDENT(RollNumber, SName, SDateofBirth, GUID), GUARDIAN(GUID, GName, GPhone, GAddress) and ATTENDANCE(AttendanceDate, RollNumber, AttendanceStatus).

Why it matters. Notice that roll number 3 has no GUID yet. The database accepts this because a foreign key may hold NULL, an idea we meet below. Once the guardian is known, one UPDATE fills it in.

Key terms of a DBMS

Schema versus instance. The schema changes rarely; the instance changes all the time. Create an empty STUDENT table and you have a schema with an empty instance. Insert six rows and the same schema now has a different instance. Think of a blank school timetable form (the schema) versus the timetable filled in for this week (the instance).

Beyond the textbook. For the exam: the schema and constraints are stored as meta data in the database catalog. In reality: you can query that catalog like any other table. In MySQL, commands such as SHOW TABLES and DESC report what the catalog knows about your tables. Why it matters: "data about data" is not an abstract slogan; it is the reason a database can describe itself.

The relational model

A data model describes how data is defined and represented, how items relate and what constraints apply. The most widely used model is the relational data model, and a DBMS built on it is a relational database management system (RDBMS). Other models exist (object oriented, entity relationship, document and hierarchical), but this book uses the relational one.

In this model a table is called a relation, and relations in a database are linked to each other, not independent. ATTENDANCE is linked to STUDENT through RollNumber, and STUDENT is linked to GUARDIAN through GUID. Without those linking columns you could not retrieve valid combined information.

Worked example: degree and cardinality. Find the degree and cardinality of the three tables in the school database.

The common wrong step is to count the header row as a tuple, or to swap the two words. A memory aid: degree counts across the header (columns), cardinality counts down the rows.

For domains, RollNumber takes integer values, so its domain is the set of integers, and SName takes character strings.

Three properties that make a relation special

A relation is not just any table. It obeys three groups of rules.

NULL. NULL means a value that is unknown or does not apply. It is not zero and it is not an empty string. If a guardian has not shared a phone number, GPhone is NULL.

Beyond the textbook. For the exam: every tuple in a relation must be distinct. In reality: a table in an actual DBMS will happily store two identical rows if you never gave it a key. It is the primary key constraint that enforces the "distinct tuples" rule. Also, any calculation with NULL gives NULL, so 5 + NULL is NULL. Why it matters: this is why defining a primary key is part of designing every table.

Pause and predict: a class list for sports is stored as (Roll_no, Preference) with the rows (9, Cricket), (17, Badminton) and (17, Football). Which property of a relation is broken if each student may give only one preference?

Check your answer: Roll number 17 has two values for the preference, so the "one value per cell and unique identification" rule fails. Making Roll_no the primary key would stop the second row for roll number 17 from being accepted.

Keys: how a relation keeps rows distinct

Because tuples must be distinct, at least one attribute (or group) must hold different, non-null values in every row. These are the keys.

The relation that holds the foreign key is the referencing (foreign) relation; the relation whose primary key is referred to is the referenced (primary or master) relation. A foreign key may be NULL unless it is part of that table's primary key.

Worked example: choosing keys for ATTENDANCE. Which attribute or attributes can be the primary key of ATTENDANCE(AttendanceDate, RollNumber, AttendanceStatus)?

The wrong move is to pick RollNumber because "it identifies the student". It does, but the question is what identifies a row, and a row is one student on one day.

Why a foreign key can be NULL. A foreign key only says "if there is a value, it must exist in the other table". Roll number 3 has no guardian recorded yet, so GUID is NULL and no rule is broken. This is not allowed when the foreign key is also part of the primary key, because a primary key attribute can never be NULL. In ATTENDANCE, RollNumber is both a foreign key and part of the primary key, so it can never be NULL.

Beyond the textbook. For the exam: GUID is chosen as the primary key and GPhone becomes an alternate key. In reality: designers often avoid keys taken from the real world, such as phone numbers or government ID numbers, because they can change or may be sensitive. A neutral auto generated number is common. Why it matters: a key must stay the same for the life of the row, otherwise every table that refers to it breaks.

Exam traps

60-second revision

Where this chapter connects

Everything here is put to work in Introduction to Structured Query Language (SQL), where you create the StudentAttendance database, add the primary and foreign keys with SQL and query it. The quality of the data you store is the theme of Understanding Data. In Class XII the same ideas continue in Database Concepts and Querying and SQL Functions, where two tables are combined through their keys.