DBMS Database management system is software that is used to manage the
database.
What is Database
The database is a collection of inter-related data which is used to retrieve,
insert and delete the data efficiently. It is also used to organize the data in
the form of a table, schema, views, and reports, etc.
For example: The college Database organizes the data about the admin,
staff, students and faculty etc.
Using the database, you can easily retrieve, insert, and delete the
information.
Types of Databases
Cloud Database, Relational Database, Distributed Database
Difference between DBMS and RDBMS The main differences between
DBMS and RDBMS are given below:
No. DBMS RDBMS
) DBMS applications store data as file. RDBMS applications store data in a tabular form.
) In DBMS, data is generally stored in either a hierarchical form In RDBMS, the tables have an identifier called primary key and th
or a navigational form. data
values are stored in the form of tables.
) Normalization is not present in DBMS. Normalization is present in RDBMS.
) DBMS does not apply any security with regards to data manipulation. RDBMS defines the integrity constraint for the purpose of ACID
(Atomocity, Consistency, Isolation and Durability) property.
) DBMS uses file system to store data, so there will be in RDBMS, data values are stored in the form of tables,
no relation between the tables. so a relationship
between these data values will be stored in the form of a table a
well.
) DBMS has to provide some uniform methods to access the stored RDBMS system supports a tabular structure of the data and
information. relationship
between them to access the stored information.
) DBMS does not support distributed database. RDBMS supports distributed database.
) DBMS is meant to be for small organization and deal with small data. RDBMS is designed to handle large amount of data.
it supports single user. it supports multiple users.
) Examples of DBMS are file systems, xml etc. Example of RDBMS are mysql, postgre, sql server, oracle etc.
Differences between connected and unconnected lookup?
Connected lookup Unconnected lookup
Receives input values diectly from the Receives input values from the result of a lkp expression in a
pipe line. another transformation.
U can use a dynamic or static cache U can use a static cache.
Cache includes all lookup columns used Cache includes all lookup out put ports in the lookup condition
in the maping and the lookup/return port.
Support user defined default values Does not support user defiend default values
Difference between Decode and IIF in Informatica?
DECODE
IIF
Decode can be used in Select statement
IIF cannot be used in a Select statement.
EXAMPLE:
DECODE(deptname,”SALES”,1,0)
—-If deptname is SALES then
return 1 else return zero EXAMPLE:IIF( GRADE > 1, IIF( GRADE < 2, SAL1,
IIF( GRADE < 3, SAL2, IIF( GRADE < 4, SAL3, BONUS))), 0 )
—-If the GRADE less than ONE then return zero else return SAL1 OR SAL2 OR SAL3 OR BONUS
Decode gives better readablity to users IIF dosenot give good readability
Difference between Joiner and Union Transformation in Informatica?
JOINER
UNION
Using joiner we can remove duplicate rows
Union will not remove duplicate rows
Joiner can be Normal,Right Outer,Left Outer,Full Outer Join
Union is equlivalent to UNION ALL in SQL
In Joiner we have one input group and one output group
In Union we have multiple input groups and one outputgroup.
Joiner implemented by using Joiner Transformation in Informatica.
Union implemented by using Union Transformation in Informatica
Joiner Transformation combines data record horizontally based on a join condition
Union Transformation combines data record vertically from multiple sources
Joiner supports both homogenous and heterogenous
Union also supports hetregenous(different sources)
Difference between Aggregator and Sorter in Informatica?
Aggregator Sorter
Aggregator transformation performs Sorter Transformation is used to sort
aggregate calculations, such as the data
averages and sums.
Aggregator uses aggregate cache to Sorter uses Sorter cache to perform
perform aggregate calculations the sort operation
Aggregation done by using aggregate Sorting is done based on the sort key
expression with conditional clauses, and the order of sort key
Aggregator uses Incremental There is nothing called incremental
aggregation to perform aggregation aggregation in sorter
calculations incrementally.
Difference between Sorter and Rank transformation in Informatica?
Sorter
Rank
Sorter is used to Sort the data either ASC or DSC
Rank is used to arrange data from top or bottom Group by can be done using Rank
Sorter can be used to remove duplicates(Use Distinct ouptut)
We can remove duplicates using Rank
In sorter we cannot assign values to the ports
In rank we can assign variables and write non-aggregate expressions also.
In sorter the Integration Service uses Sorter Cache to perform the sort operation.
In Rank the Integration Service stores group information in an index cache and row
data in a data cache.
Difference Between DROP, DELETE & TRUNCATE
Drop Delete Truncate
1. Whole structure and data will drop. 1. Structure will not drop, data will drop. 1. Structure will not
drop data will drop.
2. Can’t Rollback 2. Can Rollback 2. Can’t Rollback
3. Can’t use where clause 3. Can use WHERE clause 3. Can’t use WHERE clause
4. All data and structure drop simultaneously. 4. Row by Row data Delete4. Only all data will drop,
truncate operation drop and recreate the table.
5. Drop is a DDL Statement 5. Delete is a DML Statement 5. Truncate is a DDL Statement