INTRODUCTION TO THE RELATIONAL MODEL

The main construct for representing data in the relational model is a relation. A relation consists of a relation
schema and a relation instance. The relation instance is a table, and the relation schema describes the column heads
for the table.
The schema specifies the relation's name, the name of each field (or column, or attribute), and the domain of each
field. A domain is referred to in a relation schema by the domain name and has a set of associated values

e.g: Students(sid: string, name: string, login: string, age: integer, gpa: real)

An instance of a relation is a set of tuples, also called records, in which each tuple has the same number of fields
as the relation schema. A relation instance can be thought of as a table in which each tuple is a row, and all rows
have the same number of fields
Creating and Modifying Relations Using SQL
The SQL language standard uses the word table to denote relation, and we often follow this convention when
discussing SQL. The subset of SQL that supports the creation, deletion, and modification of tables is called the Data
Definition Language (DDL).

The CREATE TABLE statement is used to define a new table. To create the Students relation, we can use the
following statement:
CREATE TABLE Students ( sid CHAR(20) , name CHAR(30) , login CHAR(20) , age INTEGER, gpa
REAL);

Tuples are inserted ,using the INSERT command. We can insert a single tuple into the Students table as follows:

INSERT INTO Students (sid, name, login, age, gpa) VALUES (53688, 'Smith', 'smith@ee', 18, 3.2);

We can delete tuples using the DELETE command. DELETE FROM Students S WHERE S.name = 'Smith‘;

We can modify the column values in an existing row using the UPDATE command.

UPDATE Students S SET S.gpa = S.gpa - 0.1 WHERE S.gpa >= 3.3
INTEGRITY CONSTRAINTS OVER RELATIONS

SQL Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and
integrity of the data inside table.

Constraints can be divided into the following two types,

1.Column level constraints: Limits only column data.
2.Table level constraints: Limits whole table data.

Constraints are used to make sure that the integrity of data is maintained in the database. Following are the most
used constraints that can be applied to a table.

•NOT NULL
•UNIQUE
•PRIMARY KEY
•FOREIGN KEY
•CHECK
•DEFAULT
PRIMARY KEY is a column or group of columns in a table that uniquely identify every row in that table. The
Primary Key can't be a duplicate meaning the same value can't appear more than once in the table. A table cannot
have more than one primary key.

Rules for defining Primary key:

•Two rows can't have the same primary key value
•The primary key field cannot be null.
•The value in a primary key column can never be modified or updated if any foreign key refers to that primary
key.

The syntax to create a primary key using the CREATE TABLE statement in Oracle/PLSQL is:

CREATE TABLE table_name ( column1 datatype null/not null, column2 datatype null/not null, ...
CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n) );

