Database Concepts

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

What goes wrong when a school keeps attendance in a register?

A class of 50 students and 26 working days means 50 × 26 = 1,300 entries every month, all written by hand. A student is promoted, and every name must be copied into a new register. A page is torn, and a month of attendance is gone. Someone misspells a name in one place. The office keeps the same students' details in another file, so now two records of the same child disagree. Finding one student's guardian means turning through papers. The fix is to store the data in a computer, and the better fix is to store it in a database. This chapter shows why, and what a database looks like inside.

Files and their limits

A file is a container that stores data on a computer: text, program code, a CSV file, pictures, audio, video and web pages are all files. A file can be searched directly, but to show something on a website, such as a monthly attendance report, someone must write a program to read the file.

The school example uses two files. The office keeps a STUDENT file (RollNumber, SName, SDateofBirth, GName, GPhone, GAddress), and the class teacher keeps an ATTENDANCE file (AttendanceDate, RollNumber, SName, AttendanceStatus). Having two such files shows six limitations of the file system.

Redundancy and inconsistency are linked but not the same thing. Redundancy is the duplication; inconsistency is the damage that duplication causes when one copy is changed and the other is not. Store the name once, and there is no second copy left to disagree with it.

Database management system (DBMS)

A database is a collection of logically related data organised in tables. A database management system (DBMS) is the software used to create and manage databases, and to store, update and retrieve the data in them. Examples are MySQL, Oracle, PostgreSQL, SQL Server, Microsoft Access and MongoDB. The DBMS sits between the data and the users or programs, and hides how the data is physically stored, which gives users an abstract view. Retrieving data by a command is called querying the database.

Databases run banking (customer, account, loan and transaction data), crop loans (kisan credit card, land and repayment data), inventory (products, orders, delivery), organisation resource management (employees, salaries, departments) and online shopping (item descriptions, user logins, preferences).

How the school files become a database. Three changes remove the problems.

A phone number could have served as the link, but phone numbers change, so they may not truly identify a guardian. The tables now look like this (the full attendance table has 12 rows, of which the first six are shown).

The GUARDIAN table:

And the first rows of ATTENDANCE (one row per student per day):

A DBMS keeps one central copy of the data that many users, such as the office staff and the teacher, can use at the same time.

Why it matters. The cost of moving from files to a DBMS is real: buying better hardware and software, training people to write queries, and the recurring cost of backups and recovery. Putting all the data in one place also increases complexity (security, consistency, sharing and integrity must all be managed) and vulnerability, because a hardware or software failure can stop every user at once. Those are the stated limitations of a DBMS.

Key terms of a DBMS

When a database is first defined (the schema), it is empty. After data is loaded it has an instance, and the instance keeps changing as rows are inserted, updated or deleted. So one schema can have many instances at different times, but the instance at any one moment is a single snapshot.

Data manipulation has three operations. Insertion adds a row (a new student, Rivaan, must be added to STUDENT and GUARDIAN). Deletion removes rows (when a student leaves, his or her data and guardian details are removed from STUDENT, GUARDIAN and ATTENDANCE). Update changes values (a guardian's new mobile number goes into GPhone).

File system and DBMS compared

Pause and predict: After the split, the school stores the guardian's address only in GUARDIAN. Atharv's guardian moves house. How many places must be updated, and what does this tell you about redundancy?

Check your answer: One place, the GUARDIAN row for GUID 444444444444. Every student record linked to this GUID sees the new address automatically. Because the address is stored once, there is no second copy that can go out of date, which is how a DBMS removes redundancy and inconsistency.

The relational data model

Databases are classified by the data model they use. A data model describes how data is defined and represented, how items relate to one another, and what constraints apply. The most common is the relational data model; others are object oriented, entity relationship, document and hierarchical models. A database built on the relational model is a relational database, and its management software is an RDBMS.

In the relational model a table is called a relation. The terms of the relational model are these.

Worked example: degree and cardinality. Use the three tables above (ATTENDANCE has 12 rows: two days of attendance for six students).

The trap is to swap them. Remember that degree is the width (columns) and cardinality is the count (rows). If a new student joins, cardinality goes up by one while degree stays the same. If a column is added, degree goes up while cardinality stays the same.

Pause and predict: A relation has 7 tuples and 5 attributes. After deleting 2 tuples and adding 1 attribute, what are its degree and cardinality?

Check your answer: Degree 6 and cardinality 5. Deleting tuples changes the row count (7 minus 2 is 5), and adding an attribute changes the column count (5 plus 1 is 6).

Beyond the textbook. For the exam: a table is a relation and a row is a tuple. In reality: in "relational", the word "relation" does not mean the relationship between tables. It is the mathematical name for a table itself. The links between tables are made by keys. Also, not every DBMS is relational. MongoDB, which appears among the examples in this chapter, uses the document model instead of tables.

Three properties of a relation

A relation is different from an ordinary table or data file because it obeys three properties.

NULL is a special value for a value that is unknown or not applicable. If a guardian has not given a phone number, GPhone is NULL. NULL is not zero and not an empty string.

Worked example: are these two relations the same? Two class sections each list their sports preferences.

Section 1 (arranged on Roll_no):

Section 2 (arranged on sports name, with the columns in the other order):

Both contain exactly the same five tuples: (9, Cricket), (13, Football), (17, Badminton), (21, Hockey), (24, Cricket). The order of tuples and the order of attributes are immaterial, so the two relations have the same state.

Keys in a relational database

No two tuples may be identical, so there must be at least one attribute whose values are distinct and not NULL, which can pick out a single tuple. Keys are the constraints that make this work and that link relations to one another.

Why ATTENDANCE needs a composite key. RollNumber alone repeats, because the same student appears on every date. AttendanceDate alone repeats, because the same date appears for every student. But a student is marked only once on a given day, so the pair {RollNumber, AttendanceDate} is always unique and together they form the primary key.

Foreign keys connect, and they may be NULL. The relation that holds the foreign key is the referencing (foreign) relation; the one whose primary key it points to is the referenced (primary or master) relation. In the school database there are two foreign keys: GUID in STUDENT refers to GUID in GUARDIAN, and RollNumber in ATTENDANCE refers to RollNumber in STUDENT. In some cases a foreign key can be NULL if it is not part of the primary key of its own table. That is why the row for Taleem Shah can exist with GUID NULL while the guardian is not yet recorded.

Worked example: which inserts will the database accept? Assume the keys above are enforced: the primary key of ATTENDANCE is {AttendanceDate, RollNumber}, RollNumber is a foreign key to STUDENT, and the existing STUDENT rows are roll numbers 1 to 6.

Beyond the textbook. For the exam: GUID and GPhone are both candidate keys of GUARDIAN, and GUID is chosen as the primary key. In reality: GPhone is a weak choice for a key, because phone numbers change and some guardians have none, as the NULL for Danny Dsouza shows. A primary key must always have a value, which is why an artificial ID such as GUID is preferred.

Exam traps

60-second revision

Where this chapter connects

• Previous: Understanding Data introduces data storage and the limits of file processing that this chapter solves.
• Next: Structured Query Language (SQL) uses the same StudentAttendance database to create tables, add the keys and write queries.
• Class XI link: Database Concepts (Class XI) is the first look at tables and records.