Database Lab 1

A. Basic data types in MSSQL server:

1. CHAR (n): Fixed Length Character Data

2. VARCHAR (n): Variable Length Character Data

3. NUMERIC (o, d): Numeric Data Type for Integers and Real. [o- Overall Number of Digits and d= Number of Digits to the Right of Decimal Point]

4. DATE: Data Type for Storing Date and Time The default format for date is DD-MMM-YY (Ex: 05-OCT-99)

5. LONG: Character data up to a length of 2GB.

B. Creating your first object (a table without any integrity constraints) · To create a table we use the “Create table …” DDL.

CREATETABLE <tablename> (

<columnname><type> [<defaultvalue>][<columnconstraints>], :::

<columnname><type> [<defaultvalue>][<columnconstraints>], <tableconstraint>,

::: <tableconstraint> );

This creates a table named <table name>. The columns of the table are specified in a comma-delimited list of name/data type pairs. Optionally, you may specify constraints and default values.

Execute the following DDL in the query editor.

CREATE TABLE students( idno char(11),

name varchar(30), dob date,

);

Now list the tables created using the above mentioned query.

C. Inserting records in the table

· To insert records in a table we shall be using the “Insert into table…” DML. There are two forms of it.

INSERTINTO <tablename> [(<attribute>, :::, <attribute>)]

VALUES(<expression>, :::, <expression>);

- 1 -

First form : Here we need to specify values for all the columns and in the same order as they were specified at the time of table creation.For a particular column that can take null values we can specify it’s value as NULL also.

INSERT          INTO          students         VALUES(‘1997B2A5563’,’K       Ramesh’,’21-Jun-75’,7.86,27);

The above Insert statement will create a newrecord in the students table and insert these values in the corresponding columns. Note that all the char, varchar and date literals arespecified in single quotes and also the format of date value is “dd-mon-yy” which is the default format. Using the above SQL statementyou can insert as many records as you want.

Second Form : In the second form we can use column names in the SQL statement and this gives us a lot of flexibility. Firstly,we can omit any column and secondly, we don’t need to maintain any order.

INSERT INTO students(name,idno,age) VALUES(‘R Suresh’,’1998A7PS003’,25); The above Insert statement will create a new record andinserts the specified values into the corresponding columns. It will insert NULL values in those columns that are not specified hereexcept… Hence only those columns which can take NULL values should only be omitted. Can you write the first stmt in the second form?

Note that specifying a column as of type char doesn’t guarantee that it will accept exactly that much number of characters only. Inthose cases where you will specify lesser number it will simply pad blank spaces.

D. Constraints

Your DBMS can do much more than just store and access data. It can also enforce rules (called constraints) on what data areallowed in the database. Such constraints are important because they help maintain data integrity. For example, you may want toensure that each cgpa is not less than 2.0. When you specify the data type of a column, you constrain the possible values that column may hold. This is called a domain constraint. For example, a column of type INTEGER may only hold whole numbers within a certainrange. Any attempt to insert an invalid value will be rejected by SQL. SQL allows the specification of many more constraint types. SQLenforces constraints by prohibiting any data in the database that violate any constraint. Any insert, update, or delete that would result in a constraint violation is rejected without changing the database.

There are two forms of constraint specification:

> Column level Constraints:- Apply to individual columns only (are specified along with the column definition only)

>Table Level constraints:- Apply to one or more columns (are specified at the end)

Constraints can be added to the table at the time of creation or after the creation of the table using ‘alter table’ command.

NOT NULL

By default, most DBMSs allow NULL as a value for any column of any data type. You may not be so keen on allowing NULL values for some columns. You can require the database to prohibit NULL values for particular columns by using the NOT NULL column constraint. ManyDBMSs also include a NULL column constraint, which specifies that NULL values are allowed; however, because this

- 2 -

is the default behavior, this constraint usually is unnecessary. Note that the NULL column constraint is not part of the SQL specification.

create table staff(

sid numeric(10) NOT NULL, name varchar(20),

dept varchar(15) );

Now if you try inserting,

Insert into staff(name,dept) values(‘Krishna’, ‘PSD’); You will get error

Cannot insert the value NULL into column …

UNIQUE

The UNIQUE constraint forces distinct column values. Suppose you want to avoid duplicate course numbers. Just specify the UNIQUE column constraint on the courseno column as follows:

create table course ( compcode numeric(4) unique,

courseno varchar(9) not null unique, course_name varchar(20),

units numeric(2) not null );

Note that UNIQUE only applies to non-NULL values. A UNIQUE column may have many rows containing a NULL value. Of course, we canexclude all NULL values for the column using the NOT NULL constraint with the UNIQUE constraint.

UNIQUE also has a table constraint form that applies to the entire table instead of just a single column. Table constraints are specifiedas another item in the comma-delimited list of table elements. Such table constraints apply to groups of one or more columns. Consider the following CREATE TABLE statement:

drop table course; --to delete the table

create table course ( compcode numeric(4), courseno varchar(9) not null, course_namevarchar(20), units numeric(2) not null,

unique(compcode,courseno) -- table level constraint );

The combination of compcode, courseno is always unique. Note that table level unique constraint can also be for single column. Unique isnothing but the candidate key constraint but a unique column can take null values.

PRIMARY KEY

