UNIT -2

Functional Dependency
The functional dependency is a relationship that exists between two
attributes. It typically exists between the primary key and non-key
attribute within a table.

1. X → Y

The left side of FD is known as a determinant, the right side of the

production is known as a dependent.

For example:

Assume we have an employee table with attributes: Emp_Id, Emp_Name,

Emp_Address.

Here Emp_Id attribute can uniquely identify the Emp_Name attribute of

employee table because if we know the Emp_Id, we can tell that employee
name associated with it.

Functional dependency can be written as:

1. Emp_Id → Emp_Name

We can say that Emp_Name is functionally dependent on Emp_Id.

Types of Functional dependency

 1. Trivial functional dependency
o A → B has trivial functional dependency if B is a subset of A.
o The following dependencies are also trivial like: A → A, B → B

Example:

1. Consider a table with two columns Employee_Id and Employee_Name.

2. {Employee_id, Employee_Name} → Employee_Id is a trivial functional depend
ency as
3. Employee_Id is a subset of {Employee_Id, Employee_Name}.
4. Also, Employee_Id → Employee_Id and Employee_Name → Employee_Name ar
e trivial dependencies too.

2. Non-trivial functional dependency

o A → B has a non-trivial functional dependency if B is not a subset of A.
o When A intersection B is NULL, then A → B is called as complete non-
trivial.

Example:

1. ID → Name,
2. Name → DOB

SQL
o SQL stands for Structured Query Language. It is used for storing and
managing data in relational database management system (RDMS).
o It is a standard language for Relational Database System. It enables a user
to create, read, update and delete relational databases and tables.
o All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use
SQL as their standard database language.
o SQL allows users to query the database in a number of ways, using
English-like statements.

Rules:
SQL follows the following rules:
 o Structure query language is not case sensitive. Generally, keywords of
SQL are written in uppercase.
o Statements of SQL are dependent on text lines. We can use a single SQL
statement on one or multiple text line.
o Using the SQL statements, you can perform most of the actions in a
database.
o SQL depends on tuple relational calculus and relational algebra.

SQL process:
o When an SQL command is executing for any RDBMS, then the system
figure out the best way to carry out the request and the SQL engine
determines that how to interpret the task.
o In the process, various components are included. These components can
be optimization Engine, Query engine, Query dispatcher, classic, etc.
o All the non-SQL queries are handled by the classic query engine, but SQL
query engine won't handle logical files.
A Brief History of SQL
 1970 − Dr. Edgar F. "Ted" Codd of IBM is known as the father of relational
databases. He described a relational model for databases.
 1974 − Structured Query Language (SQL) appeared.
 1978 − IBM worked to develop Codd's ideas and released a product named
System/R.
 1986 − IBM developed the first prototype of relational database and
standardized by ANSI. The first relational database was released by
Relational Software which later came to be known as Oracle.
 1987 − SQL became the part of the International Organization for
Standardization (ISO).

Characteristics of SQL
o SQL is easy to learn.
o SQL is used to access data from relational database management
systems.
o SQL can execute queries against the database.
o SQL is used to describe the data.
o SQL is used to define the data in the database and manipulate it
when needed.
o SQL is used to create and drop the database and table.
o SQL is used to create a view, stored procedure, function in a
database.
o SQL allows users to set permissions on tables, procedures, and
views.

Advantages of SQL
There are the following advantages of SQL:

High speed
Using the SQL queries, the user can quickly and efficiently retrieve a large
amount of records from a database.
No coding needed
In the standard SQL, it is very easy to manage the database system. It
doesn't require a substantial amount of code to manage the database
system.

Well defined standards

Long established are used by the SQL databases that are being used by
ISO and ANSI.

Portability
SQL can be used in laptop, PCs, server and even some mobile phones.

Interactive language
SQL is a domain language used to communicate with the database. It is
also used to receive answers to the complex questions in seconds.

Multiple data view

Using the SQL language, the users can make different views of the
database structure.

SQL Datatype
o SQL Datatype is used to define the values that a column can contain.
o Every column is required to have a name and data type in the database
table.

Datatype of SQL:
Data types are used to represent the nature of the data that can be stored
in the database table. For example, in a particular column of a table, if we
want to store a string type of data then we will have to declare a string
data type of this column.

Data types mainly classified into three categories for every database.

o String Data types

o Numeric Data types
o Date and time Data types

