Database concepts
Key takeaways
- A database is an organised, persistent collection of related data, and a DBMS is the software that creates, manages and controls access to it, for example MySQL, PostgreSQL or Oracle Database.
- Relation, tuple and attribute are the formal names for table, row and column; degree counts a relation's attributes and cardinality counts the tuples it currently holds.
- The database approach controls and shares data centrally, minimises redundancy and lets the DBMS enforce consistency and integrity rules on every access.
- Databases may be centralized, distributed, relational, cloud, hierarchical (each record has one parent) or network (a record can have several parents).
- The DBMS environment has four components: hardware, software, data and procedures.
A database organises data so it can be stored once, retrieved reliably and kept consistent, in contrast to separate flat files, which are best for a smaller, less structured set of data. This chapter covers the building blocks, the formal relational vocabulary, and what a database management system actually does for an organisation.
Definitions
Core relational terminology
Used throughout the rest of this paper, in formal and everyday terms.
Reading degree and cardinality from a real table
A Students table has columns StudentID, Name, Course and Year, and currently holds 40 rows.
Degree = the number of attributes = 4 (StudentID, Name, Course, Year).
Cardinality = the number of tuples = 40.
If five new students enrol, cardinality rises to 45; degree stays at 4 unless a new column is added, since degree describes the table's structure and cardinality describes its current content.
Characteristics and purpose of the database approach
Characteristics of the database approach: data is centrally controlled and shared rather than duplicated across separate files, redundancy is minimised, and a DBMS enforces consistency and integrity rules on every access rather than leaving that discipline to each application separately.
The purpose of a database is to store data persistently, make it retrievable efficiently through queries, and let many users and applications share it safely and consistently at once.
Types of databases
Advantages of the database approach
Advantages of using a database over separate flat files: reduced data redundancy, improved consistency, controlled shared access, stronger security, and support for complex queries across related data.
Functions of a DBMS
Functions of a DBMS: defining the database structure, storing and retrieving data, enforcing integrity constraints, controlling concurrent access, providing security, and supporting backup and recovery.
Components of the DBMS environment
Self-check
(a) Define data, a database and a DBMS. (3 marks)
(b) A Products table has columns ProductID, Name, Price and Category, and holds 250 rows. State its degree and cardinality. (2 marks)
(c) Distinguish a centralized database from a distributed database. (2 marks)
(d) State four advantages of the database approach over separate flat files. (4 marks)
(e) State four functions of a DBMS. (4 marks)
(f) Name the four components of the DBMS environment. (4 marks)
Show worked answer
(a) Data is raw, unprocessed facts with no context. A database is an organised, persistent collection of related data. A DBMS is software that creates, manages and controls access to a database.
(b) Degree = 4 (ProductID, Name, Price, Category). Cardinality = 250.
(c) A centralized database stores and manages all data at a single physical location. A distributed database spreads data across multiple physical locations while appearing as one logical database.
(d) Reduced data redundancy; improved consistency; controlled shared access; stronger security (also support for complex queries). Any four.
(e) Defining the database structure; storing and retrieving data; enforcing integrity constraints; controlling concurrent access; providing security; supporting backup and recovery. Any four.
(f) Hardware, software, data, procedures.
Exam tip
Relation/tuple/attribute (the formal terms) and table/row/column (the everyday terms) refer to exactly the same things; be comfortable with both vocabularies, since exam questions use them interchangeably, and practise reading degree (columns) and cardinality (rows) from a described table.