MCA23: Database Systems: Unit I

Database Systems - Unit I

Contents
1 Introduction 2
1.1 Purpose of Database Systems ................................................................. 2
1.2 Database Architecture ............................................................................. 3

2 Relational Model 4
2.1 Relational Model Concepts ..................................................................... 4
2.2 Characteristics of Relations .................................................................... 5
2.3 Relational Model Notation...................................................................... 5
2.4 Relational Model Constraints and Relational Database Schemas . 5
2.5 Relational Databases and Relational Database Schemas...................... 6
2.6 Entity integrity, Referential integrity and Foreign keys ........................ 8
2.7 Other types of constraints: ..................................................................... 9
2.8 Update operations, Transactions, and Dealing with Constraint
Violations ............................................................................................... 10
2.8.1 The Insert Operation: ............................................................... 10
2.8.2 The Delete Operation:............................................................... 10
2.8.3 The Update Operation: ............................................................. 10

3 SQL: Structured Query Language 11

3.1 SQL Data Definition and Data Types.................................................. 11
3.2 The Parts of SQL Language................................................................. 11
3.3 SQL Data Definition............................................................................. 12
3.4 Specifying Constraints in SQL .............................................................. 12
3.5 Schema Modification ............................................................................. 14
3.5.1 The DROP Command .............................................................. 14
3.5.2 The ALTER Command .......................................................... 14

Page 1
Database Systems - Unit I

UNIT I
Introduction Introduction, Database Architecture.
Relational Model: Relational Model Concepts; Relational Model Constraints
and Relational Database Schemas; Update Operations, Transactions and Deal-
ing with Constraint Violations.
SQL: SQL Data Definition and Data Types; Specifying Constraints in SQL,
Schema Modifications.

1 Introduction
Definition 1.1 (Database). A database is an organized collection of structured
information, or data, typically stored electronically in a computer system.
Definition 1.2 (Database Management Systems). A database management
system (DBMS) is a collection of interrelated data and a set of programs to
access those data. The collection of data, usually referred to as the database,
contains information relevant to an enterprise. The primary goal of a DBMS
is to provide a way to store and retrieve database information that is both
convenient and efficient.
Definition 1.3 (Database Systems). A database is usually controlled by a
database management system (DBMS). Together, the data and the DBMS,
along with the applications that are associated with them, are referred to as a
database system, often shortened to just database.

1.1 Purpose of Database Systems

Database systems were designed as a successor of the file processing system that
had many disadvantages. These disadvantages were not there in the database
systems and hence are the reasons for the development of the database systems.
• Data redundancy and inconsistency
• Difficulty in accessing data
• Data isolation
• Integrity problems
• Atomicity problems
• Concurrent access anomalies
• Security problems

Page 2
Database Systems - Unit I

1.2 Database Architecture

Page 3
Database Systems - Unit I

2 Relational Model
2.1 Relational Model Concepts
The relational model represents the database as a collection of relations. Infor-
mally, each relation resembles a table of values or, to some extent, a flat file of
records. It is called a flat file because each record has a simple linear or flat
structure.

When a relation is thought of as a table of values, each row in the table rep-
resents a collection of related data values. A row represents a fact that typically
corresponds to a real world entity or relationship.

In the formal relational model terminology, a row is called a tuple, a column

header is called an attribute, and the table is called a relation. The data type
describing the types of values that can appear in each column is represented by
a domain of possible values.

Domain A domain D is a set of atomic values. By atomic we mean that

each value in the domain is indivisible as far as the formal relational model is
concerned. A datatype or format is also specified for each domain.

A relation schema R, demoted by R(A1, A2, A3, ...), is made up of relation

name R and a list of attributes A1, A2, A3. . . An. Each attribute Ai is the
name of a role played by some domain D in the relation schema R. D is called
the domain of Ai and is denoted by dom(Ai). A relation schema is used to
describe a relation; R is called the name of this relation. The degree or (arity)
of a relation is the number of attributes n of its relation schema.

A relation r of the relation schema R (A1, A2, A3, ...An) also denoted by r(R) is
a set of n – tuples r = {t1, t2, t3 , ...tm}. Each n-tuple t is an ordered list of n
values t = ⟨v1, v2, v3, ...vn⟩ where each value Vv1 ≤ i ≤ n is an element of dom (Ai) or
is a special NULL value.

Page 4
Database Systems - Unit I

2.2 Characteristics of Relations

1. Ordering of tuples in a relation.
2. Ordering of Values within a tuple.
3. Values and NULLs in the tuples.

2.3 Relational Model Notation