Data Types in MySQL, SQL Server and Oracle

Databases
MySQL Data Types
A list of data types used in MySQL database. This is based on MySQL 8.0.

MySQL String Data Types

CHAR(Size) It is used to specify a fixed length string that can contain

numbers, letters, and special characters. Its size can be 0 to 255
characters. Default is 1.

VARCHAR(Size) It is used to specify a variable length string that can contain

numbers, letters, and special characters. Its size can be from 0 to
 65535 characters.

BINARY(Size) It is equal to CHAR() but stores binary byte strings. Its size
parameter specifies the column length in the bytes. Default is 1.

VARBINARY(Size) It is equal to VARCHAR() but stores binary byte strings. Its size
parameter specifies the maximum column length in bytes.

TEXT(Size) It holds a string that can contain a maximum length of 255

characters.

TINYTEXT It holds a string with a maximum length of 255 characters.

MEDIUMTEXT It holds a string with a maximum length of 16,777,215.

LONGTEXT It holds a string with a maximum length of 4,294,967,295

characters.

ENUM(val1, val2, It is used when a string object having only one value, chosen from
val3,...) a list of possible values. It contains 65535 values in an ENUM list.
If you insert a value that is not in the list, a blank value will be
inserted.

SET( val1,val2,va It is used to specify a string that can have 0 or more values,
l3,....) chosen from a list of possible values. You can list up to 64 values
at one time in a SET list.

BLOB(size) It is used for BLOBs (Binary Large Objects). It can hold up to

65,535 bytes.

MySQL Numeric Data Types

BIT(Size) It is used for a bit-value type. The number of bits per value is specified in
size. Its size can be 1 to 64. The default value is 1.

INT(size) It is used for the integer value. Its signed range varies from -2147483648
to 2147483647 and unsigned range varies from 0 to 4294967295. The
size parameter specifies the max display width that is 255.
INTEGER(siz It is equal to INT(size).
e)

FLOAT(size, It is used to specify a floating point number. Its size parameter specifies
d) the total number of digits. The number of digits after the decimal point is
specified by d parameter.

FLOAT(p) It is used to specify a floating point number. MySQL used p parameter to

determine whether to use FLOAT or DOUBLE. If p is between 0 to24, the
data type becomes FLOAT (). If p is from 25 to 53, the data type
becomes DOUBLE().

DOUBLE(siz It is a normal size floating point number. Its size parameter specifies the
e, d) total number of digits. The number of digits after the decimal is specified
by d parameter.

DECIMAL(siz It is used to specify a fixed point number. Its size parameter specifies the
e, d) total number of digits. The number of digits after the decimal parameter
is specified by d parameter. The maximum value for the size is 65, and
the default value is 10. The maximum value for d is 30, and the default
value is 0.

DEC(size, d) It is equal to DECIMAL(size, d).

BOOL It is used to specify Boolean values true and false. Zero is considered as
false, and nonzero values are considered as true.

MySQL Date and Time Data Types

DATE It is used to specify date format YYYY-MM-DD. Its supported range is

from '1000-01-01' to '9999-12-31'.

DATETIME(fsp It is used to specify date and time combination. Its format is YYYY-MM-
) DD hh:mm:ss. Its supported range is from '1000-01-01 00:00:00' to
9999-12-31 23:59:59'.

TIMESTAMP(f It is used to specify the timestamp. Its value is stored as the number of
sp) seconds since the Unix epoch('1970-01-01 00:00:00' UTC). Its format is
YYYY-MM-DD hh:mm:ss. Its supported range is from '1970-01-01
00:00:01' UTC to '2038-01-09 03:14:07' UTC.
TIME(fsp) It is used to specify the time format. Its format is hh:mm:ss. Its
supported range is from '-838:59:59' to '838:59:59'

YEAR It is used to specify a year in four-digit format. Values allowed in four

digit format from 1901 to 2155, and 0000.

SQL Server Data Types

SQL Server String Data Type

char(n) It is a fixed width character string data type. Its size can be up to 8000
characters.

varchar(n) It is a variable width character string data type. Its size can be up to
8000 characters.

varchar(max It is a variable width character string data types. Its size can be up to
) 1,073,741,824 characters.

text It is a variable width character string data type. Its size can be up to
2GB of text data.

nchar It is a fixed width Unicode string data type. Its size can be up to 4000
characters.

