10 Databases

Data Model

An abstract model for organising data and its relationship to real world entities. (PMT 10.1)

Entity-Relationship Modelling

A method of abstractly describing the data tables and the relationships between them visually. (PMT 10.1)

Attribute

A characteristic specification that defines a property or feature about an entity stored in a database. (PMT 10.2 / Spec 4.10.2)

Primary Key

A unique identifier that identifies each record in a table. (PMT 10.2 / Spec 4.10.2)

Composite Primary Key

A primary key made up from two or more other keys/attributes combined. (PMT 10.2 / Spec 4.10.2)

Foreign Key

An attribute in a table which is the primary key in another, related, table. (PMT Advanced Notes 10.1 / Spec 4.10.2)

Relational Database

A database where separate tables are made for each entity, and relationships between entities are represented by foreign keys. (PMT 10.2 / Spec 4.10.2)

First Normal Form (1NF) definition (2)

A table with - no repeating attributes (unique)- where the intersection of each record and attribute produces exactly one value (atomic) (PMT 10.3)

Atomic Data

Data that has been split so that no single column contains more than one value. (PMT Advanced Notes 10.3)

Second Normal Form (2NF) definition (2)

• A table in 1NF - no partial key dependencies (if the key is composite). (PMT 10.3)

Partial Key Dependency

Occurs when a non-key attribute depends on only part of a composite primary key. (PMT Advanced Notes 10.3)

Third Normal Form (3NF) definition (2)

A table - in 2NF - no non-key/transient dependencies (all attributes must not be dependent on non key attributes) (PMT 10.3)

3NF "Mnemonic"

1: All non-key attributes depend on the key, 2: the whole key,3: and nothing but the key. (PMT Advanced Notes 10.3)

Non-key Dependency

A dependency where a non-key attribute depends on another non-key attribute rather than the primary key. (PMT 10.3 / MS June 2013 Q9a)

Normalisation: Data Consistency

Normalisation improves consistency by ensuring that if data is changed, it only needs to be updated in one place. (PMT Advanced Notes 10.3)

SQL: Declarative Language

A language where the programmer describes the result required rather than describing the process to be followed. (PMT Advanced Notes 10.4)

SQL: SELECT purpose

Used for retrieving data from a database table. (PMT Advanced Notes 10.4 / Spec 4.10.4)

SQL: UPDATE purpose

Used for modifying the attributes of an existing entity. (PMT Advanced Notes 10.4 / Spec 4.10.4)

SQL: INSERT purpose

Used to add new records to an existing table. (PMT Concise Notes 10.4 / Spec 4.10.4)

SQL: DELETE purpose

Used for removing records from a table. (PMT Advanced Notes 10.4 / Spec 4.10.4)

SQL: CREATE TABLE purpose

Used to make new database tables by specifying the name, attributes, and data types. (PMT Advanced Notes 10.4)

SQL: Data Definition Language (DDL)

SQL commands used to create or modify the structure of the database (e.g. CREATE TABLE). (MS June 2011 Comp 3 Q7c)

SQL: Command to add a field to a table

ALTER TABLE (MS June 2013 Comp 3 Q9e)

SQL Wildcard

Usually notated with an asterisk (*); used to specify any possible value. (PMT Advanced Notes 10.4)

Client-Server Database System

A system that provides simultaneous access to the database for multiple clients. (PMT 10.5 / Spec 4.10.5)

Concurrent Access

When different users attempt to access/edit the same field or record at the same time. (PMT Concise Notes 10.5)

Database Integrity

The idea of keeping a database consistent by ensuring changes to data are accounted for in all linked tables. (PMT 10.5)

Record Locks

A technique where a record is immediately locked to other users upon access until the first user has finished. (PMT 10.5)

Serialisation (Transaction Queueing)

Requests from users are placed in a queue and executed one at a time in FIFO order. (PMT 10.5 / MS Specimen P2 Q9.7)

Timestamp Ordering

A technique where each command is assigned an initiation time and executed in that order. (PMT 10.5)

Commitment Ordering

An algorithm works out the optimum order to execute commands to minimise impact on database integrity. (PMT 10.5)

Link Table

A new table created to model many-to-many relationships between two existing tables. (PMT Advanced Notes 10.1)

3NF Property: Determinants

Every determinant in a 3NF relation is a candidate key. (MS June 2013 Comp 3 Q9a)

Normalisation Importance: Storage

Redundant/duplicated data is minimised, which prevents wasting storage space. (MS June 2024 P2 Q7.5)

Normalisation Importance: Searching

Smaller tables created during normalisation allow for faster searching and sorting. (PMT Advanced Notes 10.3)

Update Anomaly

Occurs when data is stored more than once and every copy would need to be manually updated if it changed. (MS June 2024 P2 Q7.5)

Insertion Anomaly

When it is impossible to store data about one type of entity without first creating a record for another type of entity. (MS June 2024 P2 Q7.5)

Deletion Anomaly

When deleting a record for one type of entity unintentionally deletes data about a different type of entity. (MS June 2024 P2 Q7.5)

Entity Description format

EntityName(Attribute1, Attribute2, … ) with the entity identifier underlined. (Spec 4.10.1 / PMT Concise Notes 10.1)

Candidate Key

Any attribute (or set of attributes) that could uniquely identify a record in a table. (PMT Advanced Notes 10.2 / MS June 2013 Comp 3 Q9a)

Transitive Dependency

An alternative term for a non-key dependency where one non-key attribute depends on another. (PMT Advanced Notes 10.3 / MS June 2013 Comp 3 Q9b)

One-to-one relationship degree (1:1)

Where each instance of entity A is associated with at most one instance of entity B, and vice versa. (Spec 4.10.1)

One-to-many relationship degree (1:M)

