Menu

Composite key The primary key helps to identify only each row of a table

March 30, 2019 0 Comment

Composite key
The primary key helps to identify only each row of a table. It can represent a part of a concrete record, or be an artificial field (a field that has nothing to do with actual recording). The primary key can represent one or more fields of a table. When the primary key represents multiple fields, it is called a composite key (Connolly and Begg, 2010).
To make it more meaningful I would like to use this example: We want to store available documents in several languages. Simply we could do:
CREATE TABLE docs (
id TINYINT UNSIGNED NOT NULL,
lang CHAR (3) NOT NULL,
title VARCHAR (255) NOT NULL,
author VARCHAR (255) NOT NULL
)
So, to insert docs:
INSERT INTO docs (lang, title, author) VALUES (“sp”, ” Informe “, “Jorge L “);
INSERT INTO docs (lang, title, author) VALUES (“en”, “Report”, “Simo M”);

The disadvantage is that we get two id different, and so it is almost impossible to identify a link between these two documents. Yet it is the same document, except that they are not in the same language.
The solution lies in a composite key:
If we create our table like this:
CREATE TABLE documents (
id TINYINT UNSIGNED NOT NULL,
lang CHAR (2) NOT NULL,
title VARCHAR (255) NOT NULL,
author VARCHAR (255) NOT NULL,
PRIMARY KEY (id, lang)
)
This time to insert the data we must specify the id:
INSERT INTO docs (id, lang, title, author) VALUES (“1”, “sp”, “Informe”, “Jorge L”);
INSERT INTO docs (id, lang, title, author) VALUES (“1”, “en”, “Report”, “Simo M”);
INSERT INTO docs (id, lang, title, author) VALUES (“2”, “fr”, “rapport”, “Thibaut P”);
Now we can perfectly select a document:

SELECT * FROM docs WHERE id = 1
As for indexes and primary keys, it is possible to create composite foreign keys. Also, the column (or group of columns) on which the key is created must be exactly the same type as the column (the group of columns) that it references. This implies that in case of composite key, there is the same number of columns in the key and the reference. Therefore, if number is an INT UNSIGNED, in Command must be INT UNSIGNED type as well (Kroenke et al., n.d.).
Composite Attribute
Composite attributes contain structured values, consisting of an ordered succession of fields each containing a key and a value. Their main use is to associate with each other values of other attributes of the same object, called elementary, when they contain several values; each field of the composite corresponding to an elementary attribute. An attribute can be composed hierarchically of several other attributes (composite attribute as opposed to simple or atomic attribute) (Malinowski and Zimányi, 2008).

Example: composite attribute “address”

address —- wording —– House_No
—– Street_Name
—– Appt_No
—– city
—– country
—– Postal code

In addition, composite attributes are multivalued, each value representing a different association of field values. Furthermore, a composite attribute can evolve, but only by:
1- Adding new fields at the end of the value,
2- Or inserting new fields between two existing fields, without changing the relative order (Mitrovic and Suraweera, 2004).
Overall, a composite key is a key consisting of several attributes. If an ER schema contains attribute names for all its entities, a composite key is indicated in the ER schema that confirms that there is more than an attribute name to indicate that it is shared in the primary key.

Mohammed
References:
Connolly, T. and Begg, C. (2010). Database systems. Boston: Addison-Wesley.
Kroenke, D., Auer, D., Vandenberg, S. and Yoder, R. (n.d.). Database processing.
Malinowski, E. and Zimányi, E. (2008). A conceptual model for temporal data warehouses and its transformation to the ER and the object-relational models. Data & Knowledge Engineering, 64(1), pp.101-133.
Mitrovic, A. and Suraweera, P. (2004). An intelligent tutoring system for entity relationship modelling. University of Canterbury. Computer Science and Software Engineering.