Normalization

Normalization is the process of splitting relations into well-structured relations that allow users to insert, delete, and update tuples without introducing database inconsistencies. The focus of normalization is to reduce redundant data to the minimum.  Normalization is also called “Bottom-up approach”, because this technique requires very minute details like every participating attribute and how it is dependant on the key attributes, is crucial. If you add new attributes after normalization, it may change the normal form itself.

Through Normalization, the collection of data in a single table is distributed into multiple tables with specific relation. Without normalization many problems can occur when trying to load an integrated conceptual model into the DBMS. These problems arise from relations that are generated directly from user views are called anomalies. There are three types of anomalies:

RollNo	StudentName	CourseNo	CourseName	Instructor
120	SKL	CS-75	DBMS	SB
89	KBC	CS-75	DBMS	SB
25	ABC	CS-75	DBMS	SB
48	XYZ	CS-30	CA	MC
57	TKC	CS-80	OS	JB
120	SKL	CS-80	OS	JB
68		CS-90	FLAT	LD

Update anomaly: An update anomaly is a data inconsistency that results from data redundancy and a partial update. For example, if there is a change in the name of the instructor for CS-75, we need to make change for all the rows. If we forget to update a single row the database will show two instructor names for the Course CS-75.

Deletion anomaly:  This means loss of useful information. In some cases it may occur that some useful data is lost. For example, consider the row for RollNo 48. XYZ is the only student who has opted for CS-30. If XYZ leaves the institute and the data is deleted for XYZ, the associated data for Course will also be deleted.

Insertion anomaly: An insertion anomaly is the inability to add data to the database due to absence of other data. For example, assume that the above table is defined so that null values are not allowed. If a course is added but not immediately students opt for the course, then this course could not be entered into the database (68). This results in database inconsistencies due to omission.

In database tables are normalized for the following reasons:

To allow data retrieval at an optimal speed

Data maintenance through update, insertion and deletion.

To reduce the need to restructure tables for new applications

A functional dependency is a constraint between two sets of attributes in a relation from a database. It occurs when an attribute in a relation uniquely determines another attribute.

In a given relation R, X and Y are attributes. Attribute Y is functionally dependent on attribute X if each value of X determines EXACTLY ONE value of Y, which is represented as X → Y (X can be composite in nature).

We say here “X determines Y” or “y is functionally dependent on x”.

 X→Y does not imply Y→X.

If the value of an attribute “Marks” is known then the value of an attribute “Grade” is determined since Marks→ Grade

RollNo	StudentName	Marks	Grade
59	ABC	90	A
96	XYZ	70	B

Types of functional dependencies:

1.       Full Functional dependency: X and Y are attributes. X Functionally determines Y (Note: Subset of X should not functionally determine Y)

2.       Partial Functional dependency:  X and Y are attributes. Attribute Y is partially dependent on the attribute X only if it is dependent on a sub-set of attribute X.

3.       Transitive dependency: X Y and Z are three attributes. X -> Y, Y-> Z => X -> Z

Now consider the following Relation

REPORT (STUDENT#, COURSE#, CourseName, IName, Room#, Marks, Grade)

• STUDENT# - Student Number

• COURSE# - Course Number

• CourseName - Course Name

• IName - Name of the Instructor who delivered the course

• Room# - Room number which is assigned to respective Instructor

• Marks - Scored in Course COURSE# by Student STUDENT#

• Grade - obtained by Student STUDENT# in Course COURSE#

The Functional Dependencies are:

• STUDENT# COURSE# → Marks

• COURSE# → CourseName,

• COURSE# → IName (Assuming one course is taught by one and only one

Instructor)

• IName → Room# (Assuming each Instructor has his/her own and non-shared room)

• Marks → Grade

           In above example Marks is fully functionally dependent on STUDENT# COURSE# and not on subset of STUDENT# COURSE#. This means Marks cannot be determined either by STUDENT# OR COURSE# alone. It can be determined only using STUDENT# AND COURSE# together. Hence Marks is fully functionally dependent on STUDENT# COURSE#. CourseName is not fully functionally dependent on STUDENT# COURSE# because subset of STUDENT# COURSE# i.e only COURSE# determines the CourseName and STUDENT# does not have any role in deciding CourseName. Hence CourseName is not fully functionally dependent on STUDENT# COURSE#.

