Database Management Systems

Prof . R A Tela

The Language of DBMS�

SQL�Structured Query Language�Standard language for querying and manipulating data.

​

�1.Data Definition Language (DDL)

Create/alter/delete tables and their attributes

2.Data Manipulation Language (DML)

Insert/delete/modify tuples in tables

​

SQL

• SQL: widely used non-procedural language

​

• E.g. find the name of the customer with customer-id 192-83-7465� select customer.customer-name� from customer� where customer.customer-id = ‘192-83-7465’

​

• E.g. find the balances of all accounts held by the customer with customer-id 192-83-7465� select account.balance� from depositor, account� where depositor.customer-id = ‘192-83-7465’ and� depositor.account-number = account.account-number

​

• Application programs generally access databases through one of
• Language extensions to allow embedded SQL
• Application program interface (e.g. ODBC/JDBC) which allow SQL queries to be sent to a database

​

3

Tables in RDBMS

4

Product

Attribute names

Table name

Tuples or rows

Steps to Define the Schema

5

Product

Step 1: Define table name and its attributes

​

Product(PName, Price, Category, Manufacturer)

Basic data types

• Numeric
• Integer numbers: INTEGER, INT, and SMALLINT
• Floating-point (real) numbers: FLOAT or REAL, and DOUBLE PRECISION
• Character-string
• Fixed length: CHAR(n), CHARACTER(n)
• Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)

6

Data Types and Domain of Attributes

Product(PName, Price, Category, Manfacturer)

Data Types and Domain of Attributes

• Boolean
• Values of TRUE or FALSE or NULL
• DATE
• Ten positions
• Components are YEAR, MONTH, and DAY in the form YYYY-MM-DD
• Timestamp
• Includes the DATE and TIME fields
• Plus a minimum of six positions for decimal fractions of seconds
• Optional WITH TIME ZONE qualifier

​

7

Steps to Define the Schema

8

Step 2: Define Data Types and Domain of Attributes.

​

Product(PName, Price, Category, Manfacturer)

Pname : Varchar,

Price: Float,

Category: Varchar

Manfacturer: Varchar

Constraints: Restrictions on values of

Attribute.

9

Step 3: Specifying Constraints.

​

Product(PName, Price, Category, Manfacturer)

• Specifying Key and Referential Integrity Constraints

• Specifying Attribute and Domain Constraints

• Specifying Key Constraints

Specifying Attribute and Domain Constraints

• NOT NULL
• NULL is not permitted for a particular attribute

​

• Default value
• DEFAULT <value>

​

• CHECK clause
• Dnumber > 0 AND Dnumber < 21;

​

• UNIQUE clause
• Specifies attributes that have unique values

​

10

Schema of Table Product

11

​

Product(Pname varchar Primary Key,

Price float Not Null,

Category varchar, check(Gadget, Photoraphy,

Household

Manufacturer varchar )