---
title: DB Fin (06/12)
tags: "DB"
---

# Database System <small>*(CE6039\*)*</small> <br>Final Exam <br> Answer Sheet

> ==TODO==
> ==TODO==

---

[TOC]

---

## 1. (30%)
> Assume an "account" relation:
>
> | Account#<br>($A$) | Balance<br>($B$) | Type<br>($T$) | Maintain-fee<br>($M$) | Penalty<br>\($P$\) | Interest-rate<br>($I$) |
> |:-----------------:|:----------------:|:-------------:|:---------------------:|:------------------:|:----------------------:|
>
> And we have the following dependencies:
> $\{A \rightarrow \{B,T,M,P,I\},\ BT \rightarrow \{P,I\},\ P \rightarrow B,\ T \rightarrow M\}$

### (a) (10%)
> Find a minimal set of functional dependency.

### (b) (10%)
> Derive a BCNF (1NF, 2NF, 3NF also).

### \(c\) (10%)
> For the outcome of (b), please verify if this is a decomposition with non-additive join property or not.

## 2. (20%)
> 3NF and BCNF can help dealing with what kinds of insert/delete/update anomaly? Please use examples to explain.

## 3. (30%)
> Views (virtual tables) may be created by properly joining normalized base tables. But after the views are formed, they may not be normalized relations. Please answer and discuss for the following problems:

### (1)
> Could there be partial and/or transitive dependencies within any joined views?

### (2)
> Can there be insert/delete/update anomalies, or even spurious tuples if you allow updates on views? Either explain why there would not be such shortcomings, or discuss on how to deal with the problems.

## 4. (20%)
> Considering the functional dependency rule $X \rightarrow Y$, where $X,Y$ are both set of attributes. If the values of $Y$'s content are confidential and need to be protected, we should also keep <u>part of</u> $X$'s content as confidential. Because someone may use $X$'s content to infer $Y$'s information. Suppose we initially identify a group of attributes $C$ as confidential, and we provide a minimal cover of functional dependency $F$. Please design a method to identify a set of attributes $S$ which have also to be protected from inferring $C$. ($S$'s size need to be as small as possible).