•Relation:- A relational database is a set of tables containing data fitted into predefined categories. Each table (which is sometimes called a relation) contains one or more data categories in columns. Each row contains a unique instance of data for the categories defined by the columns.
•Tuple:-A tuple is one record (one row).
•Attribute:- In computing, an attribute is a specification that defines a property of an object, element, or file. It may also refer to or set the specific value for a given instance of such. For clarity, attributes should more correctly be considered metadata.
•Single valued Attributes : An attribute, that has a single value for a particular entity is known as single valued attributes. For example, age of a employee entity.
•Multi valued Attributes : An attributes that may have multiple values for the same entity is known as multi valued attributes. For example colors of a car entity.
•Compound Attribute/Composite Attribute : Attribute can be subdivided into two or more other Attribute. For Example, Name can be divided into First name, Middle name and Last name.
•Simple Attributes/Atomic Attributes : The attributes which cannot be divided into smaller subparts are called simple or atomic attributes. For example, age of employee entity
•Stored Attribute : An attribute, which cannot be derived from other attribute, is known as stored attribute. For example, BirthDate of employee.
•Derived Attribute : Attributes derived from other stored attribute. For example age from Date of Birth and Today’s date.
ERD Diagram symbol
•Cardinality:- The number of tuples in a relation is called cardinality.If there are 10 tuples or seconds in a table then cardinality is 10.
•Degree of relation set:-•The number of entity set that participate in a relation set.Example:Degree =3 as a table has 3 attribute.
• A domain is defined as the set of all unique values permitted for an attribute. For example, a domain of date is the set of all possible valid dates, a domain of integer is all possible whole numbers, a domain of day-of-week is Monday, Tuesday ... Sunday.
•Super Key is defined as a set of attributes within a table that uniquely identifies each record within a table. Super Key is a superset of Candidate key.
For Example(Emp_SSN,Emp_number,Emp_Name)
•Super keys:
•{Emp_SSN}
•{Emp_Number}
•{Emp_SSN, Emp_Number}
•{Emp_SSN, Emp_Name}
•{Emp_SSN, Emp_Number, Emp_Name}
•{Emp_Number, Emp_Name}
•Candidate key:- A super key with no redundant attribute is known as candidate key
•For Example(Emp_id,Emp_Number,Emp_Name)
•There are two candidate keys in above table:
{Emp_Id}
{Emp_Number}
•Primary key:- A primary is a column or set of columns in a table that uniquely identifies tuples (rows) in that table.
•Example:
Student Table(Stu_id,Stu_Name,Stu_Age)
•Stu_id is primary key
•Difference (-) Set Difference in relational algebra is same set difference operation as in set theory with the constraint that both relation should have same set of attributes.
Intersection:-Intersection operations select
common tuples from the two relations.
•Cartesian product
•For R × S, the Cartesian product operation defines a relation that is the concatenation of every tuple of relation R with every tuple of relation S.
Natural join
•The Join operation, which combines two relations to form a new relation, is one of the essential operations in the relational algebra.
•LEFT OUTER JOIN - keep data from the left-hand table
•RIGHT OUTER JOIN - keep data from the right-hand table
•FULL OUTER JOIN - keep data from both tables
•Tuple:-A tuple is one record (one row).
•Attribute:- In computing, an attribute is a specification that defines a property of an object, element, or file. It may also refer to or set the specific value for a given instance of such. For clarity, attributes should more correctly be considered metadata.
•Single valued Attributes : An attribute, that has a single value for a particular entity is known as single valued attributes. For example, age of a employee entity.
•Multi valued Attributes : An attributes that may have multiple values for the same entity is known as multi valued attributes. For example colors of a car entity.
•Compound Attribute/Composite Attribute : Attribute can be subdivided into two or more other Attribute. For Example, Name can be divided into First name, Middle name and Last name.
•Simple Attributes/Atomic Attributes : The attributes which cannot be divided into smaller subparts are called simple or atomic attributes. For example, age of employee entity
•Stored Attribute : An attribute, which cannot be derived from other attribute, is known as stored attribute. For example, BirthDate of employee.
•Derived Attribute : Attributes derived from other stored attribute. For example age from Date of Birth and Today’s date.
ERD Diagram symbol
•Degree of relation set:-•The number of entity set that participate in a relation set.Example:Degree =3 as a table has 3 attribute.
• A domain is defined as the set of all unique values permitted for an attribute. For example, a domain of date is the set of all possible valid dates, a domain of integer is all possible whole numbers, a domain of day-of-week is Monday, Tuesday ... Sunday.
•Super Key is defined as a set of attributes within a table that uniquely identifies each record within a table. Super Key is a superset of Candidate key.
For Example(Emp_SSN,Emp_number,Emp_Name)
•Super keys:
•{Emp_SSN}
•{Emp_Number}
•{Emp_SSN, Emp_Number}
•{Emp_SSN, Emp_Name}
•{Emp_SSN, Emp_Number, Emp_Name}
•{Emp_Number, Emp_Name}
•Candidate key:- A super key with no redundant attribute is known as candidate key
•For Example(Emp_id,Emp_Number,Emp_Name)
•There are two candidate keys in above table:
{Emp_Id}
{Emp_Number}
•Primary key:- A primary is a column or set of columns in a table that uniquely identifies tuples (rows) in that table.
•Example:
Student Table(Stu_id,Stu_Name,Stu_Age)
•Stu_id is primary key
Foreign key:-
Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.
Relational Algebra operations
•Selection (σ)
Selection is used to select required tuples of the relations.
•Example
•Find all tuples from player relation for which country is india.
• σ country = “India”(Player)
Projection (π)
•Projection is used to project required column data from a relation.
•Examples
•List all the countries in player relations.
•Πcountry(Player)
•Union (U)
Union operation in relational algebra is same as union operation in set theory, only constraint is for union of two relation both relation must have same set of Attributes.
•Difference (-) Set Difference in relational algebra is same set difference operation as in set theory with the constraint that both relation should have same set of attributes.
•For R × S, the Cartesian product operation defines a relation that is the concatenation of every tuple of relation R with every tuple of relation S.
•The Join operation, which combines two relations to form a new relation, is one of the essential operations in the relational algebra.
•LEFT OUTER JOIN - keep data from the left-hand table
•RIGHT OUTER JOIN - keep data from the right-hand table
•FULL OUTER JOIN - keep data from both tables
Mapping Cardinalities
•One to One: An entity of entity-set A can be associated with at most one entity of entity-set B and an entity in entity-set B can be associated with at most one entity of entity-set A.
•Automobile has engine.
•One to Many: An entity of entity-set A can be associated with any number of entities of entity-set B and an entity in entity-set B can be associated with at most one entity of entity-set A.
A Father has many children
Many to One: An entity of entity-set A can be associated with at most one entity of entity-set B and an entity in entity-set B can be associated with any number of entities of entity-set A.
Many student->1 college
•Many to Many: An entity of entity-set A can be associated with any number of entities of entity-set B and an entity in entity-set B can be associated with any number of entities of entity-set A.
Many students->enroll many courses
Many student->1 college
Many students->enroll many courses
Primary key vs secondary key
• Primary Key
1) Is used for Unique Identification of Rows
2) We have only one Primary Key per table
• Secondary Key
1) Is used for Identification of Rows but not usually Unique
2) We can have multiple Secondary Key per table
Composite key
• A key that consists of more than one attribute to uniquely identify rows (also known as records & tuples) in a table is called composite key. It is also known as compound key.
• Example: Table – Sales
(cust_id,order_id,product_code,amount)
cust_id,order_id is composite key.