nvarchar It is a variable width Unicode string data type. Its size can be up to
4000 characters.

ntext It is a variable width Unicode string data type. Its size can be up to 2GB
of text data.

binary(n) It is a fixed width Binary string data type. Its size can be up to 8000
bytes.

varbinary It is a variable width Binary string data type. Its size can be up to 8000
bytes.

image It is also a variable width Binary string data type. Its size can be up to
2GB.
SQL Server Numeric Data Types

bit It is an integer that can be 0, 1 or null.

tinyint It allows whole numbers from 0 to 255.

Smalli It allows whole numbers between -32,768 and 32,767.

nt

Int It allows whole numbers between -2,147,483,648 and 2,147,483,647.

bigint It allows whole numbers between -9,223,372,036,854,775,808 and

9,223,372,036,854,775,807.

float(n It is used to specify floating precision number data from -1.79E+308 to

) 1.79E+308. The n parameter indicates whether the field should hold the 4 or
8 bytes. Default value of n is 53.

real It is a floating precision number data from -3.40E+38 to 3.40E+38.

money It is used to specify monetary data from -922,337,233,685,477.5808 to

922,337,203,685,477.5807.

SQL Server Date and Time Data Type

datetim It is used to specify date and time combination. It supports range from
e January 1, 1753, to December 31, 9999 with an accuracy of 3.33
milliseconds.

datetim It is used to specify date and time combination. It supports range from
e2 January 1, 0001 to December 31, 9999 with an accuracy of 100
nanoseconds

date It is used to store date only. It supports range from January 1, 0001 to
December 31, 9999

time It stores time only to an accuracy of 100 nanoseconds

timesta It stores a unique number when a new row gets created or modified. The
mp time stamp value is based upon an internal clock and does not correspond
to real time. Each table may contain only one-time stamp variable.

SQL Server Other Data Types

Sql_variant It is used for various data types except for text, timestamp, and ntext.
It stores up to 8000 bytes of data.

XML It stores XML formatted data. Maximum 2GB.

cursor It stores a reference to a cursor used for database operations.

table It stores result set for later processing.

uniqueidentifi It stores GUID (Globally unique identifier).

er

Oracle Data Types

Oracle String data types

CHAR(size) It is used to store character data within the predefined length. It can
be stored up to 2000 bytes.

NCHAR(size) It is used to store national character data within the predefined

length. It can be stored up to 2000 bytes.

VARCHAR2(siz It is used to store variable string data within the predefined length. It
e) can be stored up to 4000 byte.

VARCHAR(SIZE It is the same as VARCHAR2(size). You can also use VARCHAR(size),

) but it is suggested to use VARCHAR2(size)

NVARCHAR2(si It is used to store Unicode string data within the predefined length.
ze) We have to must specify the size of NVARCHAR2 data type. It can be
stored up to 4000 bytes.

Oracle Numeric Data Types

NUMBER(p, s) It contains precision p and scale s. The precision p can range from 1
to 38, and the scale s can range from -84 to 127.

FLOAT(p) It is a subtype of the NUMBER data type. The precision p can range
from 1 to 126.

BINARY_FLOA It is used for binary precision( 32-bit). It requires 5 bytes, including

T length byte.

BINARY_DOUB It is used for double binary precision (64-bit). It requires 9 bytes,

LE including length byte.

Oracle Date and Time Data Types

DATE It is used to store a valid date-time format with a fixed length. Its range
varies from January 1, 4712 BC to December 31, 9999 AD.

TIMESTA It is used to store the valid date in YYYY-MM-DD with time hh:mm:ss
MP format.

Oracle Large Object Data Types (LOB Types)

BLOB It is used to specify unstructured binary data. Its range goes up to 2 32-1
bytes or 4 GB.

BFILE It is used to store binary data in an external file. Its range goes up to 2 32-1
bytes or 4 GB.

CLOB It is used for single-byte character data. Its range goes up to 2 32-1 bytes or 4
GB.

NCLOB It is used to specify single byte or fixed length multibyte national character
set (NCHAR) data. Its range is up to 232-1 bytes or 4 GB.

RAW(size It is used to specify variable length raw binary data. Its range is up to 2000
) bytes per row. Its maximum size must be specified.

LONG It is used to specify variable length raw binary data. Its range up to 2 31-1
RAW bytes or 2 GB, per row.