Now CourseName, IName, Room# are partially dependent on composite attributes STUDENT# COURSE# because COURSE# alone defines the CourseName, IName, Room#.

Again, Room# depends on IName and in turn IName depends on COURSE#. Hence Room# transitively depends on COURSE#. Similarly Grade depends on Marks, in turn Marks depends on STUDENT# COURSE# hence Grade depends Fully transitively on STUDENT# COURSE#.

Now consider this table:

Student Data		Course Details				Result
STUDENT#	StudentName	COURSE#	CourseName	IName	Room#	Marks	Grade

First normal form (1NF)

A relation schema is in 1NF if:

·         If and only if all the attributes of the relation R are atomic in nature.

·         Atomic: the smallest level to which data may be broken down and remain meaningful.

In relational database design it is not practically possible to have a table which is not in 1NF.

STUDENT#	StudentName	COURSE#	CourseName	IName	Room#	Marks	Grade

Second normal form (2NF)

A Relation is said to be in Second Normal Form if and only if:

·         It is in the First normal form, and

·         No partial dependency exists between non-key attributes and key attributes.

An attribute of a relation R that belongs to any key of R is said to be a prime attribute and that which doesn’t is a non-prime attribute To make a table 2NF compliant, we have to remove all the partial dependencies

Note: - All partial dependencies are eliminated

STUDENT#	StudentName

COURSE#	CourseName	IName	Room#

STUDENT#	COURSE#	Marks	Grade

Third normal form (3 NF)

A relation R is said to be in the Third Normal Form (3NF) if and only if:

·         It is in 2NF and

·         No transitive dependency exists between non-key attributes andkey attributes.

• STUDENT# and COURSE# are the key attributes.

• All other attributes, except grade are nonpartially, non-transitively dependent on key attributes.

STUDENT#	StudentName

COURSE#	CourseName	IName	Room#

STUDENT#	COURSE#	Marks

Marks (Lower Bound)	Marks (Upper Bound)	Grade

Boyce-Codd Normal form (BCNF)

A relation is said to be in Boyce Codd Normal Form (BCNF)

·         If and only if all the determinants are candidate keys.

BCNF relation is a strong 3NF, but not every 3NF relation is BCNF.

Consider the table:

Student#	EmailID	Course#	Marks
ABC	abc@sumitkar.in	CS-75	98

