Databases and DBMS: Models, Keys, SQL and Normalisation

A database is an organised collection of related data that a computer can store, search and update without losing its structure. A Database Management System (DBMS) is the software that sits between the user and that stored data: it creates the database, accepts queries, enforces rules and returns results. Examiners in banking, SSC and railway papers treat this chapter as pure definition work, so the marks go to students who know the vocabulary exactly.

Why a DBMS replaced file systems

Before database software, every program kept its own data file. The same address was typed into three files, one copy was corrected and the others were not, and no program could read another's file. A DBMS removes these faults. It reduces data redundancy, so a fact is stored once. It keeps data consistent, because one correction reaches every user. It shares data among many users at the same time through concurrency control. It protects data by giving each user only the rights the administrator grants. It also supports backup and recovery, so a power failure does not destroy the day's work. The cost is that a DBMS is large, needs trained staff and needs more memory than a simple file.

Data models, oldest to newest

A data model decides how records are joined to one another. The hierarchical model, used in IBM's IMS in the 1960s, is the oldest: records form a tree and each child has exactly one parent. The network model allows a child to have many parents, so it stores a graph. The relational model, proposed by Edgar Frank Codd in 1970, stores everything in simple two-dimensional tables and joins them by matching values, and it is the model behind almost every exam question. The object-oriented model stores objects with their methods and is the newest of the four.

The words for a table and its parts

Keys: the heart of the chapter

• Super key: any set of attributes that identifies a row uniquely.
• Candidate key: a minimal super key, with no spare attribute in it.
• Primary key: the candidate key the designer chooses; it can never be null and never repeat.
• Alternate key: a candidate key that was not chosen as the primary key.
• Composite key: a primary key made of two or more columns together.
• Foreign key: a column that holds values of another table's primary key and so links the two tables.

SQL and its four command groups

Structured Query Language is the standard language of relational databases. It was created at IBM in the 1970s as SEQUEL and is a non-procedural language: the user states what is wanted, not how to fetch it. Its commands fall into groups that papers ask by name. DDL, the Data Definition Language, changes structure: CREATE, ALTER, DROP, TRUNCATE and RENAME. DML, the Data Manipulation Language, changes or reads the rows: INSERT, UPDATE, DELETE and SELECT. DCL, the Data Control Language, handles rights with GRANT and REVOKE. TCL, the Transaction Control Language, closes or undoes a transaction with COMMIT, ROLLBACK and SAVEPOINT. Remember the trap: DELETE removes chosen rows and can be rolled back, while TRUNCATE empties the whole table as a structural command.

Normalisation and the ACID properties

Normalisation splits a large table into smaller ones so that the same fact is not repeated and an update cannot leave the database half correct. First normal form removes repeating groups and keeps every value atomic. Second normal form removes partial dependency on part of a composite key. Third normal form removes transitive dependency, where a non-key column depends on another non-key column. Boyce-Codd normal form is a stricter form of the third. A transaction, meanwhile, must satisfy four properties known by the word ACID: atomicity, so all its steps happen or none do; consistency, so the database moves from one valid state to another; isolation, so parallel transactions do not disturb each other; and durability, so a committed change survives a crash.

Software students should be able to name

Oracle, MySQL, PostgreSQL, Microsoft SQL Server, IBM Db2 and SQLite are relational systems; Microsoft Access is the desktop DBMS bundled with Office. MongoDB is a NoSQL system that stores documents instead of rows and is asked as the odd one out. The person who designs and maintains the whole system is the Database Administrator, and the diagram that shows entities, attributes and relationships before a table is built is the Entity Relationship diagram, drawn with rectangles for entities, ovals for attributes and diamonds for relationships.