Where an instance of entity A can be associated with multiple instances of entity B, but an instance of entity B is associated with only one instance of entity A. (Spec 4.10.1)

Many-to-many relationship degree (M:M)

Where an instance of entity A can be associated with multiple instances of entity B, and vice versa. (Spec 4.10.1)

Crow's foot notation in an E-R diagram

Represents the 'many' side of a relationship. (MS June 2022 P2 Q2.2)

[Prompt — Criterion: Data Redundancy]

Normalised Database: Redundant/duplicated data is minimised.

Unnormalised Database: Data repeats across multiple records/redundant data is present.

[Prompt — Criterion: Data Consistency/Maintenance]

Normalised Database: Easier to maintain as data is updated in one place.Unnormalised Database: Harder to perform updates as they may need to be made to multiple records. (MS June 2021 P2 Q3.2 / PMT 10.3)

[Prompt — Criterion: Dependency Rule]

2NF: No partial key dependencies (non-key attributes depend on the whole composite primary key).3NF: No non-key dependencies (non-key attributes depend on nothing but the primary key). (PMT 10.3)

[Trap: 3NF Definition]

Verbatim MS: "reject: Everything".Correct: Every attribute must be dependent on the key, the whole key, and nothing but the key. (MS June 2013 Comp 3 Q9a)

[Trap: Normalisation Benefits]

Verbatim MS: "reject: eliminate" for data redundancy/duplication.Correct: "Minimise data duplication" or "Reduce/minimise for eliminate". (MS June 2013 Comp 3 Q9a)

[Trap: SQL Statement Punctuation]

Verbatim MS: "DPT. [Deduct point] for unnecessary punctuation – allow one semicolon at the very end of the statement, but not at the end of each clause." (MS June 2022 P2 Q2.4)

[Trap: SQL Ordering Sequence]

Verbatim MS: "reject: if ASC written before [AttributeName] in ORDER BY".Correct: ORDER BY [AttributeName] ASC (or DESC). (MS June 2011 Comp 3 Q7f)

[Trap: SQL Money Data Types]

Verbatim MS: "reject: integer only types" for monetary attributes.Correct: Use MONEY, SMALLMONEY, FLOAT, DECIMAL, or CURRENCY. (MS June 2021 P2 Q3.3)

[Trap: SQL Date Literals]

Verbatim MS: "reject: no delimiters around date".Correct: Dates must be enclosed in quotation marks or hashes (e.g. "29/09/2024" or #29/09/2024#). (MS June 2024 P2 Q7.2)

[Trap: SQL Numeric Literals]

Verbatim MS: "reject: use of delimiters around [numeric ID]".Correct: Integers should not have quotation marks (e.g. ProductID = 1, not ProductID = "1"). (MS June 2024 P2 Q7.3)

[Trap: Lock Scope in Concurrent Access]

Verbatim MS: "reject: database/data/file/table for record".Correct: An (exclusive) lock is set specifically on the "record". (MS June 2019 P2 Q1.2)

[Trap: SQL Field Formatting]

Verbatim MS: "I. [Ignore] spaces inserted into fieldnames e.g. Product Number".Correct: Use underscores or CamelCase (e.g. ProductNumber). (MS June 2011 Comp 3 Q7c)

[Trap: ALTER TABLE alternatives]

Verbatim MS: "reject: CREATE FIELD, INSERT COLUMN".Correct: ALTER TABLE. (MS June 2013 Comp 3 Q9e)

[Mechanism of timestamp ordering:]

• Timestamps are generated for each transaction to indicate their order • The database records the timestamp of the last read and write transaction for each record • The database server applies rules to determine if a transaction will result in loss of data integrity (and if so aborts it) (MS June 2024 P2 Q7.4)

Steps to model a many-to-many relationship between two tables (3)

• Create a new table (link table)

• Store the primary keys from the related tables in the new table as foreign keys

• Combine the foreign keys to form a composite primary key for the link table

[Mechanism of record locks:]

• A transaction on a record starts and an (exclusive) lock is set on the record • Other transactions/users cannot edit the record until the lock is released (MS June 2019 P2 Q1.2)

[Mechanism of serialisation (transaction queueing):]

• Requests from other users are placed in a queue • Once the first user has finished using the field, the next command in the queue is executed (PMT 10.5)

[Process of updating stock via RFID:]

• RFID reader transmits signal and induces current in tag • Tag transmits ProductID by radio wave • SELECT query checks if record exists in the database • UPDATE statement increases stock level if product exists or INSERT creates new record (MS June 2020 P2 Q3.1)

[Structure of an SQL UPDATE statement:]

• UPDATE [Table Name] • SET [Attribute] = [Value] • WHERE [Condition] (PMT Advanced Notes 10.4)

[Structure of an SQL INSERT statement:]

• INSERT INTO [Table Name] ([Field List]) • VALUES ([Value List]) (PMT Concise Notes 10.4 / MS June 2024 P2 Q7.2)

[The sequence of the "lost update problem":]

• Two users (read and) edit a record/the same data simultaneously • One user writes the record/data back/saves then the other user writes the record/data back/saves • One user’s update is lost (only one user's update is kept) (MS June 2019 P2 Q1.1)

[How a database server implements record locks:]

• Maintain information about which records are currently being accessed • When a user tries to access a record, consult this information • Only permit access if the record is not currently being used (or only permit read access) (MS Specimen P2 Q9.7)

[Sequence to represent which 'Make/Model' of car a 'Part' fits (Many-to-Many):]

• Create a new relation (e.g. PartToFitMakeModel) • Store the PartID, Make, and Model in the new relation • Make all attributes the composite primary key (or add a new unique ID) (MS June 2017 Paper 2 Q10.7)