Chapter 2: Basic Concepts and Terminology of Databases – Computer Science 2nd Year Notes

This chapter covers the Basic Concepts and Terminology of Databases from the 2nd Year (ICS Part-II) Computer Science syllabus of the Punjab Curriculum and Textbook Board (PTB/PCTB). It explains fields, records, tables, attributes, views, indexes, keys (primary and foreign) and the people who work with databases. These notes are prepared by freebooks.pk.

To work with databases you must know their basic vocabulary. This chapter defines the key terms that describe how data is organised in a database.

Learning Objectives

• Define field, record and table.
• Explain attributes, tuples and entities.
• Describe views and indexes.
• Define the different types of keys.
• Distinguish a primary key from a foreign key.
• Describe the users and administrators of a database.

Key Concepts

Field, Record and Table

In a database, data is stored in tables. A field (or column) is a single item of data of a particular kind, such as a name or a roll number, and it is the smallest named unit of data. A record (or row) is a complete set of related fields about one item, such as all the details of one student. A table is a collection of records of the same kind arranged in rows and columns; for example a STUDENT table holds one record for each student.

Attributes, Tuples and Entities

The same ideas are described using more formal terms in database theory. An entity is a real-world thing about which data is stored, such as a student or a book. An attribute is a property or characteristic of the entity, such as a student’s name or age; attributes correspond to the fields (columns) of a table. A tuple is a single row of a table, that is, one record. So a table represents an entity, its columns are the attributes, and its rows are the tuples.

Views and Indexes

A view is a way of looking at the data in a database that shows only selected rows and columns, rather than the whole table; different users can be given different views so that they see only the data they need. An index is a structure that the database keeps to make searching faster; like the index of a book, it lets the DBMS find the required records quickly without scanning the whole table, which speeds up queries.

Keys

A key is a field (or combination of fields) used to identify records. A primary key is a field whose value is unique for every record in a table, so it identifies each record exactly; for example a roll number in a STUDENT table. A candidate key is any field that could serve as the primary key, and a composite key is a primary key made of more than one field. A foreign key is a field in one table that refers to the primary key of another table, and it is used to link the two tables together.

Primary Key and Foreign Key

The primary key and the foreign key are the most important keys. The primary key uniquely identifies each record within its own table and cannot be duplicated or left empty. The foreign key is a field placed in a second table that holds the value of the primary key of the first table, thereby creating a relationship between the two tables; for example, a RESULT table can include the RollNo (a foreign key) to link each result to a student in the STUDENT table. Keys are what make it possible to relate data across tables.

Database Users

Several kinds of people work with a database. End users are the people who use the database to enter, update and retrieve data for their daily work, such as clerks and managers. Application programmers write the programs that use the database. Above these are two special roles: the data administrator, who plans and controls the organisation’s overall data policy, and the database administrator (DBA), a technical expert who actually creates, maintains, secures and tunes the database and controls who may use it.

The Database Administrator (DBA)

The database administrator (DBA) is the person responsible for the whole database system. The DBA’s duties include designing and creating the database, defining its structure, controlling access by granting rights to users (security), keeping the data accurate (integrity), making regular backups and recovering the database after failure, and improving its performance. Because the DBA has full control over the database, this is a highly responsible and technical role.

Important Definitions

Key Facts & Rules

Item	Detail
Field	Column; smallest unit of data
Record	Row; all fields about one item
Table	Rows (records) x columns (fields)
Entity / attribute / tuple	Table / column / row
Primary key	Unique, identifies each record
Foreign key	Links to primary key of another table
DBA	Creates, secures and maintains the database

Diagrams & Illustrations

Table, record and field: a database table with a record (row) and a field (column) highlighted.

Primary and foreign key: two tables (STUDENT and RESULT) linked by a primary key and a foreign key.

Database users: the end users, data administrator and database administrator (DBA) who work with a database.

Solved Examples & Practice

Identify field and record

In a STUDENT table, ‘Name’ is a field and all the details of one student make up a record.

Choose a primary key

In a STUDENT table, RollNo is a good primary key because it is unique for each student.

Use a foreign key

Putting RollNo into a RESULT table lets each result be linked to a student; there RollNo is a foreign key.

