IB Computer Science A.2 the Relational Database Model Questionbank

(d) Identify three reasons for implementing views (virtual tables) in a relational database.

Jackson City University has a Music Department that provides music lessons to students in a number of high schools in the city. The Jackson City University Music Department teachers visit the different schools in the city to teach students a range of musical instruments. The following diagram shows an unnormalized table of student data.

(a) Explain one benefit of normalizing a database.

(b) Identify three ways that incorrect data could be prevented from being added into the School_phone_no field.

(c) Outline what would be necessary to make the above unnormalized table conform to 1st Normal Form (1NF).

(d) Construct the 3rd Normal Form (3NF) of the unnormalized relation shown above.

(a) Identify two issues that can occur when a database suffers from an update anomaly.

(b) Define the term referential integrity.

(c) Explain the importance of consistency in a database transaction.

(d) Distinguish between the conceptual schema and the external schema in the database architecture.

A bookstore has hired you to design a database for keeping track of their books. They have provided the following description:

• each book is entered into the database with a title, an author, a publication date, a list price and the number of copies in the bookstore
• each bookcase contains shelves and has an aisle number and a bookcase number (e.g., bookcase 23 has 5 shelves and is on aisle 4)
• each shelf contains book titles
• a sales log contains a book title, the customer name, the price paid and the date of the sale.

(a) Describe the characteristics of a normalized database.

(b) Outline the issues caused by redundant data.

(c) Construct an entity-relationship diagram (ERD) for the bookstore's database that exactly represents the information in the four bullet points shown above.

(d) Identify a component of the ERD that is not consistent with 3rd Normal Form (3NF).

The following data represent orders for shelving units. Each order specifies: - an order number

• an order date
• a product code
• a description
• a quantity
• unit prices
• a total price. The shelving units are available in wood or plastic, in small, medium or large size. There is also an extra-strong shelving unit, made from steel, which is only available in the large size. | OrderNr | OrderDate | ProductCode | Description | Quantity | UnitPrice | TotalPrice | | :--- | :--- | :--- | :--- | :--- | :--- | :--- | | 100 | $10 / 10 / 2014$ | WS | Wood, Small | 1 | 30.00 | | | | | WL | Wood, Large | 1 | 80.00 | | | | | PS | Plastic, Small | 2 | 40.00 | 190.00 | | 101 | $11 / 10 / 2014$ | PS | Plastic, Small | 1 | 40.00 | | | | | PM | Plastic, Medium | 2 | 75.00 | 190.00 | | 102 | $11 / 10 / 2014$ | XX | Extra-strong, Large | 3 | 200.00 | | | | | | | | | 600.00 |

(a) Outline why this table is not in 1st Normal Form (1NF).

(b) State three advantages of normalization.

(c) State, with a reason, whether "TotalPrice" alone can be a primary key.

(d) Suggest a way to ensure that "Description" contains atomic values in a table.