1

Department of Computer Science

Bahria University, Islamabad

Spring 2020

Course Code : CSL-220

Database Management Systems

BS-IT-4AB

Lab Manual 5

Student Name: ______________________________

Enrollment Number: __________________________

 1

Lab #5

 Data Definition Language

________________________________________________________________________________

Create Table

Creating a basic table involves naming the table and defining its columns and each column's
data type.
The SQL CREATE TABLE statement is used to create a new table.
Syntax:
Basic syntax of CREATE TABLE statement is as follows:
CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype
);

CREATE TABLE is the keyword telling the database system what you want to do. In this case, you
want to create a new table. The unique name or identifier for the table follows the CREATE TABLE
statement.

Then in brackets comes the list defining each column in the table and what sort of data type it is. The
syntax becomes clearer with an example below.
Create Table toys (
toy_name varchar (10),
weight number,
color varchar(10)
);

A copy of an existing table can be created using a combination of the CREATE TABLE statement and
the SELECT statement.
create table toys_clone as
select * from toys;

1|Page
 Constraints
A constraint specified as part of the definition of a column or attribute is an inline specification.
A constraint specified as part of the table definition is an out-of-line specification.
Constraint Type Description
NOT NULL Allows or disallows inserts or updates of rows containing a null in a specified
column. A null is the absence of a value. By default, all columns in a table
allow nulls. NOT NULL constraints are intended for columns that must not lack
values.
Unique key Prohibits multiple rows from having the same value in the same column or
combination of columns but allows some values to be null.
Unique key constraints are appropriate for any column where duplicate values
are not allowed. Unique constraints differ from primary key constraints,
whose purpose is to identify each table row uniquely, and typically contain
values that have no significance other than being unique. Examples of unique
keys include:
 A customer phone number, where the primary key is the customer
number
 A department name, where the primary key is the department number
Primary key Combines a NOT NULL constraint and a unique constraint. It prohibits multiple
rows from having the same value in the same column or combination of
columns and prohibits values from being null.
In a primary key constraint, the values in the group of one or more columns
subject to the constraint uniquely identify the row. Each table can have one
primary key, which in effect names the row and ensures that no duplicate
rows exist.
A primary key can be natural or a surrogate. A natural key is a meaningful
identifier made of existing attributes in a table. For example, a natural key
could be a postal code in a lookup table. In contrast, a surrogate key is a
system-generated incrementing identifier that ensures uniqueness within a
table. Typically, a sequence generates surrogate keys.
Foreign key Designates a column as the foreign key and establishes a relationship between
the foreign key and a primary or unique key, called the referenced key.
A foreign key constraint requires that for each value in the column on which
the constraint is defined, the value in the other specified other table and
column must match. An example of a referential integrity rule is an employee
can work for only an existing department.
Check Requires a database value to obey a specified condition.
CREATE TABLE employees (
Employee_id number,
Last_Name varchar(15),
email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL,
CONSTRAINT emp_email_unq UNIQUE (email)
);
2|Page
 3

3|Page
Below figure shows a foreign key on the employees.department_id column. It guarantees that
every value in this column must match a value in the departments.department_id column.
Thus, no erroneous department numbers can exist in the employees.department_id column.

A self-referential integrity constraint is a foreign key that references a parent key in the same table.
In the following figure, a self-referential constraint ensures that every value in
the employees.manager_id column corresponds to an existing value in
the employees.employee_id column. For example, the manager for employee 102 must exist in
the employees table. This constraint eliminates the possibility of erroneous employee numbers in
the manager_id column.

4|Page
 5

Actions taken when DELETE or UPDATE operation is performed

When a referential integrity constraint violation occurs in case of delete or update operation, you can
specify one the operations as a response.
When a parent key is modified, referential integrity constraints can specify the following
actions to be performed on dependent rows in a child table:

No action on deletion or update:

In the normal case, users cannot modify referenced key values if the results would
violate referential integrity. For example, if employees.department_id is a foreign key to
departments, and if employees belong to a particular department, then an attempt to delete
the row for this department violates the constraint.

Cascading deletions:
A deletion cascades (DELETE CASCADE) when rows containing referenced key values are
deleted, causing all rows in child tables with dependent foreign key values to also be deleted.
For example, the deletion of a row in departments causes rows for all employees in this
department to be deleted.

Deletions that set null:

A deletion sets null (DELETE SET NULL) when rows containing referenced key values are
deleted, causing all rows in child tables with dependent foreign key values to set those values to
null. For example, the deletion of a department row sets the department_id column value to
null for employees in this department.

5|Page
Alter Table
The SQL ALTER TABLE command is used to add, delete or modify columns in an existing table.
You would also use ALTER TABLE command to add and drop various constraints on a an existing
table.
Syntax:
The basic syntax of ALTER TABLE to add a new column in an existing table is as follows:
ALTER TABLE table_name ADD column_name datatype;
alter table toys add ( price number );

The basic syntax of ALTER TABLE to DROP COLUMN in an existing table is as follows:
ALTER TABLE table_name DROP COLUMN column_name;
Alter table toys drop ( weight );

The basic syntax of ALTER TABLE to change the DATA TYPE of a column in a table is as follows:
ALTER TABLE table_name MODIFY COLUMN column_name datatype;

The basic syntax of ALTER TABLE to add a NOT NULL constraint to a column in a table is as
follows:
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

Alter Table To Apply Constraints

The basic syntax of ALTER TABLE to add a NOT NULL constraint to a column in a table is as
follows:
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows:
ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

The basic syntax of ALTER TABLE to ADD PRIMARY KEY constraint to a table is as follows:
ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

The basic syntax of ALTER TABLE to DROP CONSTRAINT from a table is as follows:
ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;

The basic syntax of ALTER TABLE to DROP PRIMARY KEY constraint from a table is as follows:
ALTER TABLE table_name DROP CONSTRAINT MyPrimaryKey;

6|Page
 7

Lab Tasks
1. Create a table named supplier based on the details figure
given

2. Use Create statement and inline specification to define

constraints for the given figure below

3. Using Create statement and out-of-line specification to

define constraints for the given figure

4. Apply NOT NULL constraint on SupCity without using Create statement.

5. Give two ways to apply Uniqueness constraint on SupAddress using inline specification
6. Create tables as per requirement shown below. You must apply integrity constraints during the
creation of relations
Employee Department

7. Insert dummy data employee and department relations (5 tuples).

8. Execute an insert query that tries to violate entity integrity constraint for each relation.
9. Execute an insert query that tries to violate referential integrity constraint.
10. Execute an insert query that tries to violate referential integrity constraint.
11. Execute delete operation violating the referential integrity constraint.
12. Alter foreign key constraint to allow deletion and cascade the impact of deletions.

Additional Questions
13. Specify Default value of 1 for Salary field in Employee Table.
14. Apply check constraint on Salary to make sure that negative or Zero value can not be
entered.
15. Alter self-reference constraint on manager to allow deletion using SET NULL

7|Page
8|Page