WEEK—5 Lecture-3 hr

DATABASE LANGUAGES

1. Types Of Database Languages:

DBMS provides different varieties of languages for different categories of users.

• Data-Definition Language
• Stored-definition language
• View-definition language
• Data-Manipulation Language

DBMS Languages includes the following:

➢ Data definition language (DDL) is used by the DBA and by database designers to define both
schemas. The DBMS will have a DDL compiler whose function is to process DDL statements in
order to identify descriptions of the schema constructs and to store the schema description in the
DBMS catalog. The DDL is used to specify the conceptual schema only.
➢ Storage Definition Language (SDL), is used to specify the internal schema. This permit the
DBA staff to control indexing choices and mapping of data to storage
➢ View Definition Language (VDL), to specify user views and their mappings to the conceptual
schema.
➢ Data Manipulation Language (DML): Once the database schemas are compiled and the
database is populated with data, users must have some means to manipulate the database. Typical
manipulations include retrieval, insertion, deletion, and modification of the data. The DBMS
provides a set of operations or a language called the Data manipulation language (DML).

There are two main types of DMLs.

▪ High-level or nonprocedural DML

▪ Lowlevel or procedural DML

A Low-level or Procedural DML:

• This DML must be embedded in a general-purpose programming language.

• This type of DML typically retrieves individual records or objects from the database and
processes each separately.
• Therefore, it needs to use programming language constructs, such as looping, to retrieve and
process each record from a set of records.
• Low-level DMLs are also called record-at-a-time DMLs.

High-level or Nonprocedural DML

• DML allow high-level DML statements to be embedded in a general-purpose programming

language.
• DML statements must be identified within the program so that they can be extracted by a
precompiler and processed by the DBMS.
• Highlevel DMLs, such as SQL, can specify and retrieve many records in a single DML
statement; therefore, they are called set-at-a-time or set-oriented DMLs
 A query in a high-level DML often specifies which data to retrieve rather than how to retrieve it;
therefore, such languages are also called declarative.

Whenever DML commands, whether high level or low level, are embedded in a general-purpose
programming language, that language is called the host language and the DML is called the data
sublanguage.

A high-level DML used in a standalone interactive manner is called a query language.

2. Commands/tasks in each types:

Data Definition Language (DDL) commands:

• CREATE to create a new table or database.
• ALTER for alteration.
• Truncate to delete data from the table.
• DROP to drop a table.
• RENAME to rename a table.

Data Manipulation Language (DML) commands:

• INSERT: It is used to insert data into a table.
• UPDATE: It is used to update existing data within a table.
• DELETE: It is used to delete records from a database table.
• LOCK: Table control concurrency.
• CALL: Call a PL/SQL or JAVA subprogram.
• EXPLAIN PLAN: It describes the access path to data.

3.
Integrity Constraints

Integrity Constraints
• Integrity constraints are a set of rules. It is used to maintain the quality of information.
• Integrity constraints ensure that the data insertion, updating, and other processes have to be
performed in such a way that data integrity is not affected.
• Thus, integrity constraint is used to guard against accidental damage to the database.

Types of Integrity Constraint:

Domain Constraints:
Domain constraints specify that within each tuple, the value of each attribute A must be an atomic
value from the domain dom(A).

Key Constraints:
A relation is defined as a set of tuples and all elements of a set are distinct; hence, all tuples in a
relation must also be distinct. This means that no two tuples can have the same combination of values
for all their attributes.
 Entity integrity constraints:
The entity integrity constraint states that no primary key value can be NULL. This is because the
primary key value is used to identify individual tuples in a relation.

Referential Integrity Constraints:

The referential integrity constraint is specified between two relations and is used to maintain the
consistency among tuples in the two relations. Informally, the referential integrity constraint states
that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation.

MySQL
4. • MySQL OVERVIEW:

MySQL is an open-source relational database management system (RDBMS). It is the most

popular database system used with PHP. MySQL is developed, distributed, and supported by Oracle
Corporation. The data in a MySQL database are stored in tables which consists of columns and rows.

5. MySQL Features:

The following are the most important features of MySQL:

• Relational Database Management System (RDBMS) MySQL is a relational database
management system.
• Easy to use. MySQL is easy to use.
• It is secure.
• Client/ Server Architecture.
• Free to download.
• It is scalable.
• Speed.
• High Flexibility.
• Compatible on many operating systems.

6. MySQL Datatypes:

MySQL uses many different data types broken into three categories −

• Numeric
• Date and Time
• String Types.

Numeric Data Types

MySQL uses all the standard ANSI SQL numeric data types. The following list shows the common
numeric data types.
➢ INT
➢ TINYINT
➢ SMALLINT
 ➢ MEDIUMINT
➢ BIGINT
➢ FLOAT(M,D)
➢ DOUBLE(M,D)
➢ DECIMAL(M,D)

Date and Time Types

The MySQL date and time datatypes are as follows −

