Database Architect Interview questions

 1->  How to create one to one relationship.

Answer->

https://www.tutorialspoint.com/One-to-One-Relationship-in-DBMS#:~:text=One%2Dto%2DOne%20relationship%20in%20DBMS%20is%20a%20relationship%20between,of%20an%20entity%20with%20another.&text=An%20Employee%20is%20issued%20an,Card%20(ID_Card)%20are%20entities.

One-to-One relationship in DBMS is a relationship between an instance of an entity with another.

An Employee is issued an Employee ID Card. An individual employee is offered a unique ID card in the company. 

Here, Employee and ID Card (ID_Card) are entities.

A One-to-Many Unary Relationship is the association with the same entity between the same instances represented by different role groups.

Consider the relationship between Managers and Employees.The two different Roles in this relationship are- Managers and Employees. Only few Employees take the role of Managers within an organization while everyone working for an organization is an Employee.One Manager manages multiple Employees and each Manager is also an Employee.

2->   How to create one to many relationship

3->    How to create TDE

4->    How to create column Encryption

5->   If  there is column encryption how to give data to user

6-> How to create schema as an architect

7-> What is SQL injection

30 min-interview duration

how to delete from million of rows?

how to use drop, constraint and indexes in a package?

how to delete from  a specific date data?

filter,normalizer questions in informatica

Source Data: Sales table

year product month amount
-------------------------
1999  A      Jan   9600
1999  A      Feb   2000
1999  A      Mar   2500
2001  B      Jan   3000
2001  B      Feb   3500
2001  B      Mar   4000

The sales information of a product for each month is available in a separate row. I want to convert the rows for all the months in a specific year to a single row. The output is shown below:

Target Data:

year product Jan_month Feb_month2 Mar_month
-------------------------------------------
1999   A      9600       2000      2500
2001   B      3000       3500      4000 

ow to implement a mapping logic for this in informatica?
Solution:
Follow the below steps to implement the mapping logic for the above scenario in informatica:
Create a new mapping.
Drag the source into the mapping.
Create an expression transformation.
Drag the ports of source qualifier into the expression transformation.
Create the below additional ports in the expression transformation and assign the corresponding expressions
Mar_Month (output port) = IIF(month='Mar', amount, null)
Connect the expression transformation to an aggregator transformation. Connect only the ports year, product, Jan_Month, Feb_Month,Mar_Month ports of expression to aggregator transformation. Group by on year and product in aggregator transformation.
Create the below additional ports in aggregator transformation and assign the corresponding expressions:
o_Jan_Month (output port) = MAX(Jan_Month)
o_Feb_Month (output port) = MAX(Feb_Month)
o_Mar_Month (output port) = MAX(Mar_Month)
Now connect the ports year, product, o_Jan_Month, o_Feb_Month, o_Mar_Month of aggregator transformation to the target.
Save the mapping.
reference -> https://www.folkstalk.com/2013/04/convert-multiple-rows-single-row-Informatica.html