It is similar to unique but with implicit NOT NULL constraint. The primary key of a table is a column or set of columns that uniquelyidentifies a row in the table.

- 3 -

For example, idno is the primary key from the students table. We can declare a primary key using the PRIMARY KEY constraint. Here weshow PRIMARY KEY used as a column constraint.

create table employee(

empid numeric(4) primary key, name varchar(30) not null

);

Creating primary key as a table constraint is shown below.

create table registered ( courseno varchar(9), idno char(11),

grade varchar(10),

primary key(courseno, idno) );

You can name your constraints by using an optional prefix.

CONSTRAINT <name> <constraint

create table registered ( courseno varchar(9), idno char(11),

grade varchar(10),

CONSTRAINT pk_registered primary key(courseno, idno) );

This naming can be applied to unique constraints also. Naming constraint helps in altering or dropping that constraint at a later time.

FOREIGN KEY

A foreign key restricts the values of a column (or a set of columns) to the values appearing in another column (or set of columns) or toNULL. In table registered (child table), courseno is a foreign key that refers to courseno in table course (parent table). We want all of the values of courseno in the registered table either to reference a courseno from course or to be NULL. Any other courseno in the registered table would create problems because you couldn’t look up information about the courseno which is not offered.

In SQL, we specify a foreign key with the REFERENCES column constraint.

REFERENCES <referenced table>[(<referenced column>)]

A column with a REFERENCES constraint may only have a value of either NULL or a value found in column <referenced column> of table <referenced table>. If the <referenced column> is omitted, the primary key of table <referenced table> is used.

- 4 -

Before creating a foreignkeys in registered table, let us re-create course,students tables with proper constarints.

drop table students; CREATE TABLE students( idno char(11),

name varchar(30), dob date,

cgpa numeric(4,2), age numeric(2),

constraint pk_students primary key(idno) );

drop table course; create table course ( compcode numeric(4),

courseno varchar(9) not null, course_name varchar(20), units numeric(2) not null,

constraint un_course unique(compcode,courseno), -- table level constraint constraint pk_course primary key(courseno)

);

create table registered1(

courseno varchar(9) references course, --column level foreign key idno char(11) references students,

grade varchar(10),

primary key(courseno, idno) );

The same can be declared as table level constraint with proper naming.

create table registered2( courseno varchar(9) ,

idno char(11), grade varchar(10),

constraint pk_registered2 primary key(courseno, idno), constraint fk_cno foreign key(courseno) referencescourse, constraint fk_idno foreign key (idno) references students );

To create a foreign key reference, SQL requires that the referenced table/column already exist.

Maintaining foreign key constraints can be painful. To update or delete a referenced value in the parent table, we must makesure that we first handle all foreign keys referencing that value in the child table. For example, to update or delete 2007A7PS001 fromthe students table, we must first update or delete all registered.idno. SQL allows us to specify the default actions for maintaining foreign key constraints for UPDATE and DELETE on the parent table by adding a referential action clause to the end of a column or table foreign key constraint:

- 5 -

ON UPDATE <action> ON DELETE <action>

Any UPDATE or DELETE on the parent table triggers the specified <action> on the referencing rows in the child table.

Action                              Definition

SET NULL Sets any referencing foreign key values to NULL.

SET DEFAULT             Sets any referencing foreign key values to the default value (which may be NULL).

CASCADE On delete, this deletes any rows with referencing foreign key values. On update, this updates any row with referencing foreign key values to the new value of the referenced column.

NO ACTION                  Rejects any update or delete that violates the foreign key constraint. This is the default action.

RESTRICT Same as NO ACTION with the additional restriction that the action cannot be deferred

Try the following.

drop table registered2; create table registered2( courseno varchar(9) ,

idno char(11), grade varchar(10) ,

constraint pk_registered2 primary key(courseno, idno),

constraint fk_cno foreign key(courseno) references course ON UPDATE CASCADE ON DELETE CASCADE,

constraint fk_idno foreign key (idno) references students ON UPDATE CASCADE ON DELETE CASCADE

);

Modify the above query with other actions SET NULL, SET DEFAULT, NO ACTION, RESTRICT

CHECK

We can specify a much more general type of constraint using the CHECK constraint. A CHECK constraint specifies a booleanvalue expression to be evaluated for each row before allowing any data change. Any INSERT, UPDATE, or DELETE that would causethe condition for any row to evaluate to false is rejected by the DBMS.

CHECK (<condition>)

A CHECK constraint may be specified as either a column or table constraint. In the following example, we specify CHECK constraints onthe students table:

create table student1( idno char(11) primary key, name varchar(20) not null,

cgpa numeric(4,2) check(cgpa >= 2 and cgpa <= 10), -- cgpa constraint

- 6 -

roomno numeric(3) check(roomno >99),

hostel_code varchar(2) check (hostel_code in (‘VK’, ‘RP’, ‘MB’ )) );

Check constraints can also be named.

Does a roomno with a NULL value violate the CHECK constraint? No. In this case, the CHECK condition evaluates to unknown. The CHECK constraint only rejects a change when the condition evaluates to false. In the SQL standard, a CHECK constraint condition may even include subqueries referencing other tables; however, many DBMSs do not implement this feature.

Now list all the tables created using the following query. Select * from INFORMATION_SCHEMA.TABLES;

Now list all the constraints using the following query Select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS;