Physical Data Model - Design Step -3

This is third step for Designing for DWH Design 

Physical Data Model is the actual model which will be created in the database to store the data. It is the most detailed data model in Data Warehouse data modeling. It includes

1. Tables names
2. All column names of the table along with data type and size
3. Primary keys, Foreign Keys of a table
4. Constraints

Physical Data Model can be converted to actual SQL DDL statement by using different tools. ERWIN is the famous tool to do this.

Logical Data Model - Design Step -2

This is Second step for Designing 

Good Logical data model in data warehouse implementation is very important. Logical data model has to be detailed (though some might not agree) as it represents the entire business in one shot and shows relationship between business entities. Logical Model should have following things to make it detailed and self explanatory.

1. All entities to be included in data warehouse
2. All possible attributes of each entity
3. Primary keys of each entity ( Natural Keys as well as Surrogate Keys )
4. Relationships between each every entity

Characteristics of Data Warehouse Logical Data Model

1. It has all the entities which will be used in data warehouse
2. It shows all possible attributes of all entities
3. It depicts the relationships between all entities

Conceptual Data Model - Design Step 1

This is First step for DWH Designing.

Conceptual Data Model is the first step in Data Warehouse design. In conceptual data model, very high level relationships between dimension and fact table is depicted. Conceptual data model not necessarily includes keys, attributes of tables. Conceptual data model gives a very high level idea of proposed Data Warehouse design including possible fact and dimension table. Conceptual data model is the stepping stone to design logical data model of Data Warehouse.


Characteristics of Data Warehouse Conceptual Data Model

1. It shows only high level relationship between tables.
2. It does not show primary key or column names
3. It is the stepping stone of Logical Data Model

PLSQL Interview Questions

1) What is a Cursor and Cursor Types?

2) What is FOR UPDATE and WHERE CURRENT Clauses?

3) Differences between Procedure and Function?

4) Can we create Package Specification without package Body?

5) What is Forward Declaration in packages?

6) What are the packages you are using?

7) What are the main advantages of Packages?

8) What is the use NOCOPY?

9) Can we use OUT and INOUT Parameter in Functions?

10) What is BULK COLLECT and BULK BIND?

11) What are the cursors attributes?

12) What is save Exception?

13) What is Exception and types of Exceptions?

14) What is Raise_application_error?

15) What is PRAGMA EXCEPTION_INIT?

16) What is PRAGMA Autonomous transaction?

17) What is Global Temporary Table?

18) What are the types of Collections and Differences between all these?

19) What is Insead of Trigger?

20) What is Mutating Table Error?

21) How to debug a procedure?

22) What are the differences between BINARY_INTEGER and PLS_INTEGER?

23) Can we use TCL ( Commit, ROLLBAK) in Triggers?

24) What are the types of Triggers?

25) What are the types of System Triggers?

SQL * LOADER

1) What is SQL * Loader and why we are using SQL * LOADER?

2) What are the files we are using in SQL * Loader?

3) What is difference between Badfile, Logfile and Discard files.

4) How to Insert two tables using one data file?

5) What is FILLER and SKIP Option in SQL * LOADER?

6) What are the options available in SQL * LOADER?

7) How to insert data in one table using two data files?

8) SQL*Loader is auto commit or Not?

Oracle Tuning:

1) What are the tuning methodologies you are suing?

2) What is SQL TRACE and How to Enable SQL TRACE?

3) What is explain plan. how do you read it.

4) What are the things trace file contain?

5) Where we can find a trace file and what is the trace file extension?

6) What is TKPROF?

7) How to reduce a cost in Cost based tuning and what are the things you are following?

LINUX and Shell Scripting

1) How do you find out what’s your shell?

2) What’s the command to find out today’s date?

3) What’s the command to find out users on the system?

4) How do you find out the current directory you’re in?

5) How do you remove a file?

6) How do you find out your own username?

7) How do you search for a string inside a given file?

8) What are PIDs?

9) How do you list currently running process?

10) How do you stop a process?

11) How do you stop all the processes, except the shell window?

12) What’s the conditional statement in shell scripting?

13) How do you do number comparison in shell scripts?

14) How do you test for file properties in shell scripts?

15) How do you edit a file in shell scripts?

SQL Interview Questions

1) What is NVL and NVL2 Functions?

2) What is NULLIF and COALESCE Functions?

3) What is TRANSLATE and RELACE Functions?

4) What is SUBSTR and INSTR Functions?

5) What is CASE and DECODE?

6) What is ON DELETE CASCADE and ON DELETE SET NULL?

7) What is View and what is FORCE option in view?

8) What is Mview and what are the advantages of MVIEW.

9) Copy a structure of a table without records?

10) If two structure are same and one table in having data how to insert the data into empty
table?

11) How to display only duplicate rows?

12) How to display nth highest salary?

13) What are the sudo columns?

14) What is difference between IN and EXISTS?

15) Difference between Union and Union ALL.

16) What are the types of joins.

17) What is rownum and rowid.

18) When using index can be more expensive? types of indexes.