We will use the following notation in our presentation
1. A relation schema R of a degree n is denoted by R (A1, A2, A3...An).
2. The uppercase letters Q, R, S denote relation names.
3. The lowercase letters q,r,s denote relation states.
4. The letters t,u,v denote tuples.
5. The name of a relation schema such as STUDENT also indicates the
current state of tuples in that relation.
6. An attribute A can be qualified with the relation name R to which it
belongs by using the dot notation. R.A
7. An n-tuple t in a relation r(R) is denoted by t = ⟨v1, v2, ...vn⟩ where vi is the
value corresponding to the attribute Ai

8. Tuples can be denoted as t [Au, Aw, ...Az]. Eg., t [Name] = ⟨′BarbaraBenson′⟩and

t [Ssn, Gpa, Age] = ⟨′ 533 − 69 − 1238′, 3.25, 19⟩

2.4 Relational Model Constraints and Relational Database

Schemas
The state of the whole database will correspond to the states of all its relations at
a particular point in time. There are generally many restrictions or constraints
on the actual values in a database state. Constraints on databases can generally
be divided into three main categories:
1. Constraints that are inherent in the data model. We call these inherent
model-based constraints or implicit constraints.
2. Constraints that can be directly expressed in the schemas of the data
model, typically by specifying them in the DDl. We call these schemas
based constraints or explicit constraints
3. Constraints that cannot be directly expressed in the schemas of the data
model, and hence must be expressed and enforced by the application pro-
grams or in some other ways. We call these application-based or se-
mantic constraints or business rules.
4. Constraints that are functional dependencies or multivalued dependencies
also called as data dependencies.

Page 5
Database Systems - Unit I

Domain Constraints: Domain constraint specify that within each tuple, the
value of each attribute A must be an atomic value from the domain dom (A).
Key constraints and constraints on NULL values: A relation is defined as a set
of tuples. All tuples in a relation must also be distinct. This means that no two
tuples can have the same combinations of values for all their attributes.

A superkey SK specifies a uniqueness constraint that no two distinct tuples

in any state r of R can have the same value for SK. Every relation has at least
one default superkey – the set of all its attributes.

A key k of a relation schema R is a superkey of R with the additional property

that removing any attribute A from K leaves a set of attributes K’, that is not
a superkey of R any more. A key satisfies two properties
1. Two distinct tuples in any state of the relation cannot have identical values
for (all) the attributes in the key. This uniqueness property also applies
to a superkey.
2. It is a minimal superkey – that is, a superkey from which we cannot
remove any attributes and still have the uniqueness constraint hold. This
minimality property is required for a key but is optional for a superkey.
In general, a relation schema may have more than one key. In this case, each
of the keys is called a candidate key. For example, the CAR relation has two
candidate keys: License number and Engine serial number. It is common to
designate one of the candidate keys as the primary key of the relation. Primary
key is generally underlined while specifying the relation.

A superkey is a set of attributes within a table whose values can be used

to uniquely identify a tuple. A candidate key is a minimal set of attributes
necessary to identify a tuple; this is also called a minimal superkey.

2.5 Relational Databases and Relational Database Schemas

A relational database schema S is a set of relation schemas S = {R1, R2, R3, ...Rm}
and a set of integrity constraints IC. A relational database state DB of S is a
set of relation states DB = {r1, r2, ...rm} such that each ri is a state of Ri and
such that the ri relation states satisfy the integrity constraints specified in IC.

Page 6
Database Systems - Unit I

A database state that does not obey all the integrity constraints is called not
valid, and a state that satisfies all the constraints in the defined set of integrity
constraints IC is called a valid state.

Integrity constraints are specified on a database schema and are expected to

hold on every valid database state of that schema.

Page 7
Database Systems - Unit I

2.6 Entity integrity, Referential integrity and Foreign keys

The entity integrity constraint states that no primary key value can be NULL.

The referential integrity constraint is specified between two relations and is

used to maintain the consistency among tuples in the two relations. Informally,
the referential integrity constraint states that a tuple in one relation that refers
to another relation must refer to an existing tuple in that relation.

Page 8
Database Systems - Unit I

A set of attributes FK in relation schema R1 is a foreign key of R1 that

references relation R2 if it satisfies the following rules:
1. The attributes in FK have the same domain(s) as the primary key at-
tributes PK of R2; the attributes FK are said to reference or refer to the
relation R2.
2. A value in FK in a tuple t1 of the current state r1 (R1) either occurs as a
value of PK for some tuple t2 in the current state r2 (R2 ) or is NULL.
In this definition, R1 is called the referencing relation and R2 is the referenced
relation. If these two conditions hold, a referential integrity constraint from R1
to R2 is said to hold. A foreign key can refer to its own relation.

