IBDP Computer Science A3.1 Database fundamentals HL Paper 2 - New Syllabus

(a)
For the correct answer:

• The conceptual schema is a high-level, least-detailed representation of the database. It identifies the entities and the high-level relationships between them.
• The logical schema is more detailed and is developed from the conceptual schema. It includes details such as field names and the structure of the data.

Explanation: The conceptual schema describes what data and relationships are required, while the logical schema provides a more detailed description of how the data is organized.

(b)
For the correct answer:

• A data definition language (DDL) is used to specify and implement the schema of a database.
• DDL can define tables, fields, data types, keys, and relationships, and can modify the database schema.

Example:

CREATE TABLE STAFF (
    StaffID INTEGER,
    FirstName VARCHAR(30),
    Surname VARCHAR(30)
);

Other valid DDL commands such as ALTER and DROP are also acceptable.

Explanation: DDL provides the commands required to create and modify the structures that form the database, allowing the data model to be implemented as actual database tables and relationships.

(c)
For the correct answer, suitable points include:

• Data modelling helps identify the entities or tables required in the database.
• It ensures that the attributes of each table are necessary and sufficient for the purpose of the database.
• It identifies keys needed to access and uniquely identify records.
• It identifies relationships between tables, allowing complex queries to be performed across multiple tables.
• Normalization during data modelling can reduce data duplication.
• Reducing duplication can reduce data anomalies and save storage space.
• A well-designed model allows other stakeholders, analysts, and programmers to understand the database structure more easily.
• This makes database development and maintenance easier.

Explanation: Data modelling provides a structured representation of how data will be organized before the database is implemented. It helps ensure that the database structure meets its intended purpose and that relationships and data dependencies are correctly designed.

(d)
For the correct answer:

• Data validation is an automated process that checks whether input data meets specified rules or is reasonable and valid.
• Data verification checks that the data entered is the data that was actually intended.
• Both are required because validation can identify data that violates known rules, while verification can identify incorrect data that may still satisfy those rules.

Example: A validation rule could prevent a date of birth from being entered outside a reasonable range. Verification could then be used to check that a person’s surname was entered correctly.

Explanation: Validation checks whether data is acceptable according to predefined rules, whereas verification checks whether the data accurately represents the intended input. Using both provides more reliable data.

(e)
For the correct answer:

• Atomicity ensures that a transaction is completed in full or that no changes are made.
• If a transaction cannot be completed, it can be rolled back to the original state.
• Consistency ensures that a transaction does not leave the database in an invalid state.
• Integrity constraints can be enforced before a transaction is committed, preventing invalid or orphaned records.
• Isolation prevents concurrent transactions from interfering with one another.
• Durability ensures that the effects of a committed transaction remain permanent, even after a system failure.

Example:

BEGIN TRANSACTION

    -- database changes

COMMIT

If the transaction cannot be completed successfully, it can be rolled back:

ROLLBACK

Explanation: Database transactions use the ACID properties: atomicity, consistency, isolation, and durability. Together, these properties help prevent invalid changes, interference between transactions, and loss of committed data.

(f)
For the correct answer:

• Referential integrity refers to the relationships between tables in a relational database.
• It is maintained through the connection between a primary key in one table and a corresponding foreign key in another table.
• The foreign key must reference an existing primary key, preventing invalid references and orphan records.
• Updates and deletes can also be cascaded from the primary-key table to the related foreign-key records where appropriate.

Example: If RoleID is a primary key in the ROLE table and a foreign key in the STAFF table, a staff record cannot contain a RoleID that does not exist in the ROLE table.

Explanation: Referential integrity ensures that relationships between related tables remain valid. This prevents invalid foreign-key values, orphan records, and inconsistencies when records are updated or deleted.