Introduction to RDBMS

Introduction

Data is meaningful information.
Database is a collection of relevant data.
DBMS means database management system.
DBMS provides the software to manage the database.
Following are the operations to be performed on database: insertion, deletion,
updation, sorting, searching, traversing, etc.

Following are the different types of DBMS:

FMS, Hierarchical, DBMS

FMS (File Management System)

It is simple to create but difficult to manage.
No relations are there (like 1-to-1, 1-to-many)

Hierarchical System

Here data is stored in tree like structure.
It exhibits / exists 1-to-many relationship only.

Network DBMS

It exhibits all relationships. It is quite fast to manage the data.
Limitation: As size increases, it becomes very complicated to manage.

About RDBMS

RDBMS

It exhibits all relationships. It is very easy to manage the data.
In RDBMS data is stored in tabular form.
Tables have rows & columns.
Rows represent records and column represents fields.
To access the data in RDBMS “SQL (Structured Query Language)” is used.

Limitations of RDBMS:

1. It is slow as compare to network RDBMS.
2. Redundancy of data to establish the relationship between tables.

Different types of RDBMS:

ORACLE, SQL Server, Sybase, DB2, MySQL, etc.

SQL queries are divided into three parts:

1. DDL: create, alter, rename, etc.
2. DML: insert, update, delete, truncate, etc.
3. Data retrieval: Select Queries.

What is database or database management systems (DBMS)? and – What’s the difference between file and database? Can files qualify as a database

Database provides a systematic and organized way of storing, managing and retrieving from collection of logically related information.

Secondly the information has to be persistent, that means even after the application is closed the information should be persisted.

Finally it should provide an independent way of accessing data and should not be dependent on the application to access the information.

Main difference between a simple file and database that database has independent way (SQL) of accessing information while simple files do not File meets the storing, managing and retrieving part of a database but not the independent way of accessing data. Many experienced programmers think that the main difference is that file can not provide multi-user capabilities which a DBMS provides. But if we look at some old COBOL and C programs where file where the only means of storing data, we can see functionalities like locking, multi-user etc provided very efficiently. So it’s a matter of debate if some interviewers think this as a main difference between files and database accept it… going in to debate is probably loosing a job.

What is normalization? What are different types of normalization

There is set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization.

Benefits of normalizing your database include:

Avoiding repetitive entries
Reducing required storage space
Preventing the need to restructure existing tables to accommodate new data.
Increased speed and flexibility of queries, sorts, and summaries.

Following are the three normal forms:

First Normal Form

For a table to be in first normal form, data must be broken up into the smallest un possible.In addition to breaking data up into the smallest meaningful values, tables first normal form should not contain repetitions groups of fields.

Second Normal form

The second normal form states that each field in a multiple field primary keytable must be directly related to the entire primary key. Or in other words,each non-key field should be a fact about all the fields in the primary key.

Third normal form

A non-key field should not depend on other Non-key field.

What is Like operator for and what are wild cards

LIKE operator is used to match patterns. A “%” sign is used to define the pattern.

Below SQL statement will return all words with letter “S”

SELECT * FROM pcdsEmployee WHERE EmpName LIKE ‘S%’

Below SQL statement will return all words which end with letter “S”

SELECT * FROM pcdsEmployee WHERE EmpName LIKE ‘%S’

Below SQL statement will return all words having letter “S” in between

SELECT * FROM pcdsEmployee WHERE EmpName LIKE ‘%S%’

“_” operator (we can read as “Underscore Operator”). “_” operator is the character defined at that point. In the below sample fired a query Select name from pcdsEmployee where name like ‘_s%’ So all name where second letter is “s” is returned.

Which will return: Peter and Amanda

If you want to find out the number of descendants for a node, all you need is the left_val and right_val of the node for which you want to find the descendants count.

The formula is

No. of descendants = (right_val – left_val -1) /2

So, for 6 -11 Amanda, (11 – 6 – 1) /2 = 2 descendants

for 1-12 Peter, (12 – 1 -1 ) / 2 = 5 descendants.

for 3-4 Mary, (4 -3 – 1) / 2 = 0, means it is a child and has no descendants.

The modified preorder traversal is a little more complicated to understand, but is very useful.

What are different types of joins in SQL

INNER JOIN

Inner join shows matches only when they exist in both tables. Example in the below SQL there are two tables Customers and Orders and the inner join in made on Customers.Customerid and Orders.Customerid. So this SQL will only give you result with customers who have orders. If the customer does not have order it will not display that record.

SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

LEFT OUTER JOIN

Left join will display all records in left table of the SQL statement. In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN (Is covered in the next section) if you switch the side of each table.

SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

RIGHT OUTER JOIN

Right join will display all records in right table of the SQL statement. In SQL below all orders with or without matching customer records will be displayed. Customer data for orders without customers appears as NULL values. For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table.

SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID

What is Snow Flake Schema design in database? What’s the difference between Star and Snow flake schema

Star schema is good when you do not have big tables in data warehousing. But when tables start becoming really huge it is better to denormalize. When you denormalize star schema it is nothing but snow flake design. For instance below customeraddress table is been normalized and is a child table of Customer table. Same holds true for Salesperson table.

What’s the difference between “UNION” and “UNION ALL

UNION SQL syntax is used to select information from two tables. But it selects only distinct records from both the table. , while UNION ALL selects all records from both the tables.

What are the two types of indexes and explain them in detail? Orwhat’s the difference between clustered and non-clustered indexes

There are basically two types of indexes:-

Clustered Indexes.
Non-Clustered Indexes.

In clustered index the non-leaf level actually points to the actual data.In Non-Clustered index the leaf nodes point to pointers (they are rowid’s) which then point to actual data.