Faster searching

Adding an index on the Name field lets the DBMS find students by name more quickly.

Short Questions & Answers

Long Questions & Answers

Q1: Explain the terms field, record, table, attribute, tuple and entity with an example.

Databases have their own vocabulary for describing how data is arranged, and understanding these terms is essential. Data in a database is held in tables. The smallest named unit of data is a field, also called a column, which holds a single item of a particular kind, such as a name, a roll number or a mark. A record, also called a row, is a complete set of related fields describing one particular item; for example, in a table of students, one record contains all the fields, roll number, name, marks and so on, belonging to a single student. A table is then a collection of many records of the same kind, arranged neatly in rows and columns, so that a STUDENT table has one row for each student and one column for each piece of information kept about them. The same ideas are expressed by more formal terms in database theory. An entity is a real-world object or thing about which data is stored, such as a student, a book or an employee; a table usually represents one entity. An attribute is a property or characteristic of that entity, such as the student’s name or age, and the attributes correspond to the columns (fields) of the table. A tuple is a single row of the table, that is, one record. So, taking a STUDENT table as an example, the entity is the student, the attributes are the columns such as RollNo, Name and Marks, and each tuple (row) is the record of one student. These terms allow us to describe the structure of any database precisely.

Q2: Explain the different types of keys used in a database, especially the primary key and the foreign key.

A key is a field, or a combination of fields, that is used to identify records in a table and to relate tables to one another, and several kinds of key are used in databases. The most important is the primary key, which is a field whose value is different, or unique, for every record in the table, so that it identifies each record exactly and no two records can share the same value; a primary key value must always be present and cannot be left empty. For example, in a STUDENT table the roll number can serve as the primary key because each student has a different roll number. A candidate key is any field that has the property of being unique and could therefore be chosen as the primary key; from among the candidate keys, one is selected to be the primary key. A composite key is a primary key that is made up of two or more fields together, used when no single field is unique on its own. The other very important key is the foreign key, which is a field placed in one table that holds the value of the primary key of another table, and its purpose is to create a link, or relationship, between the two tables. For instance, a RESULT table might contain the field RollNo; this RollNo is the primary key in the STUDENT table but appears in the RESULT table as a foreign key, and it connects each result to the correct student. In this way, primary keys uniquely identify records within a table, while foreign keys join related tables together, which is the basis of the relational database.

Q3: Describe the different people who work with a database, including the database administrator (DBA).

A database is used and looked after by several different kinds of people, each with their own role. At the most basic level are the end users, the ordinary people who use the database in their daily work to enter new data, update existing data and retrieve information; examples include the clerks who record transactions and the managers who look at reports. Then there are the application programmers, who write the computer programs through which many end users work with the database. Above these ordinary users are two special and responsible roles concerned with managing the data itself. The first is the data administrator, who is chiefly concerned with policy: this person decides what data the organisation should keep, who should be allowed to use it and how it should be organised, planning and controlling the organisation’s overall data resource. The second, and the most technical, is the database administrator, usually shortened to DBA, who is responsible for the database system itself. The DBA designs and creates the database and defines its structure; controls access to it by granting the appropriate rights to different users, so keeping the data secure; maintains the accuracy, or integrity, of the data; makes regular backup copies and recovers the database if it is damaged or lost; and monitors and improves its performance. Because the DBA has complete control over the database and its security, it is a highly skilled and trusted position. Together, these people ensure that the database is well designed, correctly used, kept safe and made available to everyone who needs it.

MCQs with Answers

Quick Revision Summary

• Field = column; record = row; table = rows x columns.
• Entity = table (a thing); attribute = column; tuple = row.
• View = selected data shown to a user; index = speeds up searching.
• Primary key = unique, identifies each record; candidate/composite keys.
• Foreign key = links to another table’s primary key (relates tables).
• Users: end users, programmers, data administrator, DBA (controls the database). Notes by freebooks.pk.

Exam Tips

• Define field, record and table clearly.
• Match entity/attribute/tuple to table/column/row.
• Explain the primary key (unique, identifies records).
• Explain the foreign key (links tables).
• State what views and indexes are for.
• List the DBA’s main duties.