1. What is a data model? What are the different types?
A data model is used to organize and describe the structure and the relationships of the data
within the system. So basically, there are three types of data models: logical (which adds details
and attributes and is independent of technology), physical (which reflects database-specific
structures like tables and indexes), and conceptual (which is high-level and concentrates on
entities and relationships). Each has a specific function in database design.
The Three Levels of Data Modeling
Data modeling doesn't happen all at once. Organizations typically build data models in three
distinct stages, moving from business ideas to actual code:
Conceptual Data Model (The "What"): A high-level, business-oriented view. It defines what data
the system will contain and the broad relationships between them. It’s used to get stakeholders
on the same page and doesn't worry about technical details.
Logical Data Model (The "How"): Adds more detail to the conceptual model. It defines the
specific attributes (like names, dates, or IDs) and the explicit relationships between data types. It
is still independent of any specific database technology.
Physical Data Model (The "Implementation"): The actual technical design. It describes how the
model will be implemented using a specific database management system (DBMS), including
table structures, column data types, primary keys, and indexes.
Different Types of Data Models
Over the decades, different types of data models have evolved to handle different kinds of data
and applications. Here are the most common types:
1. Relational Data Model
This is the most common model, powering traditional databases like MySQL, PostgreSQL, and
Oracle. Data is stored in fixed-format tables consisting of rows and columns. Tables are linked to
each other using "keys" (shared identifiers).
Best for: Highly structured data, financial transactions, and applications requiring strict data
integrity.
2. Hierarchical Data Model
One of the oldest models, it organizes data into a tree-like structure. Each record has a single
"parent" and can have multiple "children" (a 1-to-many relationship).
Best for: Describing file systems or organizational charts, though it is rigid and rarely used for
modern, complex databases.
3. Network Data Model
An evolution of the hierarchical model, the network model allows a "child" record to have
multiple "parents" (a many-to-many relationship). It forms a graph-like structure of
interconnected records.
Best for: Complex mapping, though it became less popular due to how difficult it is to modify or
query.
4. Object-Oriented Data Model
This model combines database capabilities with object-oriented programming languages (like
Java or C++). Data is represented as objects that bundle both the data (attributes) and the actions
that can be taken on it (methods).
Best for: Applications with complex data structures, like multimedia editing software or
engineering design systems.
5. NoSQL (Non-Relational) Data Models
As big data and the web grew, rigid tables couldn't always keep up. NoSQL models were born to
handle unstructured or rapidly changing data. There are four main types:
Document Model: Stores data as documents (often JSON format). Each document contains pairs
of keys and values. (Great for content management and e-commerce platforms)
Graph Model: Focuses on the relationships between data points. It uses "nodes" (entities) and
"edges" (the relationships between them). (Great for social networks, fraud detection, and
recommendation engines)
Key-Value Model: The simplest type, where every item is stored as a key and its corresponding
value. (Great for caching and session management)
Wide-Column Model: Stores data in columns instead of rows, allowing for massive scalability
across multiple servers. (Great for big data analytics)
What are normalization and denormalization? When and why would you use them?
Let's understand it this way: Normalization is a technique that organizes data to minimize
duplication, or you can say data redundancy. It helps to improve data integrity through the
division of data into multiple related tables. This process guarantees that the data remains
consistent and free of anomalies, making it suitable for environments. Let's try to understand this
with an example of normalization: storing customer data once and having orders reference that
one instance of the customer to eliminate duplicates.
On the other hand, denormalization is the process of combining tables, and generally, it
combines tables to enhance query performance. When using denormalization, we would like to
keep related data together to maintain faster read operations, thus avoiding complex joins in the
modified table. analytics), lization is often used for read-heavy systems (such as reporting
systems or analytics), and it often introduces inconsistencies (such as when customer detail may
show up on every order record to speed raising a query).
Use normalization when data accuracy and efficiency of updates are most important. Use
denormalization when timeliness of read access and query performance is more important than
storage space and redundancy of data. Balancing normalization and denormalization depends on
the type of workload the application has and is performance driven.
What is a surrogate key? How is it different from a natural key?
- A surrogate key is a unique ID created by the database that has no real data meanings. For
instance, a customer may be assigned customer_id 101, 102, etc. to make it easier to identify
records.
- A natural key is a key that is made from real data that already exists, and the key uniquely
identifies a record, such as a social security number or an email address.
Difference:
- Surrogate keys are system-generated, simple, and stable.
- Natural keys come from real data and carry business meaning.
Why use surrogate keys?
- When natural keys are complex, changeable, or not unique.
- They improve database performance and simplify relationships.
What is a surrogate key? How is it different from a natural key?
- A surrogate key is a unique ID created by the database that has no real data meanings.
For instance, a customer may be assigned customer_id 101, 102, etc. To make it easier to
identify records.
- A natural key is a key that is made from real data that already exists, and the key
uniquely identifies a record, such as a social security number or an email address.
Difference:
- Surrogate keys are system-generated, simple, and stable.
- Natural keys come from real data and carry business meaning.
Why use surrogate keys?
- When natural keys are complex, changeable, or not unique.
- They improve database performance and simplify relationships.
Explain What are star schema and snowflake schema in a simple language for interview
with real time example
⭐ Star Schema
- Definition: A star schema is a way of organizing data in a data warehouse where you have one
central fact table (the “star’s center”) and multiple dimension tables (the “star’s points”)
connected directly to it.
- Structure: Fact table contains measurable data (like sales amount, quantity). Dimension tables
contain descriptive attributes (like customer details, product info, time).
- Example: Imagine a retail store:
- Fact Table: Sales (with columns like SaleID, DateID, ProductID, CustomerID, Amount).
- Dimension Tables:
- Product (ProductID, ProductName, Category)
- Customer (Customer_ID, Name, Age, Location)
- Date (Date_ID, Day, Month, Year)
- All dimensions connect directly to the Sales fact table, forming a star shape.
👉 Real-time analogy: Think of the Sales counter in a mall as the center. Every sale is recorded
there, and details about product, customer, and date are fetched directly from their respective
tables.
❄️Snowflake Schema
- Definition: A snowflake schema is like a star schema but more normalized. Dimension tables
are broken down into sub-dimensions, creating a structure that looks like a snowflake.
- Structure: Fact table still at the center, but dimension tables are split into smaller related tables.
- Example: Same retail store, but more detailed:
- Fact Table: Sales (SaleID, DateID, ProductID, CustomerID, Amount).
- Dimension Tables:
- Product → split into Product table and Category table
- Product (ProductID, ProductName, Category_ID)
- Category (CategoryID, CategoryName)
- Customer → split into Customer and Location
- Customer (CustomerID, Name, Age, LocationID)
- Location (Location_ID, City, State, Country)
- Date → split into Date and Month
- Date (DateID, Day, MonthID)
- Month (MonthID, MonthName, Year)
💡 Interview Tip:
If asked in an interview, you can say:
- “Star schema is denormalized, simple, and fast for querying — best for reporting.
Snowflake schema is normalized, reduces redundancy, but queries are slower — best
when storage optimization is important.”
Explain What are the four types of data models in a simple language for interview
with a real time example
. Hierarchical Data Model
- Definition: Data is organized in a tree-like structure with parent-child
relationships.
- Example: Think of a company’s organizational chart:
- CEO → Managers → Employees
- Real-time analogy: A folder structure on your computer (Folder → Subfolder →
Files).
2. Network Data Model
- Definition: Data is organized as a graph with nodes and connections. Each record
can have multiple parent and child records.
- Example: A university database:
- A student can enroll in multiple courses, and each course can have multiple
students.
- Real-time analogy: Social media connections (one person can be connected to
many, and those many can connect back).
3. Relational Data Model
- Definition: Data is stored in tables (rows and columns), and relationships are
established using keys.
- Example: An online shopping system:
- Customer table, Orders table, Product table — linked by Ids.
- Real-time analogy: Excel spreadsheets where different sheets are linked by a
common column.
4. Entity-Relationship (ER) Data Model
- Definition: Data is represented as entities (things), attributes (details about things),
and relationships (connections between things).
- Example: A hospital system:
- Entities: Doctor, Patient, Appointment
- Relationships: Doctor “treats” Patient, Patient “books” Appointment
- Real-time analogy: A diagram showing how people, places, and events are
connected.
I have a procedure it has 1000 linea of code. I want to find the error line number.
How to find it in oracle plsql?
“In Oracle PL/SQL, compilation errors can be checked using SHOW ERRORS or
querying USER_ERRORS. For runtime errors, I use
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE inside exception handling to get
the exact line number of failure.
BEGIN
My_proc;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error at line: ‘ ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
Example:
CREATE OR REPLACE PROCEDURE update_salary(p_emp_id NUMBER, p_amount
NUMBER) IS
V_salary NUMBER;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id = p_emp_id;
DBMS_OUTPUT.PUT_LINE(‘Before update: ‘ || v_salary);
UPDATE employees
SET salary = salary + p_amount
WHERE employee_id = p_emp_id;
DBMS_OUTPUT.PUT_LINE(‘After update: ‘ || (v_salary + p_amount));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Employee not found!’);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error: ‘ || SQLCODE || ‘ – ‘ || SQLERRM);
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
Explain chunking of data in plsql with real time example
📊 Chunking of Data in PL/SQL
Chunking in PL/SQL refers to breaking large sets of data into smaller manageable
pieces (chunks) and processing them iteratively. This approach is especially useful
when dealing with millions of rows, as handling them all at once can cause memory
issues, performance bottlenecks, or exceed limits like ORA-01555: Snapshot Too
Old.
⚙️Why Chunking is Needed
- Performance optimization: Avoids overloading memory by processing smaller sets.
- Error prevention: Reduces risks of rollback segment errors.
- Parallel processing: Enables dividing work across multiple sessions.
- Maintainability: Easier debugging and monitoring.
🖥️Real-Time Example: Processing Employee Records
Imagine you have a table EMPLOYEES with 10 million rows, and you need to
update salaries by 10%. Doing this in one go could be inefficient. Instead, you chunk
the data.
`plsql
DECLARE
CURSOR emp_cur IS
SELECT employee_id, salary
FROM employees;
TYPE emptab IS TABLE OF empcur%ROWTYPE;
Lemps emptab;
Vlimit CONSTANT PLSINTEGER := 1000; -- chunk size
BEGIN
OPEN emp_cur;
LOOP
FETCH empcur BULK COLLECT INTO lemps LIMIT v_limit;
FORALL i IN 1..l_emps.COUNT
UPDATE employees
SET salary = l_emps(i).salary * 1.10
WHERE employeeid = lemps(i).employee_id;
COMMIT; -- commit after each chunk
EXIT WHEN [Link] < vlimit;
END LOOP;
CLOSE emp_cur;
END;
`
🔎 Explanation of the Example
- BULK COLLECT: Fetches multiple rows at once into a collection.
- LIMIT clause: Controls chunk size (here, 1000 rows).
- FORALL statement: Efficiently applies DML operations to collections.
- Commit per chunk: Ensures changes are saved progressively, reducing rollback
segment usage.
🚀 Real-Time Use Cases
- Data migration: Moving millions of records between tables.
- Batch updates: Applying transformations to large datasets.
- ETL processes: Extracting and loading data in manageable pieces.
- Archiving: Moving old records to history tables chunk by chunk.
Explain instead of trigger with DML trigger with real time example
INSTEAD OF Trigger vs DML Trigger in PL/SQL
Triggers in Oracle PL/SQL are special procedures that automatically execute in response to
certain events on a table or view. Two important types are INSTEAD OF triggers and DML
triggers.
⚙️DML Trigger
Fires automatically when a DML operation (INSERT, UPDATE, DELETE) occurs on a table.
Used to enforce business rules, audit changes, or modify data before/after the DML
executes.
Example: Auditing Salary Changes
CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_audit(emp_id, old_salary, new_salary, change_date)
VALUES(:OLD.employee_id, :[Link], :[Link], SYSDATE);
END;
INSTEAD OF Trigger
Defined on views, not [Link] when you want to perform DML operations on a non-
updatable [Link] executes the trigger instead of the DML statement on the
[Link]: Updating Through a View Suppose you have a view joining two tables:
CREATE VIEW emp_dept_view AS
SELECT e.employee_id, [Link], d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
This view is not directly updatable because it joins multiple tables. To allow updates:
CREATE OR REPLACE TRIGGER trg_emp_dept_update
INSTEAD OF UPDATE ON emp_dept_view
FOR EACH ROW
BEGIN
UPDATE employees
SET name = :[Link]
WHERE employee_id = :OLD.employee_id;
UPDATE departments
SET department_name = :NEW.department_name
WHERE department_name = :OLD.department_name;
END;
Now, if you run:
UPDATE emp_dept_view
SET name = ‘John Doe’, department_name = ‘Finance’
WHERE employee_id = 101;
Oracle will execute the trigger logic, updating both employees and departments tables.
Real-Time Use Cases
DML Trigger: Audit trail for salary changes, prevent deletion of critical records, enforce
business rules.
INSTEAD OF Trigger: Allow updates on reporting views, synchronize data across
multiple tables via a single view.
Explain exception handling in plsql. Types of exception with real time example
Exception Handling in PL/SQLException handling in PL/SQL allows you to gracefully
manage errors that occur during program execution. Instead of letting the program crash,
you can catch the error, handle it, and continue or exit cleanly.
1. Predefined Exception
DECLARE
V_salary [Link]%TYPE;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id = 9999; -- invalid ID
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Employee not found!’);
END;
If no record exists, Oracle raises NO_DATA_FOUND
2. Non-Predefined Exception
DECLARE
E_invalid_number EXCEPTION;
PRAGMA EXCEPTION_INIT(e_invalid_number, -01722); -- ORA-01722: invalid
number
BEGIN
INSERT INTO employees(employee_id, name, salary)
VALUES(101, ‘John’, ‘ABC’); -- invalid salary
EXCEPTION
WHEN e_invalid_number THEN
DBMS_OUTPUT.PUT_LINE(‘Invalid number entered for salary!’);
END;
3. User-Defined Exception
DECLARE
E_low_salary EXCEPTION;
V_salary NUMBER := 2000;
BEGIN
IF v_salary < 3000 THEN
RAISE e_low_salary;
END IF;
EXCEPTION
WHEN e_low_salary THEN
DBMS_OUTPUT.PUT_LINE(‘Salary below company standards!’);
END;
Write a sql plsql query to find the manager hierarchy for a specific employee, showing
all levels up to the CEO.
SELECT employee_id,
Employee_name,
Manager_id,
LEVEL AS hierarchy_level
FROM employees
START WITH employee_id = 101 -- your chosen employee
CONNECT BY PRIOR manager_id = employee_id;
How to Explain It
START WITH → defines the starting point (the employee whose hierarchy you want).
CONNECT BY → tells Oracle how to walk the hierarchy (from employee to manager).
PRIOR → ensures the relationship is followed correctly (manager of employee).
LEVEL → shows the depth in the hierarchy (1 = employee, 2 = manager, 3 = manager’s
manager, etc.).