Candidate Keys for the relation are [C#, S#] and [C#, Email]. Since Course # is overlapping, it is referred as Overlapping Candidate Key. Valid Functional Dependencies are:

S#  → EmailID

EmailID → S#

S#, C# → Marks

C#, EmailID →Marks

Student#	EmailID
ABC	abc@sumitkar.in

Student#	Course#	Marks
ABC	CS-75	98

Read More

Advantages of DBMS over Conventional File System

The principal advantages of DBMS over file processing system:

• Flexibility: Because programs and data are independent, programs do not have to be modified when types of unrelated data are added to or deleted from the database, or when physical storage changes.

• Fast response to information requests: Because data is integrated into a single database, complex requests can be handled much more rapidly than locating data separately. In many businesses, faster response means better customer service.

• Multiple access: Database software allows data to be accessed in a variety of ways (through various key fields), by using several programming languages (both3GL and nonprocedural4GL programs).

• Lower user training costs: Users often find it easier to learn such systems and training costs may be reduced. Also, the total time taken to process requests may be less, which would increase user productivity.

• Less storage: Theoretically, all occurrences of data items need be stored only once, thereby eliminating the storage of redundant data. System developers and database designers often use data normalization to minimize data redundancy.

Here are some disadvantages:

• DBMS subjects business to risk of critical data loss in its electronic format and can be more readily stolen without proper security.

• The cost of a DBMS can be prohibitive for small enterprises as they struggle with cost justification for making investment in the infrastructure.

• Improper use of the DBMS can lead to incorrect decision making as people take presented data for granted as accurate.

• Data can be stolen by weak password security policy.

Read More

SQL Syntax

Some of the most important SQL commands are listed below. These below SQL Syntaxes will be suitable for quick reference.



SELECT [hint][DISTINCT] select_list

FROM table_list



[WHERE conditions]

[GROUP BY group_by_list]

[HAVING search_conditions]



[ORDER BY order_list [ASC DESC] ]

[FOR UPDATE for_update_options]





SQL Select Statement



SELECT "column name" FROM "table name"



Example:

Select salary from emp;



SQL Where



SELECT "column name"

FROM "table name"

WHERE "condition"



Example:

Select salary from emp

Where salary > 2000



SQL Distinct



SELECT DISTINCT "column name"

FROM "table name"



Example:

Select DISTINCT name from emp;



SQL And/Or



SELECT "column name"

FROM "table name"

WHERE "condition"

{[ANDOR] "condition"}+



Example:

SELECT salary

FROM emp

WHERE Salary > 1000

OR (Salary <> 275)



SQL Between



SELECT "column name"

FROM "table name"

WHERE "column name" BETWEEN 'value1' AND 'value2'



Example:

SELECT *

FROM emp

WHERE Date BETWEEN 'Jan-01-1999' AND 'Jan-15-1999'



SQL In



SELECT "column name"

FROM "table name"

WHERE "column name" IN ('value1', ‘value2’ ...)



Example:

SELECT *

FROM emp

WHERE last_name IN ('sharma', 'dhall')



SQL Like



SELECT "column name"

FROM "table name"

WHERE "column name" LIKE {PATTERN}



Example:

SELECT *

FROM emp

WHERE last_name LIKE '%EN%'



SQL Order By



SELECT "column name"

FROM "table name"

[WHERE "condition"]

ORDER BY "column name" [ASC, DESC]



Example:

SELECT name, Salary

FROM emp

ORDER BY name DESC



SQL Count



SELECT COUNT ("column name")

FROM "table name"



Example:

SELECT COUNT (salary)

FROM emp



SELECT * FROM Table;

In the above query, SELECT and FROM are SQL keywords, * is a wildcard which means “all columns”



Commonly available SQL keywords related to SELECT include:

1. FROM is used to indicate from which tables the data is to be taken, as well as how the tables JOIN to each other.


2. WHERE is used to identify which rows to be retrieved, or applied to GROUP BY. WHERE is evaluated before the GROUP BY.


3. GROUP BY is used to combine rows with related values into elements of a smaller set of rows.


4. HAVING is used to identify which of the "combined rows" (combined rows are produced when the query has a GROUP BY keyword or when the SELECT part contains aggregates), are to be retrieved. HAVING acts much like a WHERE, but it operates on the results of the GROUP BY and hence can use aggregate functions.


5. ORDER BY is used to identify which columns are used to sort the resulting data.

Returning a Single Column from a table

The syntax is as follows:

SELECT Column FROM Table;

Oracle uses the ANSI standard concatenation operator, . Because this operator is reserved for string concatenation, the operands don't need to be cast to a string type—they'll be converted automatically:

SELECT FirstName ' ' LastName AS Name FROM EMP_Database;

Sorting with the ORDER BY Clause

The ORDER BY clause is always applied after all other clauses are applied, such as the WHERE and GROUP BY clauses. Without an ORDER BY clause in an SQL statement, rows will often be retrieved in the physical order in which they were added to the table. The default behavior is to sort rows in ascending order.

SELECT ColumnA, ColumnB FROM Table

ORDER BY ColumnA ASC;

Although this is the default behavior so you don't need to explicitly specify the ASC keyword, it's a better idea to include it to ensure that your queries are as easy to read as possible.

Also, you may want to sort your rows in descending order, in which case you use the DESC keyword:

SELECT ColumnA, ColumnB FROM Table

ORDER BY ColumnA DESC;

Filtering Data

DISTINCT always retrieves the first value from a repeating group. If there are multiple repeating groups DISTINCT will retrieve the first row from each group.

Hence, DISTINCT will always require a sort. DISTINCT can operate on a single or multiple columns.

SELECT DISTINCT ColumnA FROM Table;

In order to filter out duplicate rows, you use the DISTINCT keyword.

If you want to retrieve multiple columns, you can guarantee that every row you obtain contains unique data for the specified set of columns.

For example, the following query will return only unique combinations of customer names and debit card details:

SELECT DISTINCT CustomerName, DebitCard FROM Customers;

This doesn't mean that you won't have duplicates in either column, only that each combination is unique. You could, for example, have several customers with the same name but with different debit card numbers.

Using WHERE Clause

We can use the WHERE clause to restrict the rows returned by a query.

SELECT ColumnA, ColumnB, ColumnC FROM Table

WHERE Condition;

Condition is very flexible, allowing you to test for equalities and inequalities in column data, ranges of values to look for etc. You achieve all this using a simple syntax that includes various operators and keywords that, when combined, allow you to search for pretty much anything.

We can use following comparison operators with the WHERE clause:

Operator	Meaning
=	Equal
<>, !=, ^=	Not Equal
>	Greater Than
<	Less Than
>=	Greater Than Or Equal
<=	Less Than Or Equal

And many more for example BETWEEN ...AND..., LIKE, IS NULL etc.

Some examples of where

1)

