Databases：練習題

Which of the following is a primary limitation of using a file-based approach compared to a relational database for data storage and retrieval?

A developer is using Data Definition Language (DDL) to create a new table. Which SQL statement correctly defines a table named Staff with an integer ID and a variable character name?

In a database with referential integrity enforced, what happens if a user attempts to delete a record from a parent table that is still referenced by a foreign key in a child table?

Which of the following data types is most appropriate for a field that stores whether a student has paid their tuition fees or not?

In a relational database, what is the effect of applying an index to a field like CustomerLastName?

When using Data Definition Language (DDL), which SQL keyword is used to permanently remove an entire table structure and all its data from the database?

In a relational database, what is the role of a secondary key?

Write a single SQL DDL statement to modify an existing table named Student by adding a new foreign key ClassID that references the Class table.

A company uses a database to track its sales and inventory. Two of the tables in the database are PRODUCT and CATEGORY. Every product belongs to exactly one category, while a category can contain many products.

a) Define the term foreign key and identify which table should contain it in this scenario. (2 points)
b) Explain the importance of referential integrity when a user attempts to delete a record from the CATEGORY table. (2 points)
c) Write an SQL query to display the ProductName and the CategoryName for all products where the Price is greater than 100. Assume the primary keys are ProductID and CategoryID respectively. (3 points)

A retail company stores sales data in a table that is currently in First Normal Form (1NF). The table structure is:
SALES(StoreID, ProductID, StoreLocation, ProductName, UnitPrice, QuantitySold)

a) Explain why this table is not in Second Normal Form (2NF), referring specifically to partial functional dependencies. (3 points)
b) Redesign the data into a set of tables that are in 2NF. (2 points)