ACCTING2503 · Accounting Systems And Analytics
Databases, DBMS & Relational Databases
This topic is where ACCTING 2503 moves from processing transactions to storing them well: replacing a sprawl of separate program files with one integrated database run by a DBMS, then organising that data as relational tables that store each fact only once. You need the vocabulary exact — tuple, attribute, primary key, foreign key, and the two integrity rules — because the closed-book final grades the precise terms as much as the idea. The signature exam moves are explaining the advantages of a relational database and taking one flat table, showing its update / insert / delete anomalies, and normalising it.
What this chapter covers
- 011. Data hierarchy — field (attribute) → record → file → database; master file vs transaction file vs an integrated database
- 022. File approach vs database approach — duplicated files cause redundancy and inconsistency; a database gives integration, sharing and data independence
- 033. DBMS, DBA & data dictionary — the software between programs and data, who maintains it, and the metadata catalogue of every field
- 044. The three schema levels — external (user subschemas) → conceptual (enterprise schema) → internal (physical storage), giving data independence
- 055. DBMS languages — DDL builds the structure, DML changes the data, DQL queries it (SELECT … FROM … WHERE)
- 066. Relational vocabulary — relation = table (one entity), tuple = row = record, attribute = column = field; degree vs cardinality
- 077. Primary key vs foreign key — a unique, non-null identifier vs the same attribute borrowed into another table to create the link
- 088. Entity vs referential integrity — the PK cannot be null vs a non-null FK must match an existing PK
- 099. The three anomalies & normalisation — update, insert and delete anomalies, and how splitting into one-entity-per-table removes them
- 1010. Advantages of relational databases — the sample-exam discussion answer (redundancy, independence, sharing, flexible querying)
Spot the anomalies and normalise a flat table
- +1Update anomaly: if a customer changes email, it must be edited on every row for that customer; miss one and the table now stores two different emails for the same person — the data is inconsistent.
- +1Insert anomaly: you cannot add a brand-new customer who has not bought anything yet, because there is no sale row to attach them to (and the primary key Sale# would be null).
- +1Delete anomaly: deleting a customer's only sale also wipes out their customer details entirely — you lose data you meant to keep.
- +1Entity integrity: the primary key cannot be null and must uniquely identify each row.
- +1Referential integrity: a non-null foreign key must match an existing primary key in the related table (no sale can point to a customer who does not exist).
- +1.5Normalise — Customer(CustID [PK], Name, Email) and Product(ProductID [PK], Description, Price): each entity now lives in its own table so its facts are stored once.
- +1.5Normalise — Sale(Sale# [PK], CustID [FK], RepID [FK], ProductID [FK], Amount, Date): the Sale table carries foreign keys that reference the other tables, recreating every relationship without duplicating detail.
Key terms
- DBMS (Database Management System)
- The software that sits between application programs and the stored data, controlling access and providing data independence.
- Data dictionary
- The DBMS's metadata — 'data about data': for each field its name, type, length, source, and which programs and users may use it.
- Schema vs subschema
- The schema is the enterprise-wide conceptual view of the whole database; a subschema is one user's restricted logical view (external level).
- Tuple / attribute / relation
- In the relational model a tuple is a row (one record), an attribute is a column (a field), and a relation is a table representing one entity.
- Primary key (PK)
- An attribute (or combination) that uniquely identifies each row and cannot be null.
- Foreign key (FK)
- An attribute in one table that is the primary key of another table — the mechanism that links related tables.
- Entity integrity
- The rule that a primary key can never be null, so every row is identifiable.
- Referential integrity
- The rule that a non-null foreign key must match an existing primary key in the related table (or be null) — no orphan references.
Databases, DBMS & Relational Databases FAQ
Is this database topic on the final exam or only in the tests?
It is squarely on the closed-book final (50%). Expect an open-ended case question — 'explain the advantages of a relational database' is a listed sample-exam topic, and a 'here is one flat table, show the anomalies and normalise it' problem is a classic. Only Power BI is excluded from the final.
What is the difference between entity integrity and referential integrity?
Entity integrity means the primary key cannot be null (so every row is identifiable). Referential integrity means a non-null foreign key must match a real primary key in the related table. They are asked together so a swap is easy — anchor each to its key: PK → entity, FK → referential.
What are the actual advantages of a relational database — is it just that it is faster?
Speed is the wrong answer and loses marks. The examined advantages are eliminating redundancy and inconsistency, data integration and sharing, data independence (change storage without rewriting programs), and flexible ad-hoc querying for cross-functional reporting.
What is the difference between a primary key and a foreign key?
A primary key uniquely identifies a row in its own table and cannot be null. A foreign key is that same attribute borrowed into another table to create a relationship. For a many-to-many relationship the primary key becomes the combination of the two linked keys.
What do DDL, DML and DQL each do?
DDL (Data Definition Language) builds the structure — creating the database, data dictionary, subschemas and security. DML (Data Manipulation Language) changes the data — insert, update, delete. DQL (Data Query Language) retrieves it — SELECT … FROM … WHERE.
How is 'data independence' actually achieved?
By separating the three schema levels. Because each user's logical view (external) and the enterprise schema (conceptual) are defined apart from physical storage (internal), you can re-index or reorganise the stored data without rewriting the programs that read it.
Exam move
Treat this topic as vocabulary-plus-one-procedure. First lock the terms cold, both directions — tuple = row = record, attribute = column = field, relation = table — and the two integrity rules (PK not null; FK matches a real PK), because these are one-word marks the exam loves to test with a swap. Then rehearse the one procedure that recurs every year: given a flat table, name each anomaly with a row-level example, then normalise by giving each entity its own table with a primary key and linking them with foreign keys in the transaction table. Finally, memorise the advantages-of-a-relational-database answer as a five-point list (no redundancy/inconsistency, integration, sharing, data independence, flexible querying) and never write 'it is faster'. If you can draw the three schema levels and normalise a small table from memory, you are ready for the database question.
