2. Consider the following relation: Bank. ID Branch ID Branch Address Branch Cit

Question

2. Consider the following relation: Bank. ID Branch ID Branch Address Branch City Branch State Branch ZipCode Bank City BranchManagerlD Application Application Amount Requested Appstats Application Date Name Cust ID Cust, Name Naine Consider this to be all one relation. I've put it on two lines to make it all fit. Identify the primary key(s). Are there any other candidate keys (fields that could be considered in choosing a primary key?) I. 2. Identify the partial functional and transitive dependencies. You can either show these 3. 4. Extra Credit (If you have time) Convert the relations to an ERID with arrows on the diagram or write them out. Convert the relation into a set of third normal form relations

Explanation / Answer

1) Based on given relational schema the primary keys are: Bank_ID, Branch_ID,Branch_ZipCode,BranchManagerID,Cust_ID,Application_ID
2)As per given schema and assumed primary keys, functional dependencies are as follows:
Bank_ID--->Bank_City,Branch_ID
Branch_ID---->Branch_Address,Branch_City,Branch_State,Branch_ZipCode,BranchManagerID
Branch_ZipCode--->Branch_City,Branch_State
BranchManagerID--->BranchManagerName
Cust_ID----->Cust_Name,Amount_Required,Branch_ID,
Application_ID----->Application_Date,AppStatus,Cust_ID

3)
In database Normalization is nothing but a database schema design process, by modifiying existing schema to minimize anomolies and dependency of data.Generally Normalization devides large tables into smaller schemas and establishes relationships among them to increases effectiveness.

First Normal Form (1NF):
As per first normal form,each and every attribute in a table must be unique. That means there should no duplication.

So that as per given information the schema is already in 1NF.

Second Normal Form (2NF):

As per second normal form,schema should be in 1NF and attributes within that table should depend individually on primary key.

As per given data {Branch_Address,Branch_City,Branch_State,Branch_ZipCode,BranchManagerID,BranchManagerName,Cust_Name,Amount_Required,Branch_ID,Application_Date,AppStatus,Cust_ID
} are not completely solely dependent on primary key{Bank_ID}. {Branch_Address,Branch_City,Branch_State,Branch_ZipCode,BranchManagerID} depennds on Branch_ID
By applying 2NF the tables sould be as follows:

Bank(Bank_ID,Bank_City,Branch_ID)
Branch(Branch_ID,Branch_Address,Branch_ZipCode)
Address( Branch_ZipCode,Branch_City,Branch_State)
Manager(BranchManagerID,BranchManagerName,Branch_ID)
Customer(Cust_ID,Cust_Name,Amount_Required,Branch_ID)
Application(Application_ID,Application_Date,AppStatus,Cust_ID)

Here I have normalized the given table by avoiding partial dependencies.

Third Normal Form (3NF):

As per 3NF, schema should be in 2NF and there should no trivial dependencies.
After normalizing given table to 2NF there are no trivial dependencies.

The above scema is in 3NF.

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.