2.7 Other types of constraints: -

• Semantic integrity constraints: the salary of an employee should never be
more than his supervisor. Such types of constraints are implemented by
the language using triggers and assertions.
• State constraints: at any time, the state of a database should always be
valid.
• Transition constraints: the salary of an employee can only increase.

Page 9
Database Systems - Unit I

2.8 Update operations, Transactions, and Dealing with

Constraint Violations
The operations of the relational model are as follows:
• Retrievals: The user formulates a query that specifies the data of in-
terest; and a new relation is formed by applying relational operators to
retrieve the data.
• Updates: There are three basic operations that can change the states of
relations in the database: Insert, Delete and Update (or Modify). They
insert new data, delete old data, or modify existing data records, respec-
tively. Insert is used to insert one or more new tuples in a relation, delete
is used to delete tuples, and update (or modify) is used to change the
values of some attributes in existing tuples. Whenever these operations
are applied, the integrity constraints specified on the relational database
schema should not be violated.

2.8.1 The Insert Operation:

The insert operation provides a list of attribute values for a new tuple t that
is to be inserted into a relation R. Insert can violate any of the four types of
constraints.
• Domain constraint: If the attribute does not exist in the table or the value
of the attribute is of a different data type.
• Key constraint can be violated if a key value in the new tuple t already
exists
• Entity integrity can be violated if any part of the primary key of the new
tuple t is NULL
• Referential integrity can be violated if the value of any foreign key in t
refers to a tuple that does not exist in the referenced relation.

2.8.2 The Delete Operation:

The delete operation can violate only referential integrity. This occurs if the tu-
ple being deleted is referenced by foreign keys from other tuples in the database.
Options available if a deletion operation causes a violation are: -
• Restrict
• Cascade
• Set null/ set default

2.8.3 The Update Operation:

The update (or Modify) operation is used to change the values of one or more
attributes in a tuple (or tuples) of some relation R. It is necessary to specify a
condition on the attributes of the relation to select the tuple (or tuples) to be
modified.

Page 10
Database Systems - Unit I

3 SQL: Structured Query Language

SQL uses the terms table, row and columnfor the formal relational model
terms relation, tuple, and attribute, respectively. The main SQL command
for data definition is the CREATE statement, which can be used to create
schemas, tables(relations), and domains (as well as other constructs such as
views, assertions, and triggers).

3.1 SQL Data Definition and Data Types

An SQL Schema is identified by a schema name, and includes an autho-
rization identifier to indicate the user or account who owns the schema, as
well as descriptorsfor each element in the schema. Schema elements include
tables, constraints, views, domains and other constructs (such as authorization
grants) that determine the schema.

A Schema is created via the CREATE SCHEMA statement, which can in-
clude all the schema elements definition.

Example: To create a schema for airline-related tables and give the au-
thorization ID anita access to all of the objects that use the schema, use the
following syntax:

CREATE SCHEMA FLIGHTS AUTHORIZATION anita

3.2 The Parts of SQL Language

• Data-Definition Language: The SQL DDL provides commands for
defining relation schemas, deleting relations, and modifying relation schemas.
• Data - Manipulation Language: The SQL DML provides the ability
to query information from the database and to insert tuples into, delete
tuples from, and modify tuples in the database.
• Integrity The SQL DDL includes commands for specifying integrity con-
straints that the data stored in the database must satisfy. Updates that
violate integrity constraints are disallowed.
• View Definition The SQL DDL includes commands for defining views
• Transaction Control SQL includes commands for specifying the begin-
ning and end of transactions.
• Embedded SQL and Dynamic SQL Embedded and dynamic SQL
define how SQL statements can be embedded within general - purpose
programming languages, such as C, C++ and Java
• Authorization The SQL DDL includes commands for specifying access
rights to relations and views.

Page 11
Database Systems - Unit I

3.3 SQL Data Definition

