Department of Computer Science and Engineering (CSE)

DBMS

Overview of Databases

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Outline

• Relational Data Structure

• Keys and types of keys

• Integrity Constraints and its types

• Schema and Instance

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Relational Data Structure

• A relation is nothing but a table of values.

• Every row in the table represents a collection of related data values.
These rows in the table denote a real-world entity or relationship.
• Tables
• In the Relational model the, relations are saved in the table format. It
is stored along with its entities. A table has two properties rows and
columns. Rows represent records and columns represent attributes.
• Entity
• The main data objects are termed as Entities. An entity may be any
object, class, person or place.
• e.g. a School Management Software, Student is an entity, Teacher is
an entity. If a Student is an Entity, then the complete dataset of all the
students will be the Entity Set

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Relational Data Structure

• Attribute
• Each column in a Table. Attributes are the properties which define a
relation. e.g. Student, Rollno, NAME etc.
• Tuple
• It is nothing but a single row of a table, which contains a single record.
• Degree
• The total number of attributes which in the relation is called the
degree of the relation.
• Cardinality: Total number of rows present in the Table.

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Relational Data Structure

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Relational Data Structure

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Relational Data Structure

Formal terms Alternative 1 Alternative2

Relation Table File

Tuple Row Record

Attribute Column Field

Alternative terminologies for relational database

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Keys in DBMS

• Key plays an important role in the database.

• Keys help you uniquely identify a row in a table by a combination of one
or more columns in that table.
• It is also used to establish and identify relationships between tables.
• e..g student roll number is a key.

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Keys in DBMS

• There are various types of keys in DBMS.

• Primary Key
• A column or group of columns in a table which helps us to uniquely
identifies every row in that table is called a primary key.
• Rules for defining Primary key:
• Two rows can't have the same primary key value
• It must for every row to have a 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.

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Keys in DBMS

In the EMPLOYEE table, ID can be

primary key since it is unique for each
employee.
The selection of the primary key is
based on requirement and developers.

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Keys in DBMS

• Foreign key
• A foreign key is a column which is added to create a relationship
with another table.
• Foreign keys are used to link together two or more different tables
which have some form of relationship with each other.
• Foreign keys values should always be matched by corresponding
primary key values.
• e.g. Consider the two tables(EMP,DEPT)
• In a company, every employee works in a specific department and
employee and department are two different tables. So you can't store
the information of the department in the employee table. That's why
you need to link these two tables through the primary key of one
table.

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Keys in DBMS

In the EMP table, Deptno is the foreign key and both the tables are related.

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Keys in DBMS

• Candidate Key
• Candidate keys are defined as the minimal set of fields which can
uniquely identify each record in a table.
• It is an attribute or a set of attributes that can act as a Primary Key
for a table to uniquely identify each record in that table. There can
be more than one candidate 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

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Keys in DBMS

Student_id name phone age

1 Pankaj 9876723452 17
2 Priya 9991165674 19
3 Rahul 7898756543 18
4 Vijay 8987867898 19
5 Sandeep 9990080080 17
6 Priya 6283061431 16

student_id and phone both are candidate keys for table Student.
The Primary key should be selected from the candidate keys.

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Keys in DBMS

• Candidate keys have the properties of uniqueness and irreducibility.

• Irreducibility property means that if a candidate key is a composite
key(consists of more than one attribute)then no individual attribute of
candidate key, which participate into it, is unique.
• For example, if the combination of (name, age) is unique then it can be
identified as a candidate key if and only if name and age individually
are not unique.

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Keys in DBMS

• Super Key
• Super key is a set of one or more than one keys that can be used to
uniquely identify the record in table.
• A super key has the uniqueness property but not necessarily the
irreducibility property. A candidate key is a special case of a super key.
• In the table defined,
• super key would include student_id, (student_id, name), phone etc.
• student_id is unique for every row of data, hence it can be used to
identity each row uniquely.
• (student_id, name), name of two students can be same but student_id
can't be same hence this combination can also be a key.
• phone number for every student will be unique hence again, phone can
also be a key. So they all are super keys.

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Keys in DBMS