CREATE TABLE Persons (

ID int ,
LastName varchar(255) ,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
NOT NULL Constraint

NOT NULL constraint restricts a column from having a NULL value. Once NOT NULL constraint is applied to a
column, you cannot pass a null value to that column. It enforces a column to contain a proper value.
One important point to note about this constraint is that it cannot be defined at table level.

CREATE TABLE Student(sid int NOT NULL, Name varchar(60), Age int);

UNIQUE Constraint

UNIQUE constraint ensures that a field or column will only have unique values. A UNIQUE constraint field will not
have duplicate data. This constraint can be applied at column level or table level.

Using UNIQUE constraint when creating a Table (Table Level)

CREATE TABLE Student(sid int UNIQUE, Name varchar(60), Age int);

CHECK Constraint

CHECK constraint is used to restrict the value of a column between a range. It performs check on the values, before
storing them into the database. Its like condition checking before saving data into a column.

CREATE table Student(sid int CHECK(sid > 0), Name varchar(60) NOT NULL, Age int );
CANDIDATE KEY is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with
no repeated attributes. The Primary key should be selected from the candidate keys. Every table must have at least a
single candidate key. A table can have multiple candidate keys but only a single primary key.

Properties of Candidate key:

•It must contain unique values
•Candidate key may have multiple attributes
•Must not contain null values
•It should contain minimum fields to ensure uniqueness
•Uniquely identify each record in a table

ALTERNATE KEYS is a column or group of columns in a table that uniquely identify every row in that table. A
table can have multiple choices for a primary key but only one can be set as the primary key. All the keys which are
not primary key are called an Alternate Key.
Super Key: The set of attributes which can uniquely identify a tuple is known as Super Key. For Example,
STUD_NO, (STUD_NO, STUD_NAME) etc.

•Adding zero or more attributes to candidate key generates super key.

•A candidate key is a super key but vice versa is not true.
 ENFORCING INTEGRITY CONSTRAINTS

The following insertion violates the primary key constraint because there is already a tuple with the s'id 53688,
and it will be rejected by the DBMS:

INSERT INTO Students (sid, name, login, age, gpa) VALUES (53688, 'Mike', 'mike@ee', 17,3.4);

The following insertion violates the constraint that the primary key cannot contain null:

INSERT INTO Students (sid, name, login, age, gpa) VALUES (null, 'Mike', 'mike@ee', 17,3.4);

An update can cause violations, similar to an insertion:

UPDATE Students S SET S.sid = 50000 WHERE S.sid = 53688 ;

This update violates the primary key constraint because there is already a tuple with sid 50000.
QUERYING RELATIONAL DATA

We can retrieve rows corresponding to students who are younger than 18 with the following SQL query:

SELECT * FROM Students S WHERE S.age < 18

Logical database design :E-R to Relational
A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is
actually a composition of a table in the form of a predefined SQL query.

A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables
which depends on the written SQL query to create a view.

Views, which are a type of virtual tables allow users to do the following

 Structure data in a way that users or classes of users find natural or

intuitive.
 Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and
no more.
 Summarize data from various tables which can be used to generate reports
Creating Views

Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple
tables or another view.

To create a view, a user must have the appropriate system privilege according to the specific implementation.

The basic CREATE VIEW syntax is as follows

CREATE VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];

SQL > CREATE VIEW CUSTOMERS_VIEW AS SELECT name, age FROM CUSTOMERS;

Now, you can query CUSTOMERS_VIEW in a similar way as you query an actual table. Following is an
example for the same.

SQL > SELECT * FROM CUSTOMERS_VIEW;

Destroying / altering Tables and Views

Altering the Tables

The SQL ALTER TABLE command is used to add, delete or modify columns in an existing table. You should also
use the ALTER TABLE command to add and drop various constraints on an existing table.

Syntax

The basic syntax of an ALTER TABLE command to add a New Column in an existing table is as follows.

ALTER TABLE table_name ADD column_name datatype;

The basic syntax of an ALTER TABLE command to DROP COLUMN in an existing table is as
follows.

ALTER TABLE table_name DROP COLUMN column_name;

The basic syntax of an ALTER TABLE command 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 an ALTER TABLE command 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 an ALTER TABLE command to ADD CHECK CONSTRAINT to a table is as follows.

ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

The basic syntax of an ALTER TABLE command 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 an ALTER TABLE command to DROP CONSTRAINT from a table is as follows.

ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;

Dropping Tables / Views

The syntax to drop a table / view are given below

DROP TABLE table_name;

DROP VIEW view_name;

Following is an example to drop the CUSTOMERS_VIEW and CUSTOMERS table.

DROP TABLE CUSTOMERS;

DROP VIEW CUSTOMERS_VIEW;

 Deleting Rows from a Table / View

Rows of data can be deleted from a table / view.

Following is an example to delete all records in both tables / views.

SQL > DELETE FROM CUSTOMERS;

SQL > DELETE FROM CUSTOMERS_VIEW;

The WHERE clause can be used to delete a record from table / view with conditions.

Following is an example to delete a record having AGE = 22 in both tables / views.

SQL > DELETE FROM CUSTOMERS WHERE age = 22;

SQL > DELETE FROM CUSTOMERS_VIEW WHERE age = 22;

Tuple Relational Calculus

A tuple variable is a variable that takes on tuples of a particular relation schema as values. That is, every value
assigned to a given tuple variable has the same number and type of fields.

 A tuple relational calculus query has the form { T I p(T) }, where T is a tuple variable and p(T) denotes a
formula that describes T

 The result of this query is the set of all tuples t for which the formula p(T) evaluates to true with T = t.

 The language for writing formulas p(T) is thus at the heart of TRC and essentially a simple subset of first-order
logic.
Domain Relational Calculus