- Foundation
- About 12 min
- 5 practice questions
Tables, records, fields and keys
Read a table in terms of rows and columns and identify primary and foreign keys.
If something looks wrong, please tell us.
Most databases store data in tables, much like a register or a spreadsheet. A few special words describe their parts.
Parts of a table
| Roll | Name | City | Marks |
|---|---|---|---|
| 1 | Asha | Pune | 72 |
| 2 | Ravi | Jaipur | 55 |
| 3 | Meena | Patna | 90 |
| 4 | Imran | Pune | 63 |
- A table (relation) holds data about one kind of thing.
- A field (attribute or column) is one kind of detail, such as Name.
- A record (row or tuple) is all the details of one item, such as one student.
- A data type says what a field can hold: text, number, date, yes/no.
Keys
- Primary key: a field whose value is unique for every record and never empty, such as Roll. It identifies one record.
- Candidate key: any field that could serve as the primary key.
- Foreign key: a field in one table that refers to the primary key of another table, linking them.
- Composite key: a key made of two or more fields together.
Name would be a poor primary key because two students can share a name; City is poor because many students live in the same city.
Worked examples
Example 1. In the table above, how many records and fields are there? 4 records and 4 fields.
Example 2. Which field best works as the primary key? Roll, since each value is unique.
Example 3. A Marks table has columns Roll and Subject_Mark. How does Roll in this table relate to the Students table? It is a foreign key pointing to Roll in Students.
Row = record, column = field. Primary key = unique identifier; foreign key = link to another table.
Key takeaways
- Tables have fields (columns) and records (rows).
- Primary keys are unique; foreign keys connect tables.
Check your understanding
-
1. In a database table, a single column such as Name is called a
- Record
- Field
- Tuple
- Relation
Show answer
Answer: B. A column (attribute) is a field. A row is a record (tuple) and a table is a relation.
-
2. A table has 6 records and 5 fields. How many individual data values does it store?
- 11
- 25
- 30
- 36
Show answer
Answer: C. Each record has one value per field: 6 x 5 = 30.
-
3. In a Students table with the fields Roll, Name, City and Marks, which field is the best primary key?
- Name
- City
- Marks
- Roll
Show answer
Answer: D. Roll is unique for every student. Names, cities and marks can repeat.
-
4. A Marks table stores Roll, which refers to Roll in the Students table. In the Marks table, Roll is a
- Foreign key
- Composite key
- Data type
- Record
Show answer
Answer: A. A field that refers to the primary key of another table is a foreign key; it links the two tables.
-
5. A key made of two or more fields taken together is a
- Foreign key
- Composite key
- Data type
- Single field
Show answer
Answer: B. A composite key combines two or more fields, for example Roll and Subject together.
Sign in to keep track of the lessons you finish.