Introduction to Database Concepts by Paul Elias

Author:Paul, Elias [Paul, Elias]
Language: eng
Format: epub
Published: 2020-06-20T16:00:00+00:00

introduction to BDR design

relationship in a certain normal way, for example in 2FN, can be converted in a set of relationships in a more desirable way, 3FN, and so successively. That is, this procedure is the successive reduction of a

given set of relationships to a more desirable form . It is important note that this procedure is reversible , it is always possible to take the output of the procedure and make it the input, which means it is not

loses information during the additional normalization process.

The three original normal forms of Codd and the normal form of Boyce / Codd are based on the concept of functional dependency . Will be given first a definition this concept to be able to define later when a relationship is in a certain normal way.

Proposed example

The example to be followed to clarify all definitions deals with very small form of a company that has a number of employees, those who know your NIF, your name, your address, a contact phone and his year of birth. This company has a series of projects uniquely identified by a code and whose name is known, your budget, your start date, your end date, the city where is developed and the degree of suitability of the project, this has to do with the ease of movement from the city where the headquarters of the company to the city where the project is developed and depends not only of the kilometers but also of the media that exist

among them according to a study done by the company itself. To the employees they can be assigned to work on different projects during a period of established time being able to alternate his work in different projects.

If we carry out the logical design based on the appropriate conceptual scheme, the schema of the database that we will obtain, regardless of the domains associated with the attributes, is the one shown below: EMPLOYEE (NIF, name, address, date-birth)

Primary key: NIF

CITY (city, suitable)

Primary key: city

PROJECT (code, name, budget, start, end, city)

Primary key: code

Foreign key: city → CITY

WORK (NIF, code, from, to)

Primary key: (NIF, code, from)

Foreign password: NIF → EMPLOYEE

Foreign password: code → PROJECT

But is the way we intend to store the information completely correct?

information? There are things that we are very clear that "are in place"

because it seems "natural". For example, it seems logical that if, in addition, we want to store the personal phone number of each employee, this data must be in the EMPLOYEE table.

Suppose no, that the employee's phone number is included in the table TO WORK.

89

Page 98

BD1 2006-2007

WORK (NIF, telephone , code, from, to)

Primary key: (NIF, code, from)

Foreign password: NIF → EMPLOYEE

Foreign password: code → PROJECT

A possible occurrence of the WORK table would be:

NIF

PHONE

CODE

SINCE

UNTIL

22446688A

5632224

A111

10/10/92

12/20/92

22446688A

5632224

A112

01/09/93

03/12/93

22446688A

5632224

A112

03/20/93

03/14/93

22446688A

5632224

A116

08/15/93

11/24/93

11116666B

5211111

A112

01/09/93

05/20/93

11116666B

5211111

A114

07/07/93

10/27/93

As we can see, the contact phone number of an employee is going to repeat as many times as projects is assigned. This redundancy can cause inconsistent information if possible handling is not handled carefully updates to an employee's contact phone number.

This problem arises because of

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.