What are the Types of Relational algebra?
Relational algebra is a procedural query language. It gives a step by step process to obtain the result of the query. It uses operators to perform queries.
Select Operation
The select operation selects tuples that satisfy a given predicate.
It is denoted by sigma (σ).
Notation: σ p(r)
Project Operation
This operation shows the list of those attributes that we wish to appear in the result. Rest of the attributes are eliminated from the table.
It is denoted by ∏.
Notation: ∏ A1, A2, An (r)
Union Operation
Suppose there are two tuples R and S. The union operation contains all the tuples that are either in R or S or both in R & S.
It eliminates the duplicate tuples. It is denoted by ∪.
Notation: R ∪ S
Intersection
Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in both R & S.
It is denoted by intersection ∩.
Notation: R ∩ S
Set Difference
Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in R but not in S.
It is denoted by intersection minus (-).
Notation: R - S
Cartesian Product
The Cartesian product is used to combine each row in one table with each row in the other table. It is also known as a cross product.
It is denoted by X.
Notation: E X D
Explain about Entity Relational Model?
ER model stands for an Entity-Relationship model. It is a high-level data model. This model is used to define the data elements and relationship for a specified system.
It develops a conceptual design for the database. It also develops a very simple and easy to design view of data.
In ER modeling, the database structure is portrayed as a diagram called an entity-relationship diagram.
Componentes Of Er modelImage Entity
An entity may be any object, class, person or place. In the ER diagram, an entity can be represented as rectangles.
Attributes
The attribute is used to describe the property of an entity. Eclipse is used to represent an attribute.
For example, id, age, contact number, name, etc. can be attributes of a student.
It is further divided into:
Key Attributes
The key attribute is used to represent the main characteristics of an entity. It represents a primary key. The key attribute is represented by an ellipse with the text underlined.
Composite Attributes
An attribute that composed of many other attributes is known as a composite attribute. The composite attribute is represented by an ellipse, and those ellipses are connected with an ellipse.
Multi-valued attribute
An attribute can have more than one value. These attributes are known as a multivalued attribute. The double oval is used to represent multivalued attribute.
Derived attribute
An attribute that can be derived from other attribute is known as a derived attribute. It can be represented by a dashed ellipse.
Relationship
A relationship is used to describe the relation between entities. Diamond or rhombus is used to represent the relationship.
It is further Divided into:
One To One Relationship
When only one instance of an entity is associated with the relationship, then it is known as one to one relationship.
One To many Relationship
When only one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then this is known as a one-to-many relationship.
Many To One Relationship
When more than one instance of the entity on the left, and only one instance of an entity on the right associates with the relationship then it is known as a many-to-one relationship.
Many To Many Relationship
When more than one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then it is known as a many-to-many relationship.
Explain about Aggregate Function with example?
In database management an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning.
Various Aggregate Functions
1) Count()
2) Sum()
3) Avg()
4) Min()
5) Max()
Count()
The count function returns the number of rows in the result. It does not count the null values.
Example: Write a query to return number of rows where salary > 20000.
Select COUNT(*) from Employee where Salary > 20000;
Sum()
This function sums up the values in the column supplied as a parameter.
Example: Write a query to get the total salary of employees.
Select SUM(salary) from Employee
Avg()
This function returns the average value of the numeric column that is supplied as a parameter.
Example: Write a query to select average salary from employee table.
Select AVG(salary) from Employee
Min()
The MIN function is used to find minimum value in the column that is supplied as a parameter. It can be used on any type of data.
Example - Write a query to find the minimum salary in employee table.
Select MIN(salary) from Employee
Max()
The MAX function is used to find maximum value in the column that is supplied as a parameter. It can be used on any type of data.
Example − Write a query to find the maximum salary in employee table.
Select MAX(salary) from Employee
Explain Different Types of Data Models in Dbms?
Data Model is the modeling of the data description, data semantics, and consistency constraints of the data. It provides the conceptual tools for describing the design of a database at each level of data abstraction. Therefore, there are following four data models used for understanding the structure of the database
Relational Data Model
This type of model designs the data in the form of rows and columns within a table. Thus, a relational model uses tables for representing data and in-between relationships. Tables are also called relations. This model was initially described by Edgar F. Codd, in 1969. The relational data model is the widely used model which is primarily used by commercial data processing applications.
Entity relationship Model
An ER model is the logical representation of data as objects and relationships among them. These objects are known as entities, and relationship is an association among these entities. This model was designed by Peter Chen and published in 1976 papers. It was widely used in database designing. A set of attributes describe the entities. For example, student_name, student_id describes the 'student' entity. A set of the same type of entities is known as an 'Entity set', and the set of the same type of relationships is known as 'relationship set'.
Object Based Data model
An extension of the ER model with notions of functions, encapsulation, and object identity, as well. This model supports a rich type system that includes structured and collection types. Thus, in 1980s, various database systems following the object-oriented approach were developed. Here, the objects are nothing but the data carrying its properties.
Semistructured Data Model
This type of data model is different from the other three data models (explained above). The semistructured data model allows the data specifications at places where the individual data items of the same type may have different attributes sets. The Extensible Markup Language, also known as XML, is widely used for representing the semistructured data. Although XML was initially designed for including the markup information to the text document, it gains importance because of its application in the exchange of data.
Explain DDl and DML? DDL
DDL stands for Data Definition Language. As the name suggests, the DDL commands help to define the structure of the databases or schema. When we execute DDL statements, it takes effect immediately. The changes made in the database using this command are saved permanently because its commands are auto-committed. The following commands come under DDL language:
CREATE: It is used to create a new database and its objects such as table, views, function, stored procedure, triggers, etc.
DROP: It is used to delete the database and its objects, including structures, from the server permanently.
ALTER: It's used to update the database structure by modifying the characteristics of an existing attribute or adding new attributes.
TRUNCATE: It is used to completely remove all data from a table, including their structure and space allocates on the server.
RENAME: This command renames the content in the database.
DMl
It stands for Data Manipulation Language. The DML commands deal with the manipulation of existing records of a database. It is responsible for all changes that occur in the database. The changes made in the database using this command can't save permanently because its commands are not auto-committed. Therefore, changes can be rollback. The following commands come under DML language:
SELECT: This command is used to extract information from a table.
INSERT: It is a SQL query that allows us to add data into a table's row.
UPDATE: This command is used to alter or modify the contents of a table.
DELETE: This command is used to delete records from a database table, either individually or in groups.
Explain BCNF with examples?
BCNF is the advance version of 3NF. It is stricter than 3NF.
A table is in BCNF if every functional dependency X → Y, X is the super key of the table.
For BCNF, the table should be in 3NF, and for every FD, LHS is super key.
Application of the general definitions of 2NF and 3NF may identify additional redundancy caused by dependencies that violate one or more candidate keys. However, despite these additional constraints, dependencies can still exist that will cause redundancy to be present in 3NF relations. This weakness in 3NF, resulted in the presentation of a stronger normal form called Boyce–Codd Normal Form (Codd, 1974).
Although, 3NF is adequate normal form for relational database, still, this (3NF) normal form may not remove 100% redundancy because of X?Y functional dependency, if X is not a candidate key of given relation. This can be solve by Boyce-Codd Normal Form (BCNF).
Example Img
Explain different Normalization 1NF 2NF 3NF?
Normalization is the process of organizing the data in the database.
Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to eliminate undesirable characteristics like Insertion, Update, and Deletion Anomalies.
Normalization divides the larger table into smaller and links them using relationships.
The normal form is used to reduce redundancy from the database table.
Types of Normal Form
1NF A relation is in 1NF if it contains an atomic value.
2NF A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional dependent on the primary key.
3NF A relation will be in 3NF if it is in 2NF and no transition dependency exists.
BCNF A stronger definition of 3NF is known as Boyce Codd's normal form.
4NF A relation will be in 4NF if it is in Boyce Codd's normal form and has no multi-valued dependency.
5NF A relation is in 5NF. If it is in 4NF and does not contain any join dependency, joining should be lossless.
