ZeePedia Add to Favourites   |   Contact us


Database Management Systems

<<< Previous Categories of SQL Commands, Next >>>
 
img
Database Management System (CS403)
VU
Lecture No. 26
Reading Material
"Database Management Systems", 2nd edition, Raghu Ramakrishnan, Johannes Gehrke,
McGraw-Hill
Overview of Lecture
o 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
201
img
Database Management System (CS403)
VU
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:
CREATE
TABLE
[
database_name.[
owner
]
|
owner.
]
table_name
.
{
<
column_definition
>
(
|
column_name
AS
omputed_column_expression
| < table_constraint >
}
| [ { PRIMARY KEY | UNIQUE } [ ,...n ] ]
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),
202
img
Database Management System (CS403)
VU
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 } ]
]
203
img
Database Management System (CS403)
VU
| 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)
204
img
Database Management System (CS403)
VU
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.
205
Table of Contents:
  1. Introduction to Databases and Traditional File Processing Systems
  2. Advantages, Cost, Importance, Levels, Users of Database Systems
  3. Database Architecture: Level, Schema, Model, Conceptual or Logical View:
  4. Internal or Physical View of Schema, Data Independence, Funct ions of DBMS
  5. Database Development Process, Tools, Data Flow Diagrams, Types of DFD
  6. Data Flow Diagram, Data Dictionary, Database Design, Data Model
  7. Entity-Relationship Data Model, Classification of entity types, Attributes
  8. Attributes, The Keys
  9. Relationships:Types of Relationships in databases
  10. Dependencies, Enhancements in E-R Data Model. Super-type and Subtypes
  11. Inheritance Is, Super types and Subtypes, Constraints, Completeness Constraint, Disjointness Constraint, Subtype Discriminator
  12. Steps in the Study of system
  13. Conceptual, Logical Database Design, Relationships and Cardinalities in between Entities
  14. Relational Data Model, Mathematical Relations, Database Relations
  15. Database and Math Relations, Degree of a Relation
  16. Mapping Relationships, Binary, Unary Relationship, Data Manipulation Languages, Relational Algebra
  17. The Project Operator
  18. Types of Joins: Theta Join, Equi–Join, Natural Join, Outer Join, Semi Join
  19. Functional Dependency, Inference Rules, Normal Forms
  20. Second, Third Normal Form, Boyce - Codd Normal Form, Higher Normal Forms
  21. Normalization Summary, Example, Physical Database Design
  22. Physical Database Design: DESIGNING FIELDS, CODING AND COMPRESSION TECHNIQUES
  23. Physical Record and De-normalization, Partitioning
  24. Vertical Partitioning, Replication, MS SQL Server
  25. Rules of SQL Format, Data Types in SQL Server
  26. Categories of SQL Commands,
  27. Alter Table Statement
  28. Select Statement, Attribute Allias
  29. Data Manipulation Language
  30. ORDER BY Clause, Functions in SQL, GROUP BY Clause, HAVING Clause, Cartesian Product
  31. Inner Join, Outer Join, Semi Join, Self Join, Subquery,
  32. Application Programs, User Interface, Forms, Tips for User Friendly Interface
  33. Designing Input Form, Arranging Form, Adding Command Buttons
  34. Data Storage Concepts, Physical Storage Media, Memory Hierarchy
  35. File Organizations: Hashing Algorithm, Collision Handling
  36. Hashing, Hash Functions, Hashed Access Characteristics, Mapping functions, Open addressing
  37. Index Classification
  38. Ordered, Dense, Sparse, Multi-Level Indices, Clustered, Non-clustered Indexes
  39. Views, Data Independence, Security, Vertical and Horizontal Subset of a Table
  40. Materialized View, Simple Views, Complex View, Dynamic Views
  41. Updating Multiple Tables, Transaction Management
  42. Transactions and Schedules, Concurrent Execution, Serializability, Lock-Based Concurrency Control, Deadlocks
  43. Incremental Log with Deferred, Immediate Updates, Concurrency Control
  44. Serial Execution, Serializability, Locking, Inconsistent Analysis
  45. Locking Idea, DeadLock Handling, Deadlock Resolution, Timestamping rules