Database Quiz

What is the main purpose of database normalisation?

• To make the database faster

• To reduce data redundancy and improve data integrity

• To increase the number of tables

• To make data easier to enter

Answer explanation

The main purpose of database normalization is to reduce data redundancy and improve data integrity by organizing data efficiently, ensuring that each piece of information is stored only once.

Which field would be most suitable as a primary key in the Customers table?

• CustomerSurname

• CustomerMobile

• AccountNo

• Town

Answer explanation

AccountNo is the most suitable primary key as it uniquely identifies each customer. In contrast, CustomerSurname, CustomerMobile, and Town may not be unique for every customer, making them unsuitable as primary keys.

Why should Category be stored in a separate Categories table?

• To make queries more complex

• To allow multiple categories per product

• To avoid repeating category names for every product

• To reduce the number of products

Answer explanation

Storing Category in a separate table avoids repeating category names for every product, which simplifies data management and reduces redundancy. This design enhances database efficiency and integrity.

What problem occurs if OrderDate is stored in the same table as ProductCode and QtyOrdered?

• Transitive dependency

• Partial dependency

• Repeating groups

• Data redundancy

Answer explanation

Storing OrderDate with ProductCode and QtyOrdered can lead to data redundancy, as the same OrderDate may be repeated for multiple products, causing unnecessary duplication of data in the table.

Which table links Orders and Products together in a fully normalised design?

• Customers

• Categories

• OrderDetails

• Products

Answer explanation

The OrderDetails table serves as a junction table that links Orders and Products in a fully normalized design, allowing for a many-to-many relationship between them.

Which normal form removes transitive dependencies?

• First Normal Form (1NF)

• Second Normal Form (2NF)

• Third Normal Form (3NF)

• Fourth Normal Form (4NF)

Answer explanation

Third Normal Form (3NF) eliminates transitive dependencies by ensuring that non-key attributes are only dependent on the primary key, thus promoting a more efficient database structure.

Why is a separate OrderDetails table useful?

• It stores customer addresses

• It allows multiple products per order

• It removes the need for foreign keys

• It reduces the number of orders

Answer explanation

A separate OrderDetails table is useful because it allows multiple products per order. This structure supports complex orders, enabling customers to purchase various items in a single transaction, enhancing flexibility and organization.

Which field would be a foreign key in the Products table?

• ProductCode

• CategoryID

• Description

• SellingPrice

Answer explanation

CategoryID is the correct choice as it typically references a primary key in another table (e.g., Categories), establishing a relationship. ProductCode, Description, and SellingPrice are attributes of the product itself.

What type of relationship exists between Orders and OrderDetails?

• One-to-one

• Many-to-many

• One-to-many

• Zero-to-one

Answer explanation

The relationship between Orders and OrderDetails is one-to-many because each Order can have multiple OrderDetails associated with it, while each OrderDetail is linked to only one Order.

Which problem is most likely if the database is NOT normalised?

• Increased storage capacity

• Faster data retrieval

• Inconsistent data values

• Improved scalability

Answer explanation

If a database is not normalized, it can lead to inconsistent data values due to redundancy and anomalies. This is the primary issue, as it affects data integrity, unlike increased storage or retrieval speed.