Attributes:
Attributes are the descriptive properties which are owned by each entity of an
Entity Set.
Types of Attributes:
? Simple Attributes - Simple attributes are those attributes which cannot be
divided
further. Ex. Age
? Composite Attributes - Composite attributes are those attributes which are
composed
of many other simple attributes. Ex. Name, Address
? Multi Valued Attributes - Multi valued attributes are those attributes which can
take
more than one value for a given entity from an entity set. Ex. Mobile No, Email
ID ?
Derived Attributes - Derived attributes are those attributes which can be derived
from
other attribute(s). Ex. Age can be derived from DOB.
? Key Attributes - Key attributes are those attributes which can identify an entity
uniquely
in an entity set. Ex. Roll No.
Constraints:
Relational constraints are the restrictions imposed on the database contents and
operations. They ensure the correctness of data in the database.
? Domain Constraint - Domain constraint defines the domain or set of values for an
attribute. It specifies that the value taken by the attribute must be the atomic
value from
its domain.
? Tuple Uniqueness Constraint - Tuple Uniqueness constraint specifies that all the
tuples
must be necessarily unique in any relation.
? Key Constraint - All the values of the primary key must be unique. The value of
the
primary key must not be null.
? Entity Integrity Constraint - Entity integrity constraint specifies that no
attribute of
primary key must contain a null value in any relation.
? Referential Integrity Constraint - It specifies that all the values taken by the
foreign key
must either be available in the relation of the primary key or be null.
Closure of an Attribute Set:
The set of all those attributes which can be functionally determined from an
attribute set is
called a closure of that attribute set.
Keys:
A key is a set of attributes that can identify each tuple uniquely in the given
relation.
Types of Keys:
? Super Key - A superkey is a set of attributes that can identify each tuple
uniquely in the
given relation. A super key may consist of any number of attributes.
? Candidate Key - A set of minimal attribute(s) that can identify each tuple
uniquely in the
given relation is called a candidate key.
? Primary Key - A primary key is a candidate key that the database designer selects
while
designing the database. Primary Keys are unique and NOT NULL.
? Alternate Key - Candidate keys that are left unimplemented or unused after
implementing the primary key are called as alternate keys.
? Foreign Key - An attribute `X' is called as a foreign key to some other attribute
`Y' when its
values are dependent on the values of attribute `Y'. The relation in which
attribute `Y' is
present is called as the referenced relation. The relation in which attribute `X'
is present
is called as the referencing relation.
? Composite Key - A primary key composed of multiple attributes and not just a
single
attribute is called a composite key.
? Unique Key - It is unique for all the records of the table. Once assigned, its
value cannot
be changed i.e. it is non-updatable. It may have a NULL value.
Functional Dependency:
In any relation, a functional dependency ? ? ? holds if- Two tuples having same
value
of attribute ? also have same value for attribute ?.
Types of Functional Dependency:
? Trivial Functional Dependencies -
o A functional dependency X ? Y is said to be trivial if and only if Y ? X. o
Thus, if RHS of a functional dependency is a subset of LHS, then it is called a
trivial functional dependency.
? Non-Trivial Functional Dependencies -
o A functional dependency X ? Y is said to be non-trivial if and only if Y ? X. o
Thus, if there exists at least one attribute in the RHS of a functional dependency
that is not a part of LHS, then it is called a non-trivial functional dependency.
Decomposition of a Relation:
The process of breaking up or dividing a single relation into two or more sub
relations is called
the decomposition of a relation.
Properties of Decomposition:
? Lossless Decomposition - Lossless decomposition ensures
o No information is lost from the original relation during decomposition. o When
the sub relations are joined back, the same relation is obtained that was
decomposed.
? Dependency Preservation - Dependency preservation ensures
o None of the functional dependencies that hold on the original relation are lost.
o
The sub relations still hold or satisfy the functional dependencies of the original
relation.
Types of Decomposition:
? Lossless Join Decomposition:
o Consider there is a relation R which is decomposed into sub relations R1,
R2, ....,
Rn.
o This decomposition is called lossless join decomposition when the join of the sub
relations results in the same relation R that was decomposed.
o For lossless join decomposition, we always have- R1 ? R2 ? R3 ....... ? Rn = R
where ? is a natural join operator
? Lossy Join Decomposition:
o Consider there is a relation R which is decomposed into sub relations R1,
R2, ....,
Rn.
o This decomposition is called lossy join decomposition when the join of the sub
relations does not result in the same relation R that was decomposed.
o For lossy join decomposition, we always have- R1 ? R2 ? R3 ....... ? Rn ? R
where ? is a natural join operator
