Candidate Key

In the relational model of databases, a candidate key of a relation is a minimal superkey for that relation; that is, a set of attributes such that


1. the relation does not have two distinct tuples with the same values for these attributes (which means that the set of attributes is a superkey)

2.there is no proper subset of these attributes for which
(1) holds (which means that the set is minimal).


Since a relation contains no duplicate tuples, the set of all its attributes is a superkey if NULL values are not used. It follows that every relation will have at least one candidate key.



The candidate keys of a relation tell us all the possible ways we can identify its tuples. As such they are an important concept for the design database schema.

read more

Super Key

A superkey is defined in the relational model of database organization as a set of attributes of a relation variable (relvar) for which it holds that in all relations assigned to that variable there are no two distinct tuples (rows) that have the same values for the attributes in this set. Equivalently a superkey can also be defined as a set of attributes of a relvar upon which all attributes of the relvar are functionally dependent.

A superkey is a set of columns within a table whose values can be used to uniquely identify a row. A candidate key is a minimal set of columns necessary to identify a row, this is also called a minimal superkey. For example, given an employee table, consisting of the columns employeeID, name, job, and departmentID, we could use the employeeID in combination with any or all other columns of this table to uniquely identify a row in the table. Examples of superkeys in this table would be {employeeID, Name}, {employeeID, Name, job}, and {employeeID, Name, job, departmentID}.

read more

Composite & Compound keys

In database design, a compound key is a key that consists of 2 or more attributes that uniquely identify an entity occurrence. Each attribute that makes up the compound key is a simple key in its own right.

This is often confused with a composite key whereby even though this is also a key that consists of 2 or more attributes that uniquely identify an entity occurrence, at least one attribute that makes up the composite key is not a simple key in its own right.

An example might be an entity that represents the modules each student is attending at University. The entity has a studentId and a moduleCode as its primary key. Each of the attributes that make up the primary key are simple keys because each represents a unique reference when identifying a student in one instance and a module in the other.

In contrast, using the same example, imagine we identified a student by their firstName + lastName. In our table representing students on modules our primary key would now be firstName + lastName + moduleCode. Because firstName + lastName represent a unique reference to a student, it is not a simple key, it is a combination of attributes used to uniquely identify a student. Therefore the primary key for this entity is a composite key.

read more

Foreign key

In the context of relational databases, a foreign key is a referential constraint between two tables.[1] The foreign key identifies a column or a set of columns in one (referencing) table that refers to set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL). This way references can be made to link information together and it is an essential part of database normalizationExample:

CREATE TABLE table_name (

id INTEGER PRIMARY KEY,

col2 CHARACTER VARYING(20),

col3 INTEGER,

CONSTRAINT col3_fk FOREIGN KEY(col3)

REFERENCES other_table(key_col) ON DELETE CASCADE,

)

Example:

in the example below c.coutnry_id is the primary key in the reference table of Country while representing here in this query as a foreign key in the  customer tabel.

Select ct.cus_name,ct.cus_address,c.country_name

from customer ct

innner join country c on ct.country_id=c.coutnry_id

 

 

read more

Alternate or Secondary Key

With respect to relational databases, an alternate key (or secondary key) is any candidate key which is not selected to be the primary key (PK).

For example, a relational database with a table "employee" could have attributes like "employee_id", "national_insurance_number", and so on. In this case, both "employee_id" and "national_insurance_number" serve as unique identifiers for a given employee, and could thus arguably be used for a primary key. Hence, both of them are called "candidate keys". If, for example, "national_insurance_number" was chosen as the primary key, "employee_id" would become the alternate key.

read more

Surrogate Key

A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data.

Two definitions of a surrogate appear in the literature:

Surrogate (1)

This definition is based on that given by Hall, Owlett and Todd (1976). Here a surrogate represents an entity in the outside world. The surrogate is internally generated by the system but is nevertheless visible to the user or application.

Surrogate (2)

This definition is based on that given by Wieringa and De Jonge (1991). Here a surrogate represents an object in the database itself. The surrogate is internally generated by the system and is invisible to the user or application.

read more

Unique key

In relational database design, a unique key can uniquely identify each row in a table, and is closely related to the Superkey concept. A unique key comprises a single column or a set of columns. No two distinct rows in a table can have the same value (or combination of values) in those columns if NULL values are not used. Depending on its design, a table may have arbitrarily many unique keys but at most one primary key.

Unique keys do not enforce the NOT NULL constraint in practice. Because NULL is not an actual value (it represents the lack of a value), when two rows are compared, and both rows have NULL in a column, the column values are not considered to be equal. Thus, in order for a unique key to uniquely identify each row in a table, NULL values must not be used.

 Example:

CREATE TABLE table_name (

id_col INT,
col2 CHARACTER VARYING(20),
key_col SMALLINT,
CONSTRAINT key_unique UNIQUE(key_col),
)

read more

Primary Key

The Primary key wihtin the Relational Database is that attribute or combination of attributes which is set and defined with the purpose to uniquely identifies a row or record in a relation.

Example

CREATE TABLE Employee

(ID integer PRIMARY KEY,
Emp_Name varchar(30),
Emp_Address varchar(30));

read more

Database Keys

A key within the Relational database is used for specifying the uniqueness, According to Codd, Date, and all other experts, a key has only one meaning in relational theory: it is a set of one or more columns whose combined values are unique among all occurrences in a given table, some of the most used keys withing the Rrelational Database are defined below.

keys are defined in tables to access or sequence the stored data quickly and smoothly. They are also used to create links between different tables

1. Primary Key

2. Composite & Compound Keys

3. Foreign Key

4. Candidate Key

5. Super Key

6. Alternate Key or Secondary Key

7. Sarrogate Key

8. Unique Key

read more

What Nomalization is?

In the field of relational database design, normalization is a systematic way of ensuring that a database structure is suitable for general-purpose querying and free of certain undesirable characteristics—insertion, update, and deletion anomalies—that could lead to a loss of data integrity.

A standard piece of database design guidance is that the designer should create a fully normalized design; selective denormalization can subsequently be performed for performance reasons. However, some modeling disciplines, such as the dimensional modeling approach to data warehouse design, explicitly recommend non-normalized designs, i.e. designs that in large part do not adhere to 3NF.

Let understand it in an example below

Employee Address has a functional dependency on Employee ID, because a particular Employee ID value corresponds to one and only one Employee Address value. (Note that the reverse need not be true: several employees could live at the same address and therefore one Employee Address value could correspond to more than one Employee ID. Employee ID is therefore not functionally dependent on Employee Address.) An attribute may be functionally dependent either on a single attribute or on a combination of attributes. It is not possible to determine the extent to which a design is normalized without understanding what functional dependencies apply to the attributes within its tables; understanding this, in turn, requires knowledge of the problem domain. For example, an Employer may require certain employees to split their time between two locations, such as New York City and London, and therefore want to allow Employees to have more than one Employee Address. In this case, Employee Address would no longer be functionally dependent on Employee ID.

read more