SELECT name FROM bedroom

WHERE bedcolor = 'BLACK'

OR ceilingcolor = 'GREEN'

OR wallcolor = 'YELLOW'

2)

SELECT z

FROM t

WHERE x = 6 AND y > 7 ;

3)

Find the average price of Bud.

Sells (bar, beer, price)

SELECT AVG (price)

FROM Sells

WHERE beer = 'Bud';

Read More

SQL Introduction

SQL stands for Structured Query Language and it is generally referred to as SEQUEL. SQL is simple language to learn. SQL is a Nonprocedural language, as compared to the procedural or third generation languages (3GLs) such as COBOL and C. SQL was developed by IBM in the 1970s.

The American National Standards Institute (ANSI) published its first SQL standard in 1986 and a second widely adopted standard in 1989. ANSI released updates in 1992, known as SQL92 and SQL2, and again in 1999, termed both SQL99 and SQL3. Each time, ANSI added new features and incorporated new commands and capabilities into the language.

SQL is a simple, yet powerful, language used to create, access, and manipulate data and structure in the database.

SQL Statements categories: 

DDL - Data Definition Language.

DDL is used to define, alter, or drop database objects and their privileges. DDL statements will implicitly perform a commit.

DDL Statements:

Create

It is used to create objects(tables, views) in the database.

Alter

It is used to alter the structure of the database objects.

Drop

delete database objects (It will invalidate the dependent objects ,it also drops indexes, triggers and referential integrity constraints ).

Truncate

remove all records from a table, including all spaces allocated for the records are removed (It is fast as compared to Delete and does not generate undo information as Delete does. It performs an implicit commit as it is a DDL. It resets the high water mark.)

Grant

assigning privileges

DML - Data Manipulation Language.

DML is used to access, create, modify or delete data in the structures of the database.

DML Statements:

Select

Select data from the database

Insert

It is used to insert data into a table

Update

It is used to update existing data within a table

Delete

It removes rows from the table.

DCL - Data Control Language

Following are the examples of Data control Statements.

DCL Statements:

Commit

It will end the current transaction making the changes permanent and visible to all users..

Savepoint

It will identify a point(named SAVEPOINT) in a transaction to which you can later roll back

Rollback

It will undo all the changes made by the current transaction.

Set- Transaction

It is used to define the properties of a transaction.

Read More

MCQ on Oracle

1. What does a
COMMIT statement do to a CURSOR

a]
Open the Cursor

b]
Fetch the Cursor

c]
Close the Cursor

d] None of the
above

 

2. Which of the
following is TRUE

1]
Host variables are declared anywhere in the program

2]
Host variables are declared in the DECLARE section

a]
Only 1 is TRUE

b] Only 2 is
TRUE

c]
Both 1 & 2are TRUE

