CS Professional · Artificial Intelligence, Data Analytics and Cyber Security - Laws and Practice · Database Management
An e-commerce firm stores personal data of buyers. Its database designer finds that a Buyer table holds buyer name, city and also the city's PIN-code region manager, which depends only on city and not on the buyer key. Which normalisation problem does this represent, and what is the remedy?
This is a transitive dependency that violates third normal form, because the manager depends on city, a non-key attribute, rather than directly on the buyer key. The remedy is to move city and manager into a separate table linked by a foreign key.
- ATransitive dependency violating third normal form; move city and manager into a separate tableCorrect
- BPartial dependency violating second normal form; add a composite key to the Buyer table
- CMulti-valued attribute violating first normal form; split the name into two columns
- DNo violation, as the manager is a non-key attribute of the same row
Explanation
The manager depends on city, which is a non-key attribute that depends on the buyer key. That is a transitive dependency, which breaks 3NF. The fix is to create a City table with the manager and reference it by foreign key. Partial dependency needs a composite key, which is not present here.
Did you get it right without looking?
One question tells you little. A timed set on Database Management shows your real accuracy, how long you take and where you lose marks.
More Database Management questions
- In an ER design for a listed company's compliance system, 'Dependent' (family members of an employee) has no key of its own and is identifie…
- In an Employee table, Emp_ID is the primary key, and columns are Dept_ID and Dept_Head, where Dept_ID determines Dept_Head. A company wants …
- A web application builds its SQL query by directly joining text typed by a user into the query string. An attacker enters input that alters …
- A company's payment database transfers Rs 5,000 from Account A to Account B. The debit on A succeeds, but the system crashes before the cred…
- A bank's employee, while a transaction updating a customer's balance is still uncommitted, is shown by another session a balance value that …
- In the ETL process used to populate a data warehouse, which activity is performed in the 'Transform' step?