Access Examples

Relationships in a database

Tuesday, January 15th, 2008 |

Once you have set up different tables for each subject in your database, you need a way to tell Microsoft Access how to gather this information again. The first step in this process is to define relationships between your tables. After having done this, you can create queries, forms and reports to display information from multiple tables at once.

How relationships?

A one-to-many
The one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.

A many-to-many
In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called an association table) whose primary key consists of two fields  foreign keys from both Tables A and B. In fact, a many-to-many relationships are a two-to-many relationship with a third table. For example, the Orders table and the Products table have a many-to-many relationship that is defined by creating two one-to-many for the Order Details table.

A one-to-one
In a one-to-one, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common because most information related well be in a single table. The use of a one-to-one is recommended when you want to share a table with many fields, to isolate part of a table for security or store information that applies only to a subset of the main table. You may want, for example, create a table to record the employees participating in a football game to raise funds.

Defining relationships
You define a relationship by adding the tables you want to relate to the Relationships window and then dragging the key field of a table and dropping it into the key of another table.
The kind of relationship that Microsoft Access creates depends on how the related fields are defined:
• A one-to-many relationship is created if only one of the related fields is a primary key or has a unique index.
• A one-to-one is created when both the related fields are primary keys or have unique indexes.
• In fact, a many-to-many relationships are a two-to-many relationship with a third table whose primary key consists of two fields  foreign keys of the other two tables.
Note If you drag a field that is not a primary key and does not have a unique index to another field that is not a primary key and does not have a unique index, an indeterminate relationship is created. In queries containing tables with an indeterminate relationship, Microsoft Access displays a default join line between the tables, but referential integrity is not enforced and there is no guarantee that records are unique in each table.

Posted on primary keys , relations , tables | 19 Comments »

Standardization (Choice of primary keys)

Saturday, December 22nd, 2007 |

It is the formal process step by step to examine the attributes of an entity, in order to avoid anomalies observed in the addition, deletion and modification of unique sequences.

Objectives

1) Minimization of duplication and inconsistencies;
2) Ease of handling database;
3) Maintainability Information System.

1st Normal Form (1NF)

A relation is in 1NF if only all the basic fields contain only unique values ​​(does not contain repeating groups).

Procedures:

a) Identify the primary key of the entity;
b) Identify the repetitive group and removes it from the body;
c) Create a new entity with the primary key of the entity and the group before repeating.

The primary key of the new entity will be obtained by concatenation of the primary key of the original entity and the repetitive group.

Ex: Request

(Numero_pedido; data_pedido; cod_cliente; customer_name; endereco_cliente; (codigo_produto; numero_produto; quantidade_produto; preco_produto; total_produto) total_pedido)

Request => (numero_pedido; data_pedido; cod_cliente; customer_name; endereco_cliente)

Item_Pedido => (numero_pedido; cod_produto; nome_produto; quantity; preco_produto; total_produto)

Functional Dependency

A given ratio R, the attribute X is functionally dependent R X R attribute is only necessary when two R sequences to combine values ​​of X they also match the value of X.

2nd Normal Form (2NF)

A relation R is in 2NF if and only if it is the first and all nonkey attributes are fully dependent on primary key (dependent on the entire key and not just part of it).

Procedures:

a) Identify the attributes that are not functionally dependent on the entire primary key.
b) Remove the entity identified all these attributes and create a new entity with them.
The primary key of the new entity will be the attribute of which the attributes are removed attributes which are functionally dependent.

Product => (cod_produto, nome_produto, preco_produto)

Transitive Dependency

Let A, B and C three attributes of an entity X. C depending on whether B and B depend on the C is then said to transitively dependent A.

3rd Normal Form (3NF)

A relation R is in 3NF if only you are in 2NF and every nonkey attributes are dependent on the primary key is not transitive (each attribute is functionally dependent only on the primary key attributes of the components or if all its non-key attributes are mutually independent) .

Procedures:

a) Identify all attributes that are functionally dependent on other nonkey attributes;
b) Remove them and create a new entity with the same.
The primary key of the new entity will be the attribute which removed the attributes are functionally dependent.

Customer (cod_cliente; customer_name; endereco_cliente)
Product (cod_produto; nome_produto; preco_produto)
Request (numero_pedido; data_pedido; ; Total_pedido)

Posted on primary keys , tables | No Comments »