Separate columns for first, middle, and last names with MiddleName allowing NULLs provides the best balance of normalization and real-world flexibility. It allows for accurate querying and sorting by last name, accommodates cultures with multiple middle names or no middle name, and avoids the problems of parsing a single string. Using a single column for the full name violates 1NF and makes queries difficult, while forcing a middle initial doesn’t accommodate all naming conventions.

Storing the calculated LineTotal is a form of data redundancy because it can be derived from other columns (Quantity and Price). This violates the principles of normalization. The main concern is that if either Quantity or Price is updated, the LineTotal must also be updated manually, leading to potential update anomalies where the stored total becomes inconsistent with its source data. It’s generally better practice to calculate derived values in queries or views.

Creating a separate lookup (reference) table for ProductStatuses is the most normalized and maintainable approach. It ensures referential integrity through foreign keys, centralizes the definition of valid statuses, and makes it easy to add, remove, or rename statuses in the future without altering the Products table structure. It also minimizes storage compared to multiple boolean columns and is more portable than DBMS-specific ENUM types.

This is a classic one-to-many relationship where one employee (the manager) can manage many projects, but a project has only one manager. The FOREIGN KEY constraint on Projects.ManagerID referencing Employees.EmployeeID is essential. It enforces referential integrity, ensuring that every value in ManagerID corresponds to a valid, existing EmployeeID. This directly implements the business rule that a project manager must be a valid employee.

Creating appropriate indexes on the columns used for joining and filtering is the most targeted and effective performance tuning step in this scenario. Indexes allow the database to locate rows much faster than performing full table scans, which is likely the cause of the slowness with large tables. While hardware upgrades might help, they are less efficient and more costly. Rewriting a necessary, well-structured query or moving logic to the application layer are less optimal solutions that add complexity.

This describes a many-to-many (M:N) relationship. The only correct way to model this in a normalized relational database is with a junction table (also called an associative or linking table). The BookAuthors table would have at least two columns: BookID (FK to Books) and AuthorID (FK to Authors). Its primary key would typically be the composite of both columns. This design cleanly resolves the M:N relationship into two one-to-many relationships and allows for an unlimited number of authors per book.

Atomicity guarantees that a transaction is treated as a single, indivisible unit of work. It ensures that either all operations within the transaction are completed successfully and committed, or if any part fails, the entire transaction is rolled back and all data is returned to its previous state. In this crash scenario, Atomicity, typically implemented via transaction logs, would cause the incomplete transaction to be rolled back upon recovery, undoing the debit and preventing an inconsistent state where money has disappeared.

To filter based on an aggregate condition (like a count of orders per customer), you must use the HAVING clause in conjunction with GROUP BY. The sequence is: 1) GROUP BY CustomerID to collapse rows into one per customer, 2) Use aggregate functions like COUNT(*) in the SELECT list, and 3) Apply HAVING to filter the groups. The WHERE clause filters individual rows before grouping and cannot use aggregate functions. The correct syntax is ‘GROUP BY CustomerID HAVING COUNT(*) > 5’.

A CHECK constraint is a declarative, schema-level integrity constraint. It is the most direct and reliable place to enforce this domain rule. It guarantees the rule is applied consistently regardless of how the data is entered (application, direct SQL, import, etc.). While application logic and triggers can also enforce rules, they are more prone to being bypassed. A CHECK constraint is maintained by the DBMS itself and is the standard method for enforcing simple business rules on column values.

Separate columns for first, middle, and last names with MiddleName allowing NULLs provides the best balance of normalization and real-world flexibility. It allows for accurate querying and sorting by last name, accommodates cultures with multiple middle names or no middle name, and avoids the problems of parsing a single string. Using a single column for the full name violates 1NF and makes queries difficult, while forcing a middle initial doesn’t accommodate all naming conventions.0