Could you please answer this question using Business Rule and create a relationa

Question

Could you please answer this question using Business Rule and create a relational Schema. The manager of a consulting firm has asked you to evaluate a database that contains the table structure shown below Attribute Name CLIENT NUM CLIENT NAME CLIENT REGION CONTRACT DATE CONTRACT NUMBER 5841 CONTRACT AMOUNT $2.985,00.00 CONSULT CLASS 1 Sample Value 298 Marianne R. Brown James D. Smith James D. Smit Midwest 10-Feb-2016 Sample value Sample Value 289 15-Feb-2016 12-Mar-2016 5842 $670,300.00 S1.250,000.00 5843 Database Design Database Administration Services CONSULT CLASS 2 Web Applications Administration CONSULT CLASS 3 Network CONSULT CLASS CONSULTANT NUM I CONSULTANT NAME 29 34 Rachel G. Carson GeraldK Angcla M. Jamison Ricardo CONSULTANT REGION_1 Midwest CONSULTANT NUM 2 56 CONSULTANT NAME 2 Karl M. Spenser Ane 38 34 Anne T. Gerald K. Ricardo CONSULTANT REGION 2| Midwest CONSULTANT NUM 3 CONSULTANTNAME3 Julian H. Donatello Geraldo Southeast 45 J. - - Rivera CONSULTANT REGION 3 Midwest 18 Donald Chen West CONSULTANT NUM 4 CONSULTANT NAME 4 CONSULTANT REGION 4 This was created to enable the manager to match clients with consultants. The objective is to match a client within a given region with a consultant in that region and to make sure that the clients need for specific consulting services is properly matched to the consultant's expertise. For example, if the client need help with database design and is located in the Southeast, the objective is to make a match with a consultant who is located in the Southeast and whose expertise is in database design. (Although the consulting company manager tries to match consultant and client locations to minimize travel expense, it is not always possible to do so.) The following basic business rules are derived during the system analysis and design process and they must be maintained in the database schemas.

Explanation / Answer

Third Normal form (3NF)

A table design is said to be in 3NF if both the following conditions hold:

An attribute that is not part of any candidate key

is known as non-prime attribute.

In other words 3NF can be explained like this: A table is in 3NF if it is in 2NF and for each functional dependency X-> Y at least one of the following conditions hold:

An attribute that is a part of one of the candidate keys is known as prime attribute.

Example: Suppose a company wants to store the complete address of each employee, they create a table named employee_details that looks like this:

Super keys: {emp_id}, {emp_id, emp_name}, {emp_id, emp_name, emp_zip}…so on
Candidate Keys: {emp_id}
Non-prime attributes: all attributes except emp_id are non-prime as they are not part of any candidate keys.

Here, emp_state, emp_city & emp_district dependent on emp_zip. And, emp_zip is dependent on emp_id that makes non-prime attributes (emp_state, emp_city & emp_district) transitively dependent on super key (emp_id). This violates the rule of 3NF.

To make this table complies with 3NF we have to break the table into two tables to remove the transitive dependency:

employee table:

employee_zip table:

Coursework Submission Requirements • An electronic copy of your work .... One A4 page, state clearly any assumptions (i.e. Enterprise or Business rules) that you ... Write the SQL code only (i.e. no form or report) for applications A1-A4 above. ... that the produced relational schema is in 3rdNF is essential).

emp_id emp_name emp_zip emp_state emp_city emp_district 1001 John 282005 UP Agra Dayal Bagh 1002 Ajeet 222008 TN Chennai M-City 1006 Lora 282007 TN Chennai Urrapakkam 1101 Lilly 292008 UK Pauri Bhagwan 1201 Steve 222999 MP Gwalior Ratan

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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