➢ DATE: YYYY-MM-DD format.
➢ DATETIME: YYYY-MM-DD HH:MM:SS format.
➢ TIMESTAMP: YYYYMMDDHHMMSS format.
➢ TIME: HH:MM:SS format.
➢ YEAR(M): Stores a year in a 2-digit or a 4-digit format.

String Types

Although the numeric and date types are fun, most data you'll store will be in a string format. This
list describes the common string datatypes in MySQL.
➢ CHAR(M) − A fixed-length string between 1 and 255 characters in length.
➢ VARCHAR(M) − A variable-length string between 1 and 255 characters in length.
➢ BLOB or TEXT − A field with a maximum length of 65535 characters. BLOBs are
"Binary Large Objects" and are used to store large amounts of binary data, such as images
or other types of files. Fields defined as TEXT also hold large amounts of data. The
difference between the two is that the sorts and comparisons on the stored data are case
sensitive on BLOBs and are not case sensitive in TEXT fields. You do not specify a length
with BLOB or TEXT.
➢ TINYBLOB or TINYTEXT − A BLOB or TEXT column with a maximum length of 255
characters.
➢ MEDIUMBLOB or MEDIUMTEXT − A BLOB or TEXT column with a maximum
length of 16777215 characters.
➢ LONGBLOB or LONGTEXT − A BLOB or TEXT column with a maximum length of
4294967295 characters.
➢ ENUM − An enumeration, which is a fancy term for list. When defining an ENUM, you are
creating a list of items from which the value must be selected (or it can be NULL). For
example, if you wanted your field to contain "A" or "B" or "C", you would define your
ENUM as ENUM ('A', 'B', 'C') and only those values (or NULL) could ever populate that
field.

7. MySQL Standardization guidelines:

• Do not ever give anyone (except MySQL root accounts) access to the user table in the
mysql system database.
 • Learn how the MySQL access privilege system works.
• Do not store cleartext passwords in your database.

PostgreSQL
8. PostgreSQL OVERVIEW:

PostgreSQL (pronounced as post-gress-Q-L) is an open source relational database management

system (DBMS) developed by a worldwide team of volunteers. PostgreSQL is not controlled by any
corporation or other private entity and the source code is available free of charge.

9. PostgreSQL Features:

o Free to download.
o Compatible on several operation systems.
o Compatible with various programming languages.
o Compatible with Data Integrity.
o Support multiple features of SQL.
o Compatible with multiple data types.
o Highly extensible.
o Secure.
o Highly Reliable.

10. PostgreSQL Datatypes:

PostgreSQL supports a wide set of Data Types. Besides, users can create their own custom data type
using CREATE TYPE SQL command. There are different categories of data types in PostgreSQL.
They are discussed below.
Numeric Types:
Numeric types consist of two-byte, four-byte, and eight-byte integers, four-byte and eight-byte
floating-point numbers, and selectable-precision decimals.
Monetary Types:
The money type stores a currency amount with a fixed fractional precision. Values of the numeric,
int, and bigint data types can be cast to money. Using Floating point numbers is not recommended to
handle money due to the potential for rounding errors.
Character Types:
character varying(n), varchar(n):variable-length with limit
character(n), char(n):fixed-length, blank padded
text:variable unlimited length
Binary Data Types
Bytea: allows storage of variable-length binary string.
Date/Time Types
PostgreSQL supports a full set of SQL date and time types. Dates are counted according to the
Gregorian calendar.
Boolean Type
PostgreSQL provides the standard SQL type Boolean. The Boolean data type can have the
states true, false, and a third state, unknown, which is represented by the SQL null value.
Enumerated Type
Enumerated (enum) types are data types that comprise a static, ordered set of values. They are
equivalent to the enum types supported in a number of programming languages.
Unlike other types, Enumerated Types need to be created using CREATE TYPE command. This
type is used to store a static, ordered set of values. For example compass directions, i.e., NORTH,
SOUTH, EAST, and WEST or days of the week as shown below −
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
Enumerated, once created, can be used like any other types.
Geometric Type
Geometric data types represent two-dimensional spatial objects. The most fundamental type, the
point, forms the basis for all of the other types.
Network Address Type
PostgreSQL offers data types to store IPv4, IPv6, and MAC addresses. It is better to use these types
instead of plain text types to store network addresses.
Bit String Type
Bit String Types are used to store bit masks. They are either 0 or 1. There are two SQL bit
types: bit(n) and bit varying(n), where n is a positive integer.
Text Search Type
This type supports full text search, which is the activity of searching through a collection of natural-
language documents to locate those that best match a query.
XML Type
The XML data type can be used to store XML data.
JSON Type
The json data type can be used to store JSON (JavaScript Object Notation) data.
Array Type
PostgreSQL gives the opportunity to define a column of a table as a variable length
multidimensional array. Arrays of any built-in or user-defined base type, enum type, or composite
type can be created.
Composite Types
This type represents a list of field names and their data types, i.e., structure of a row or record of a
table.
 Range Types
Range types represent data types that uses a range of data.
Object Identifier Types
Pseudo Types
UUID Type

11. PostgreSQL Standardization guidelines: