3.2 Databases (OCR A Level Computer Science) Flashcards

False.

A flat file database often causes data redundancy and is harder to maintain than a relational database.

The flat file database would require repeating the customer's data for each car, causing redundant data and possible inconsistencies.

A table is in First Normal Form (1NF) if it contains only atomic values, has no repeating groups, has unique column names, and includes a unique identifier (primary key) for each row.

A table is in first normal form if it contains atomic values and has no repeating groups.

A value is atomic if it is a single, indivisible value, not a list or array.

A table is in Second Normal Form (2NF) if it is in 1NF and all non-prime attributes are fully dependent on the whole of every candidate key (has no partial dependencies).

False.

Second Normal Form only applies to tables with a compound (composite) primary key, because partial dependencies can only occur in such tables.

In second normal form, all non-prime attributes must be fully dependent on the whole primary key.

A partial dependency occurs when a non-prime attribute is dependent on only part of a compound primary key.

A table is in Third Normal Form (3NF) if it is in 2NF and all non-prime attributes are dependent only on the primary key, not on other non-prime attributes (no transitive dependencies).

In third normal form, there must be no transitive dependencies between non-prime attributes.

A transitive dependency occurs when a non-prime attribute depends on another non-prime attribute, rather than directly on the primary key.

True.

A table must meet all requirements of lower normal forms before achieving a higher normal form.

A table in third normal form must also satisfy the conditions of first normal form and second normal form.

Because in Third Normal Form, each non-prime attribute must depend only on the primary key, so splitting removes transitive dependencies and maintains data integrity.

An entity is something worthy of capturing and storing data about, such as students, orders, or products.

A one to many relationship is a relationship where one record in an entity is associated with multiple records in another entity, but each record in the second entity is associated with only one record in the first.

An entity relationship diagram (ERD) is a diagram that represents the entities (tables) in a database and the relationships between them.

A entity in a relational database becomes a table.

The relationship between a customer and an order is one to many.

A many to many relationship cannot be directly implemented in a relational database and must be resolved using a link table.

The three main types of entity relationships are one to one, one to many, and many to many.

False.

In a relational database, different entities are stored in separate tables.

The relationship between an order and a product is many to many, since an order can contain multiple products and a product can be on multiple orders.

A foreign key in an ERD indicates a field in one table that links to the primary key of another, showing a relationship between the entities.

True.

Crow's feet notation is commonly used in entity relationship diagrams to show the type of relationship between entities.

To resolve a many to many relationship, a new link table is created between the two entities.

An ERD shows the entities (tables), their relationships, and which tables will have a foreign key.

Optical Mark Recognition (OMR) is a technology that detects marked areas on paper using a special machine, commonly used for exams and surveys to automate data collection.

A barcode is a machine-readable representation of data using parallel lines of varying widths and spacings, commonly used for tracking items.

The main purpose of a data capture form is to collect user input and organize data in a structured format, commonly in web applications.

True.

Sensors convert physical signals into digital data to enable automated data collection and real-time monitoring in various applications.

Query By Example (QBE) is a user-friendly method for constructing database queries using a visual interface, allowing users to search for data by providing an example of the desired output.

A key advantage of QBE is that it provides a more accessible and easy-to-use interface for users, especially non-technical users, compared to writing complex SQL queries.

True.

QBE supports joining tables and aggregation operations such as counting or averaging values.

In Query By Example (QBE), users enter criteria in the QBE grid or fields, and the system translates it into an SQL query.

A Database Manipulation Language (DML) is a set of commands, such as SQL, used to add, edit or delete data in a database.

A CSV stores tabular data in plain text using commas to separate values.

The main advantage of EDI is that it streamlines transactions by electronically transferring documents and data, reducing paper usage and manual processes.

False.

APIs are protocols that allow different software applications, regardless of programming language, to communicate and exchange data.

SQL stands for Structured Query Language, a programming language used to interact with a DBMS.

The SELECT statement retrieves data from a database table.

The WHERE clause in SQL is used to filter which rows are returned from a table.

True.

The LIKE operator allows pattern matching using wildcards such as % in SQL.

A nested SELECT is a SELECT statement placed inside another SELECT statement, often used to filter results based on a subquery.

An INNER JOIN combines rows from two or more tables based on a related column, returning only records that have matching values in both tables.

True.

A nested SELECT is often used in a WHERE clause to compare values, such as finding all records where a field is greater than the average of that field in the table.

To retrieve user names and their corresponding order IDs from two tables, you use an INNER JOIN clause to combine the tables based on a related column.

The INSERT command is used to add new data to a database table.

You use the INSERT command to add a new record to a table in SQL.

To insert a new user named John Doe who is 25 into the users table, the SQL command would be: INSERT INTO users (name, age) VALUES ('John Doe', 25);

False.

The INSERT command can add multiple rows at once by specifying multiple sets of values.

The DELETE command is an SQL statement used to remove records from a database table based on specified conditions.

This SQL statement removes all records from the users table where the age is less than 18.

To remove all employees from the Employees table who work in the Marketing department, use the command: DELETE FROM Employees WHERE Department= 'Marketing'.

True.

If no WHERE clause is given, the DELETE statement removes all records from the table.

The DROP TABLE command permanently deletes an entire table and its data from a database.

Executing DROP TABLE Employees; will delete the Employees table and all its data permanently from the database.

To remove a table named Users from a database, use the command: DROP TABLE Users;.

False.

The DROP command deletes entire tables, not individual rows. To delete rows, you use the DELETE command instead.

Referential integrity ensures consistency between related tables in a relational database by maintaining valid relationships between primary and foreign keys.

False.

Referential integrity requires that every foreign key value must match an existing primary key in the related table or be null (if allowed).

A database enforces referential integrity by ensuring that each foreign key value in one table matches a primary key value in another table.

A foreign key constraint ensures that a value in a foreign key field either matches a primary key value in the related table or is null (if allowed), helping to enforce referential integrity.

When a record with a primary key is deleted and related foreign keys are also deleted, this is called a cascaded delete.

The SET NULL action sets the foreign key value to null in related records when the referenced primary key is deleted or updated.

A benefit is that referential integrity prevents orphaned records and ensures data consistency. A drawback is that it may impact performance due to additional checks.

To maintain referential integrity, a database system can use DBMS with built-in support and implement triggers to enforce custom rules.

A transaction is a sequence of database operations treated as a single unit of work. All operations in a transaction must succeed together or fail together to ensure data consistency and integrity.

If two or more transactions are waiting for each other to release resources, a deadlock occurs, causing them to wait indefinitely.

Locking ensures that no two transactions can access the same data simultaneously, preventing data inconsistencies during simultaneous access.

True.

The commit operation makes all changes in the transaction permanent in the database.

Atomicity means that all operations in a transaction must succeed or fail as a whole. If any operation fails, the transaction is rolled back, preventing partial completion.

Consistency ensures that a database remains in a valid state before and after a transaction, following all rules and constraints, so data integrity is not compromised.

In ACID, Isolation means that each transaction is processed independently and intermediate states are not visible to other transactions.

False.

Durability ensures that committed transactions persist even if the system fails, so data is not lost after a transaction is complete.

Record locking is a technique used in database management systems (DBMS) to prevent conflicting access to data by multiple transactions or processes, ensuring data consistency and integrity.

A lock is a mechanism that prevents access to a database record by other transactions while a specific transaction is using the record.

False.

A shared lock allows multiple transactions to read a record simultaneously, but does not allow modifications until the lock is released.

The main benefit of record locking is that it maintains data consistency and integrity by preventing conflicting access and modifications by multiple transactions.

In a database, redundancy occurs when the same piece of data is stored in more than one table, either by accident or by design.

True.

Redundancy can cause data to be inconsistent if updates are not made everywhere, and it also wastes storage space by duplicating data.