d]
Both are FALSE

 

3. Which of the
following is NOT VALID is PL/SQL

a]
Bool boolean;

b] NUM1, NUM2
number;

c]
deptname dept.dname%type;

d]
date1 date := sysdate

 

4. Declare

fvar number :=
null; svar number := 5

Begin

goto <<
fproc>>

if fvar is null
then

<<
fproc>>

svar := svar + 5

end if;

End;

What will be the
value of svar after the execution ?

a] Error

b]
10

c]
5

d]
None of the above

 

5. Which of the
following is not correct about an Exception ?

a]
Raised automatically / Explicitly in response to an ORACLE_ERROR

b]
An exception will be raised when an error occurs in that block

c] Process
terminates after completion of error sequence.

d]
A Procedure or Sequence of statements may be processed.

 

6. Which of the
following is not correct about User_Defined Exceptions ?

a]
Must be declared

b]
Must be raised explicitly

c] Raised
automatically in response to an Oracle error

d]
None of the above

 

7. A Stored
Procedure is a

a]
Sequence of SQL or PL/SQL statements to perform specific function

b]
Stored in compiled form in the database

c]
Can be called from all client environments

d] All of the
above

    

8. Which of the
following statement is false

a]
Any procedure can raise an error and return an user message and error number

b]
Error number ranging from 20000 to 20999 are reserved for user defined messages

c] Oracle checks
Uniqueness of User defined errors

d]
Raise_Application_error is used for raising an user defined error.

 

9.  Is it possible to open a cursor which is in a
Package in another procedure ?

a] Yes

b]
No

 

10.  Is it possible to use Transactional control
statements in Database Triggers ?

a]
Yes

b] No

11.  Is it possible to Enable or Disable a Database
trigger ?

a] Yes

b]
No 

 

12. PL/SQL
supports datatype(s)

a]
Scalar datatype

b]
Composite datatype

c] All of the
above

d]
None of the above

13. Find the ODD
data type out

a]
VARCHAR2

b] RECORD

c]
BOOLEAN

d]
RAW

14. Which of the
following is not correct about the "TABLE" data type ?

a] Can contain
any no of columns

b]
Simulates a One-dimensional array of unlimited size

c]
Column datatype of any Scalar type

d]
None of the above

    

15. Find the ODD
one out of the following

a]
OPEN

b]
CLOSE

c] INSERT

d]
FETCH

    

16. Which of the
following is not correct about Cursor ?

a]
Cursor is a named Private SQL area

b] Cursor holds
temporary results

c]
Cursor is used for retrieving multiple rows

d]
SQL uses implicit Cursors to retrieve rows

 

 

17. Which of the
following is NOT VALID in PL/SQL ?

a]
Select ... into

b]
Update

c] Create

d]
Delete

    

18. What is the
Result of the following 'VIK'||NULL||'RAM' ?

a]
Error

b]
VIK RAM

c] VIKRAM

d]
NULL

 

19. Declare

a number := 5; b
number := null; c number := 10;

Begin

if a > b AND
a < c then

a := c * a;

end if;

End;

What will be the
value of 'a' after execution ?

a]
50

b]
NULL

c] 5

d]
None of the above

 

20. Does the
Database trigger will fire when the table is TRUNCATED ?

a]
Yes

b] No

 

21.
SUBSTR(SQUARE ANS ALWAYS WORK HARD,14,6) will return

a]
ALWAY

b}
S ALWA

c] ALWAYS

 

22.
REPLACE('JACK AND JUE','J','BL') will return

a]
JACK AND BLUE

b]
BLACK AND JACK

c] BLACK AND
BLUE

d]
None of the above

 

23. GET_BLOCK
property is a

a]
Restricted procedure

b]
Unrestricted procedure

c]
Library function

d]
None of the above

24.
TRANSLATE('333SQD234','0123456789ABCDPQRST','0123456789') will return

a] 333234

b]
333333

c]
234333

d]
None of the above

 

25.. EMPNO ENAME
SAL

A822 RAMASWAMY
3500

A812 NARAYAN
5000

A973 UMESH 2850