The SQL standard supports a variety of buit - in types, including,
• char(n) A fixed length character string with user - specified length n. the
full form, character, can be used instead.
• varchar(n) A variable length character string with user specified maxi-
mum length n. the full form, character varying is equivalent
• Binary(n) Equal to CHAR (), but stores binary byte strings. The size
parameter specifies the column length in bytes. Default is 1.
• int An integer(a finite subset of the integers that is machine dependent.)
The full form, integer is equivalent.
• smallint A small integer(a machine-dependent subset of the integer type).
• numeric(p,d) A fixed point number with user specified precision. The
number consists of p digits (plus a sign), and d of the p digits are to the
right of the decimal point. Thus, numeric(3,1) allows 44.5 to be stored
exactly, but neither 444.5 or 0.32 can be stored exactly in a field of this
type.
• real, double precision Floating point and double precision floating point
numbers with machine dependent precision
• float(n) A floating point number, with precision of atleast n digits.
• Bool Zero is considered as false, nonzero values are considered as true.
• Date A date. Format: YYYY-MM-DD. The supported range is from
’1000-01-01’ to ’9999-12-31’
• TIMESTAMP(fsp)A timestamp. TIMESTAMP values are stored as
the number of seconds since the Unix epoch (’1970-01-01 [Link]’ UTC).
Format: YYYY-MM-DD hh:mm:ss.

3.4 Specifying Constraints in SQL

Create Command The CREATE TABLE statement is used to create a new
table in a database. The example has the mechanism of giving a primary key
in the create table command itself.

CREATE TABLE table name (

column1 datatype ,
column2 datatype ,
column3 datatype ,
....
);

Page 12
Database Systems - Unit I

Example:
CREATE TABLE Persons (
ID in t NOT NULL,
LastName v ar char ( 255 ) NOT NULL,
FirstName var char ( 2 5 5 ) ,
Age int ,
PRIMARY KEY ( ID)
);

CREATE TABLE Orders (

OrderID in t NOT NULL,
OrderNumber in t NOT NULL,
PersonID int ,
PRIMARY KEY ( OrderID ) ,
FOREIGN KEY ( PersonID )
REFERENCES Persons ( ID)
);

CREATE TABLE Orders (

OrderID in t NOT NULL,
OrderNumber in t NOT NULL,
PersonID int ,
PRIMARY KEY ( OrderID )
);

OR
ALTER TABLE Orders ADD FOREIGN KEY ( PersonID ) REFERENCES Persons ( ID ) ;

Deleting the Structure of a Table

General Syntax DROP TABLE table name;
Note: Be careful before dropping a table. Deleting a table will result in loss of
complete information stored in the table!

Table Structure Display: To look at the columns and datatypes of a

table
General Syntax
DESCRIBE table name;
DESC table name;
Examples
Describe Orders;
Desc Persons;

Insert into
The INSERT INTO command is used to insert new rows in a table.
INSERT INTO P e rs ons ( ID , Lastname , Firstname , Age )
VALUES ( 1001 , ’ Bobby ’ , ’ Brown ’ , 2 9 ) ;

Page 13
Database Systems - Unit I

INSERT INTO P ersons VALUES ( 1 0 0 2 , ’ St e l l a ’ , ’ Green ’ , 3 6 ) ;

INSERT INTO Orders VALUES ( 101 , 1011 , Nul l ) ;

Alter Statement to change the constraints on the table after creation
ALTER TABLE Persons ADD CONSTRAINT PK Person PRIMARY KEY
( ID , LastName ) ;

Update
Setting or changing the value of a particular row based on the primary key of
that table.

UPDATE Orders SET P e r s onid = 1001 WHERE OrderId = 10 1 ;

Displaying the contents of a table

SELECT: The SELECT command is used to select data from a database. The
data returned is stored in a result table, called the result set.
Se l e c t ∗ from P ersons ;
Se l e c t ∗ from Orders ;
Se l e c t orderid , ordernumber from Orders ;

3.5 Schema Modification

3.5.1 The DROP Command
The DROP command can be used to drop named schema elements, such as
tables, domains or constraints. One can also drop a [Link] example, if a
whole schema is no longer needed, the DROP SCHEMA command can be used.
There are two drop behavior options: CASCADE and RESTRICT.
DROP SCHEMA Company CASCADE;
DROP SCHEMA Company RESTRICT;
If the RESTRICT option is chosen instead of CASCADE, a table is dropped
only if it is not referred in any constraint.

3.5.2 The ALTER Command

The definition of a base table or of other named schema elements can be changed
by using the ALTER command. For base tables, the possible alter table actions
include adding or dropping a column (attribute), changing a column definition,
and adding or dropping table constraints.
ALTER TABLE [Link] ADD COLUMN Job Varchar ( 1 2 ) ;
ALTER TABLe [Link] DROP COLUMN Address CASCADE;
ALTER TABLE [Link] ALTER COLUMN Mgr ssn DROP DEFAULT;
ALTER TABLE [Link] ALTER COLUMN
Mgr ssn SET DEFAULT ’ 3 33 44 555 5 ’ ;
ALTER TABLE [Link] DROP CONSTRAINT EMPSUPERFK CASCADE;

Page 14