Database Models and DBMS

Organising data

NoSQL databases often trade strict consistency for scale, so integrity controls may have to live in the application instead of the database.

Relational basics

• Table: rows (records) and columns (fields).
• Primary key: the unique ID of each row, like a customer number.
• Foreign key: a column that points to a primary key in another table, like the customer number on an order.
• View: a virtual table showing only selected rows or columns. Views are an access control: give a user a view that hides salary columns, and they simply can’t see salaries.
• Stored procedures and triggers: code inside the database that enforces rules or automates actions.

What the DBMS does

A database management system (DBMS) provides:

• the three SQL language types (below)
• security and access control
• concurrency control (locking, so users don’t overwrite each other)
• integrity enforcement (keys, constraints)
• backup, logging and recovery
• the data dictionary / directory system (DD/DS): metadata describing every data element, structure, relationship, owner and access rule. Unauthorised changes to it can corrupt or expose data, so it’s restricted and logged.

The three SQL languages

• DDL (data definition) changes structure: CREATE, ALTER, DROP.
• DML (data manipulation) changes data: SELECT, INSERT, UPDATE, DELETE.
• DCL (data control) changes permissions: GRANT, REVOKE.

Three-schema architecture

• External: what each user or application sees (their views).
• Conceptual: the logical design of the whole database.
• Internal: how it’s physically stored.

Keeping the levels separate means storage can change without breaking applications (data independence), and views can limit what each user sees.

Watching the DBA

The database administrator (DBA) maintains structures, performance, backup and recovery, and often access. That’s a lot of power: a DBA can change data directly and skip every application control.

• separate DBA and security administration duties where possible
• named DBA accounts, no shared logins
• DBA activity logged (especially direct data changes and structure changes) and independently reviewed
• structure changes through change management
• direct data fixes only through approved tickets

Example. On a manager’s verbal request, a DBA runs an UPDATE statement directly on the payments table to “fix” a vendor’s bank account. The application’s approval workflow for bank detail changes is skipped entirely. Database activity monitoring should flag direct changes to that table for independent review.

How the exam asks about it

In short

• Database models: hierarchical (tree), network (many-to-many links), relational (tables with keys), object-oriented, and NoSQL (document, key-value, graph, column).
• Relational databases organise data in tables of rows and columns, linked by primary and foreign keys.
• A DBMS provides data definition (DDL), manipulation (DML) and control (DCL) languages, plus security, concurrency, backup and recovery.
• The data dictionary/directory system holds metadata: definitions, structures, relationships and access rules.
• The three-schema architecture separates the external (user views), conceptual (logical) and internal (physical) levels.
• The DBA has powerful access to data and structures; their activity must be logged and independently reviewed.

Exam traps

• DDL changes structure (CREATE, ALTER, DROP); DML changes data (INSERT, UPDATE, DELETE); DCL grants rights (GRANT, REVOKE).
• Database views can restrict what users see, acting as an access control.
• Unauthorised changes to the data dictionary can corrupt or expose data; restrict and log them.
• NoSQL databases often trade strict consistency for scale; integrity controls may sit in the application instead.

Slides

Click the slide and use the arrow keys. F toggles full screen.