Database Concepts: One Mark Questions, Book Back Answers and Notes

About Database Concepts

Medium ~90 min study

Data is a raw fact, such as the number 87 or the word Chennai. Information is what you get after data is processed and organised, such as "Anu scored 87 in Computer Science". A Database Management System (DBMS) is the software that stores such data, lets you retrieve it, and controls who can change it. This unit builds on that one idea.

From there the chapter moves to tables. In the relational model a table is called a relation, a row is a tuple and a column is an attribute. You will meet five database models, see how entities are linked by one-to-one, one-to-many, many-to-one and many-to-many relationships, and learn why an RDBMS reduces redundancy better than a plain file-based DBMS.

Questions from this unit are usually short and definition-based: the components of a DBMS, DBMS versus RDBMS, the hierarchical and network models, and the symbols of relational algebra. Learn the symbol and the meaning of each operation together, because MCQs often give one and ask for the other.

What you'll learn

• Tell data apart from information with an example of each.
• List the five components of a DBMS and state the main differences between a DBMS and an RDBMS.
• Describe the hierarchical, network, relational, ER and object models.
• Identify the type of relationship between two entities from a given situation.
• Write and read select and project operations using \(\sigma\) and \(\Pi\).
• Work out the result of union, intersection, set difference and cartesian product on small relations.

Before you start

• Basic idea of files and how a computer stores data.
• Set theory from Mathematics: union, intersection and difference.
• Familiarity with lists and tables of data from earlier Python chapters.

Database Concepts explained

Database Concepts: What You Need to Know

Data, Information and DBMS

Data is raw and unprocessed. Information is data that has been organised so it means something. A DBMS is software that lets users define, create, maintain and control access to a database. Compared with keeping records in separate files, a DBMS keeps data consistent, applies security, allows many users to work at the same time and reduces duplicate storage of the same data.

RDBMS and the Components of a DBMS

An RDBMS stores all data in tables of rows and columns. It uses normalization to split large tables into smaller related ones, which reduces redundancy and avoids update anomalies. A DBMS has five components: hardware, software, data, procedures (methods) and database access languages such as SQL. Students often forget procedures when listing them, so learn all five.

Relational Terms and Data Models

In a relational database, a table is a relation, a row is a tuple and a column is an attribute. A data model describes how data is represented and linked. The textbook covers the hierarchical model (a tree where each child has only one parent), the network model (a child can have more than one parent), the relational model, the entity-relationship (ER) model and the object model. The ER model is drawn as a diagram and is used to plan a database before it is built.

Types of Relationships

Relationships between entities are classified by how many records on one side match records on the other: one-to-one (one student, one exam number), one-to-many (one department, many staff), many-to-one (many staff, one department) and many-to-many (many students, many courses). Keys are used to link related tables so that each record can be matched correctly.

Relational Algebra

Relational algebra was introduced by E. F. Codd. It is a procedural query language: each operation takes one or two relations and gives a new relation as the result. The unary operations are select (\(\sigma\)), which picks rows that satisfy a condition, and project (\(\Pi\)), which picks columns and removes duplicate rows from the result. The set operations are union (\(\cup\)), intersection (\(\cap\)), set difference (\(-\)) and cartesian product (\(X\)). Union, intersection and difference need both relations to have the same number of attributes with matching domains.

Common mistakes to avoid

• Mixing up DBMS and RDBMS. A DBMS uses the navigational model (records linked to each other) and does not perform normalization; an RDBMS stores data in related tables and does.
• Swapping the relational terms. Relation means table, tuple means row, attribute means column. A tuple is never a column.
• Thinking project removes duplicate columns. Project keeps only the columns you name, and any duplicate rows in that result are removed.
• Applying union, intersection or difference to relations with different structures. Both relations must have the same number of attributes with matching domains.
• Getting the size of a cartesian product wrong. The number of rows is multiplied (3 rows and 2 rows give 6 rows), while the number of columns is added.

Test yourself on these with the practice test, then check the worked reasoning in the solved MCQs.

Frequently asked questions

What is the difference between data and information?

Data is a collection of raw facts, such as 87, 92 or Kavin, with no context. Information is data that has been processed and arranged so it is meaningful, for example a mark sheet showing that Kavin scored 92 in Physics.

Why is normalization used in an RDBMS?

Normalization splits data into smaller related tables so the same fact is not stored in many places. This reduces redundancy and avoids anomalies when records are inserted, updated or deleted.

What are relations, tuples and attributes?

A relation is a table. A tuple is one row of that table, which holds a single record. An attribute is a column, which holds one kind of value, such as Name or Age, for every record.

How does the hierarchical model differ from the network model?

In the hierarchical model data is arranged like a tree and each child record has only one parent. In the network model a child can have more than one parent, so many-to-many relationships can be represented.

What do the select and project operations do?

Both are unary operations. Select (\(\sigma\)) takes the rows that satisfy a condition, so it works horizontally. Project (\(\Pi\)) takes only the named columns, so it works vertically, and duplicate rows in the result are removed.

What does a cartesian product give?

It pairs every row of the first relation with every row of the second. If the first table has 3 rows and the second has 2, the result has 3 x 2 = 6 rows, and its columns are all the columns of both tables.