In: Computer Science
Normalization
This is a Database Development problem. Go through all the steps and process of normalization. Do normalization/dependency diagram FIRST. Then create a dependency diagram normalizing the following business rules to 3NF. Once normalized, create an accurate Entity Relationship Diagram. (ERD)
Business Scenario: You have been hired by a law firm to create a database that keeps track of the specific areas of law that each of their attorneys currently practice and their current caseload.
Business Rules:
1. An attorney can practice any area of law once he/she is admitted to their state’s Bar Association. However, most attorneys specialize in specific areas of law.
2. Our law firm is organized into departments based on the types of cases we receive in the areas of labor law, family law, and personal injury law.
3. Within these main departments, attorneys have specializations. For example, we have some attorneys who only handle worker’s compensation cases in our labor law area.
4. An attorney can only accept cases in their currently assigned department.
5. A client can have many open cases with our law firm at any one time.
6. Each client’s case is assigned to an attorney who specializes in that case’s form of law
Attorney Table
Attorney ID | Attorney Name | Area of Expertise | Case | Department | Client ID |
Client Table
Client ID | Client Name | Case |
First Normal Form
Attorney ID | Attorney Name | Case |
Attorney ID | Attorney Name | Department |
Second Normal Form
Both table satisfies partial dependencies for 2NF
Attorney ID | Attorney Name |
Attorney ID | Department | Case | Area of Expertise |
Third Normal Form
Attorney table
Attorney table contains two transitive dependencies for the following column name i.e., Department and Case. Hence it is placed in a separate table.
Attorney ID | Attorney Name |
Attorney ID | Area of Expertise |
Department | Case |
Client Table
Client name and Case attributes are transitive dependencies.It must be placed in a separate table for the client table.
Client ID | Client Name |
Client Name | Case |
Entity Relationship Diagram