A500 BALAJI 5750

Use
these data for the following Questions

Select
SAL from EMP E1 where 3 > ( Select count(*) from Emp E2

where
E1.SAL > E2.SAL ) will retrieve

a]
3500,5000,2500

b]
5000,2850

c]
2850,5750

d]
5000,5750

26. Is it
possible to modify a Data type of a column when column contains data ?

a]
Yes

b] No

 

27. Which of the
following is not correct about a View ?

a]
To protect some of the columns of a table from other users

b] Occupies data
storage space

c]
To hide complexity of a query

d]
To hide complexity of a calculations

 

28. Which is not
part of the Data Definition Language ?

a]
CREATE

b]
ALTER

c] ALTER SESSION

    

29. The Data
Manipulation Language statements are

a]
INSERT

b]
UPDATE

c]
SELECT

d] All of the
above

  

30. EMPNO ENAME
SAL

A822 RAMASWAMY
3500

A812 NARAYAN
5000

A973 UMESH

A500 BALAJI 5750

Using
the above data Select count(sal) from Emp will retrieve

a]
1

b]
0

c] 3

d]
None of the above

31. If an UNIQUE
KEY constraint on DATE column is created, will it accept the rows that are
inserted with SYSDATE ?

a]
Will

b] Won't

 

32. What are the
different events in Triggers ?

a]
Define, Create

b]
Drop, Comment

c] Insert,
Update, Delete

d]
All of the above

33. What
built-in subprogram is used to manipulate images in image items ?

a]
Zoom_out

b]
Zoom_in'

c] Image_zoom

d]
Zoom_image

 

34. Can we pass
RECORD GROUP between FORMS ?

a] Yes

b]
No

    

35. SHOW_ALERT
function returns

a]
Boolean

b] Number

c]
Character

d]
None of the above

 

36. What SYSTEM
VARIABLE is used to refer DATABASE TIME ?

a] $$dbtime$$

b]
$$time$$

c]
$$datetime$$

d]
None of the above

  

37.
:SYSTEM.EFFECTIVE.DATE varaible is

a]
Read only

b]
Read & Write

c] Write only

d]
None of the above

 

38.. How can you
CALL Reports from Forms4.0 ?

a]
Run_Report built_in

b]
Call_Report built_in

c] Run_Product
built_in

d]
Call_Product built_in

 

39. When do you
get a .PLL extension ?

a] Save Library
file

b]
Generate Library file

c]
Run Library file

d]
None of the above

    

40. What is
built_in Subprogram ?

a]
Stored procedure & Function

b]
Collection of Subprogram

c]
Collection of Packages

d] None of the
above 

41. Out of the
below data types which can be used for Date & time type?

a)     Date

b)     Datetimeoffset

c)     Time

d)     All

42. What is the
ISO synonym for ntext data type?

a)     New text

b)     Next text

c)     National Text

d)     None of the above

43. Which data
type is used to store binary data up to the limit of 2,147,483,647?

a)     Image

b)     Text

c)     Ntext

d)     None of the above

44. Which data
type can be used to represent position in a hierarchy?

a.     Hierarchy

b.     Hierarchyid

c.     Nhierarchyid

d.     None of the above

45. What is the
storage size of smallmoney data type?

a)     3 bytes

b)     2 bytes

c)     4 bytes

d)     8 bytes

46.  What is the range of TinyInt data type in SQL
Server?

a)     0-10

b)     0-255

c)     -2^15 to 2^15-1

d)     None of the above

47. Which data
type in SQL server can be used to store time in the table?

a)     Timeonly

b)     OnlyTime

c)     Time

d)     DateTime

48. What is the
ISO synonym for real data type in SQL server?

a)     Float(24)

b)     Float(8)

c)     Float(16)

d)     None of the above

49. What is the
default size of n in a column which is defined as VARCHAR(n)?

a)     NULL

b)     0

c)     1

d)     None of the above

50. Which data
type is used for assigning GUID value?

a)     UNIQUE

b)     UNIQUEIDENTYFIER

c)     GUID

d)     None of the above

Read More