CS403 - Database Management Systems - Lecture Handout 26

User Rating:  / 0
PoorBest 

Related Content: CS403 - VU Lectures, Handouts, PPT Slides, Assignments, Quizzes, Papers & Books of Database Management Systems

Overview of Lecture

  • Different Commands of SQL

In the previous lecture we have seen the database of an examination system. We had drawn the ER model and then the relational model, which was normalized. In this lecture we will now start with different commands of SQL.

Categories of SQL Commands

We have already read in our previous lecture that there are three different types of commands of SQL, which are DDL, DML and DCL. We will now study DDL.

DDL

It deals with the structure of database.The DDL (Data Definition Language) allows specification of not only a set of relations, but also the following information for each relation:

  • The schema for each relation.
  • The domain of values associated with each attribute.
  • Integrity constraints.
  • The set of indices for each relation.
  • Security and authorization information.
  • Physical storage structure on disk.

Following are the three different commands of DDL:-

Create

The first data management step in any database project is to create the database. This task can range from the elementary to the complicated, depending on your needs and the database management system you have chosen. Many modern systems (including Personal Oracle7) include graphical tools that enable you to completely build the database with the click of a mouse button. This timesaving feature is certainly helpful, but you should understand the SQL statements that execute in response to the mouse clicks. This command is used to create a new database table. The table is created in the current default database. The name of the table must be unique to the database. The name must begin with a letter and can be followed by any combination of alphanumeric characters. The name is allowed to contain the underscore character ( _ ). This command can be used to create permanent disk-based or temporary in- memory database tables. Data stored in a temporary table is lost when the server is shutdown. To create a temporary table the "AS TEMP" attribute must be specified. Note that querying against a temporary in-memory table is generally faster than querying against a disk-based table. This command is non-transactional. If no file size is given for a disk-based table, the table will be pre-allocated to 1MB. If no filegrowth is given, the default is 50%. It is used to create new tables, fields, views and indexes. It is used to create database. The format of statement is as under:

CREATE DATABASE db_name

For Example CREATE DATABASE EXAM. So now in this example database of exam has been created. Next step is to create tables. There are two approaches for creating the tables, which are:

  • Through SQL Create command
  • Through Enterprise Manager

Create table command is used to:

  • Create a table
  • Define attributes of the table with data types
  • Define different constraints on attributes, like primary and foreign keys, check constraint, not null, default value etc.

The format of create table command is as under:

The format of create table

Let us now consider the CREATE statement used to create the Airport table definition for the Airline Database.

CREATE TABLE Airport

(airport char(4) not null,

name varchar(20),

checkin varchar(50),

resvtns varchar(12),

flightinfo varchar(12) );

Table Name.(Airport)

The name chosen for a table must be a valid name for the DBMS.

Column Names. (Airport, Name, ..., FlightInfo)

The names chosen for the columns of a table must also be a valid name for the DBMS.

Data Types

Each column must be allocated an appropriate data type. In addition, key columns, i.e. columns used to uniquely identify individual rows of a given table, may be specified to be NOT NULL. The DBMS will then ensure that columns specified as NOT NULL always contain a value.

The column definition is explained as under:

< column_definition > ::= { column_name data_type }

[ DEFAULT constant_expression ]

[ < column_constraint > ] [ ...n ]

The column constraint is explained as under:

< column_constraint > ::= [ CONSTRAINT constraint_name ]

{ [ NULL | NOT NULL ]

| [ { PRIMARY KEY | UNIQUE } ]

| [ [ FOREIGN KEY ]

REFERENCES ref_table [ ( ref_column ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

]

| CHECK( logical_expression )

}

)

We will now see some examples of CREATE command. This is a very simple command for creating a table.

CREATE TABLE Program (

prName char(4),

totSem tinyint,

prCredits smallint)

If this command is to written in SQL Server, it will be written in Query Analyzer. We will now see an example in which has more attributes comparatively along with different data types:

CREATE TABLE Student

(stId char(5),

stName char(25),

stFName char(25),

stAdres text,

stPhone char(10),

prName char(4)

curSem smallint,

cgpa real)

In this example there are more attributes and different data types are also there. We will now see an example of creating a table with few constraints:

CREATE TABLE Student (

stId char(5) constraint ST_PK primary key constraint ST_CK check (stId
like‘S[0- 9][0-9][0-9][0-9]'),

stName char(25) not null,

stFName char(25),

stAdres text,

stPhone char(10),

prName char(4),

curSem smallint default 1,

cgpa real)

Every constraint should be given a meaningful name as it can be referred later by its name. The check constraint checks the values for any particular attribute. In this way different types of constraints can be enforced in any table by CREATE command.

Summary

Designing a database properly is extremely important for the success of any application. In today’s lecture we have seen the CREATE command of SQL. How different constraints are applied on this command with the help of different examples. This is an important command and must be practiced as it is used to create database and different tables. So create command is part of DDL.

Exercise:

Create a database of Exam System and create table of student with different constraints in SQL Server.