What are Domains, Attributes, Tuples, and Relations

Domains, Attributes, Tuples, and Relations:-

Domain: A domain D is a set of atomic values. By atomic we mean that each value in the domain is indivisible (single)  as far as the formal relational model is concerned.

Keys : It uniquely identifies rows in a table

Schema: A relation schema R(Name of the table, denoted by R(A1, A2, … , An), is made up of a relation name.R and a list of attributes, A1, A2, … , An.

Attributes: Each attribute Ai is the name of a role played by some domain D in the relation schema R.

Degree: Number of attributes in a relation.

Cardinality: Number of rows in a relation.

Characteristics of Relations:

Order of attributes has no role

Order of tuples has no role

NULL- Are used to represent the values of attributes that may be unknown or may not apply to a tuple.

Domain Constraints:

Every value in a tuple must be from the domain of its attributes or NULL, if allowed for that attribute.

 Key Constraints and Constraints on NULL Values:

There are three main constraints in relational model

Key constraint

Entity integrity

Referential Integrity

Key constraints (Concepts of Keys):

Superkeys:

A superkey is a group of single or multiple keys which identifies rows in a table. A Super key may have additional attributes that are not needed for unique identification.

EmpSSN & EmpNum are superkeys in  below table

EmpSSN

EmpNum

Empname

City

9812345098

AB05

Shown

Delhi

9876512345

AB06

Roslyn

Delhi

199937890

AB07

James

Nagpur

CANDIDATE KEY is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes. The Primary key should be selected from the candidate keys. Every table must have at least a single candidate key. A table can have multiple candidate keys but only a single primary key.

In above table EmpSSn,EmpNum & Empname are Candidate key

ALTERNATE KEYS is a column or group of columns in a table that uniquely identify every row in that table. A table can have multiple choices for a primary key but only one can be set as the primary key. All the keys which are not primary key are called an Alternate Key.

For example EmpNum & Empname are Alternate keys 

FOREIGN KEY is a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity. It acts as a cross-reference between two tables as it references the primary key of another table.

Let consider two tables (a) and (b) :

(a)

DeptCode

DeptName

001

Science

002

English

005

Computer

(b)

Teacher ID

Fname

Lname

B002

David

Warner

B017

Sara

Joseph

B009

Mike

Brunton

Now, consider third table (c) in which we added foreign key in Deptcode to the Teacher name, we can create a relationship between the two tables as

(c)

Teacher ID

DeptCode

Fname

Lname

B002

002

David

Warner

B017

002

Sara

Joseph

B009

001

Mike

Brunton

Note:

This concept is also known as Referential Integrity.

COMPOUND KEY has two or more attributes that allow you to uniquely recognize a specific record. It is possible that each column may not be unique by itself within the database. However, when combined with the other column or columns the combination of composite keys become unique. 

OrderNo

PorductID

Product Name

Quantity

B005

JAP102459

Mouse

5

B005

DKT321573

USB

10

B005

OMG446789

LCD Monitor

20

B004

DKT321573

USB

15

B002

OMG446789

Laser Printer

3

Compound key of Order ID and Product ID could be used as it uniquely identified each record.

COMPOSITE KEY is a combination of two or more columns that uniquely identify rows in a table. The combination of columns guarantees uniqueness, though individually uniqueness is not guaranteed. Hence, they are combined to uniquely identify records in a table.

Note: The difference between compound and the composite key is that any part of the compound key can be a foreign key, but the composite key may or maybe not a part of the foreign key.

Entity Integrity:

Value of prime attributes may not be NULL.