• Consider a relation of Patient in which Patient_number is unique.

Then, Patient_number is a candidate key and (Patient number, Patient
name) is a super key.
• Thus, you can say that "A superset of a candidate key is a super key."

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Keys in DBMS

• Secondary or Alternative key

• The alternate keys of any table are simply those candidate keys, which are
not currently selected as the primary key.
• Out of all candidate keys, only one gets selected as primary key, remaining
keys are known as alternate or secondary keys.

• StudID, Roll No, Email are qualified to become a primary key.

• Since StudID is the primary key, Roll No, Email becomes the alternative key.

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Keys in DBMS

• Composite Key
• Composite key is a combination of more than one attributes that can be
used to uniquely identity each record. It is also known as “Compound”
key.
• A composite key may be a candidate or primary key.

• Composite Key in Student_Information table:

{ Student_Id, Student_Name }

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Keys in DBMS

• Surrogate Key
• Surrogate key is a kind of primary key, but it is not defined by the
designer.
• It is a system generated random number.
• Surrogate key is an artificial key that is used to uniquely identify the
record in table.
• For example, in SQL Server or Sybase database system contain an
artificial key that is known as “Identity”.
• Artificial keys are permitted when no attribute has all the primary
key properties or the primary key is large and complex.

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Integrity Constraints

• Integrity constraints are a set of rules. It is used to maintain the quality

of information.
• Integrity constraints ensure that the data insertion, updating, and other
processes have to be performed in such a way that data integrity is not
affected.
• Thus, integrity constraint is used to guard against accidental damage to
the database.
• Types of Integrity Constraint
• Domain constraints
• Entity integrity constraints
• Referential Integrity Constraints
• Key constraints

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Integrity Constraints

• Domain constraints
• Domain constraints can be defined as the definition of a valid set of
values for an attribute.
• The data type of domain includes string, character, integer, time,
date, currency, etc. The value of the attribute must be available in the
corresponding domain.
• Example:

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Integrity Constraints

• Entity integrity constraints

• The entity integrity constraint states that primary key value can't be null.
• This is because the primary key value is used to identify individual rows
in relation and if the primary key has a null value, then you can't identify
those rows.
• A table can contain a null value other than the primary key field.
• Example:

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Integrity Constraints

• Referential Integrity Constraints

• A referential integrity constraint is specified between two tables.
• In the Referential integrity constraints, if a foreign key in Table 1 refers
to the Primary Key of Table 2, then every value of the Foreign Key in
Table 1 must be null or be available in Table 2.

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Integrity Constraints

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Integrity Constraints

• Key constraints
• Keys are the entity set that is used to identify an entity within its entity
set uniquely.
• An entity set can have multiple keys, but out of which one key will be the
primary key. A primary key can contain a unique and null value in the
relational table.
• Example:

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Schema and Instances

• Database changes over time when information is inserted or deleted.

• The collection of information stored in the database at a particular
moment is called an instance of the database.
• The overall design of the database is called the database schema.
• A database schema is the skeleton structure of the database.
• A database schema can be represented by using the visual diagram.
That diagram shows the database objects and relationship with each
other.
• A database schema is designed by the database designers to help
programmers whose software will interact with the database. The
process of database creation is called data modeling.

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

Schema and Instances

• The schema will remain the same while the values filled into it
change from instant to instant.
• When the schema framework is filled in with data item values, it is
referred as an instance of the schema.
• The data in the database at a particular moment of time is called a
database state or snapshot, which is also called the current set of
occurrences or instances in the database.
• Schema is of three types: Physical schema, logical schema and view
schema.

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

For example, in the given figure, the database changes whenever you add a
new grade or add a student.
The data at a particular moment of time is called the instance of the database.

University Institute of Engineering (UIE)

 Department of Computer Science and Engineering (CSE)

THANKS…..

University Institute of Engineering (UIE)