Introduction & Programming

Database Management with

System
Chapter-4: ESP8266
Relational Data Model

AKASH SURESH PATIL

Assistant Professor
Department of Computer Science and
Engineering
Content

1. Relational Data Model…………………………………..1

2. Constraints & Keys……………..……….…………………2

3. Relational Algebra Operations……………………….3

INDEX
What are the Constraints of DBMS?

• In DBMS, constraints refer to limitations placed on data or data processes.

This indicates that only a particular type of data may be entered into the
database or that only a particular sort of operation can be performed on the
data inside.

• Constraints thereby guarantee data accuracy in a database management

system (DBMS).
What are the Constraints of DBMS?

The following can be guaranteed via constraints

• Data Accuracy ? Data accuracy is guaranteed by constraints, which make sure

that only true data is entered into a database. For example, a limitation may
stop a user from entering a negative value into a field that only accepts
positive numbers.

• Data Consistency ? The consistency of data in a database can be upheld by

using constraints. These constraints are able to ensure that the primary key
value in one table is followed by the foreign key value in another table.

• Data integrity ? The accuracy and completeness of the data in a database are
ensured by constraints. For example, a constraint can stop a user from
putting a null value into a field that requires one.
What are the Constraints of DBMS?
• Constraints are rules applied on table columns to maintain accuracy,
consistency, and integrity of data. They prevent invalid data from being
stored.

1. NOT NULL Constraint

• Ensures that a column must have a value; it cannot be left empty (NULL).

• Example:
CREATE TABLE Student (
student_id INT NOT NULL,
name VARCHAR(50) NOT NULL
);
• Here, both student_id and name must be provided; no missing values.
What are the Constraints of DBMS?
2. UNIQUE Constraint
• Ensures that no two rows have the same value in a column (or set of
columns).
• You can have multiple UNIQUE constraints in a table.

• Example:
CREATE TABLE Student (
student_id INT UNIQUE,
email VARCHAR(100) UNIQUE
);

• Both student_id and email must be unique across all rows.

What are the Constraints of DBMS?
3. PRIMARY KEY Constraint
• A unique identifier for each record in a table.
• Combines UNIQUE + NOT NULL.
• Only one primary key per table.
• Can be a single column or multiple columns (composite key).

• Example:
CREATE TABLE Student (
student_id INT PRIMARY KEY,
name VARCHAR(50)
);

• student_id uniquely identifies each student.

What are the Constraints of DBMS?
4. FOREIGN KEY Constraint
• Enforces a relationship between two tables.
• Refers to the primary key of another table.
• Ensures referential integrity: a value must exist in the referenced table.

• Example:
CREATE TABLE Enrollment (
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES Student(student_id)
);

• Only student IDs present in the Student table can appear in Enrollment.
What are the Constraints of DBMS?
5. CHECK Constraint
• Specifies a condition that must be true for each row.
• Used for validations.

• Example:

CREATE TABLE Student (

age INT CHECK (age >= 18)
);

• Only allows inserting students with age 18 or above.

What are the Constraints of DBMS?
6. DEFAULT Constraint

• Provides a default value when no value is supplied during insert.

• Example:

CREATE TABLE Student (

city VARCHAR(50) DEFAULT 'Unknown'
);
• If no city is provided, 'Unknown' is inserted automatically.
Keys in DBMS
• Keys are one of the basic requirements of a relational database model. It is
widely used to identify the tuples(rows) uniquely in the table.

• We also use keys to set up relations amongst various columns and tables of a
relational database.

Why do we require Keys in a DBMS?

• We require keys in a DBMS to ensure that data is organized, accurate, and
easily accessible. Keys help to uniquely identify records in a table, which
prevents duplication and ensures data integrity.

• Keys also establish relationships between different tables, allowing for

efficient querying and management of data. Without keys, it would be
difficult to retrieve or update specific records, and the database could
become inconsistent or unreliable.
Different Types of Database Keys
Super Key

• The set of one or more attributes (columns) that can uniquely identify a tuple
(record) is known as Super Key. For Example, STUD_NO, (STUD_NO,
STUD_NAME), etc.

• A super key is a group of single or multiple keys that uniquely identifies rows
in a table. It supports NULL values in rows.

• A super key can contain extra attributes that aren’t necessary for uniqueness.
For example, if the “STUD_NO” column can uniquely identify a student,
adding “SNAME” to it will still form a valid super key, though it’s unnecessary.

• Example:
Different Types of Database Keys

STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

Consider the table shown above. STUD_NO+PHONE is a super key.

Different Types of Database Keys
Candidate Key
• The minimal set of attributes that can uniquely identify a tuple is known as a
candidate key. For Example, STUD_NO in STUDENT relation.

• A candidate key is a minimal super key, meaning it can uniquely identify a

record but contains no extra attributes.

• It is a super key with no repeated data is called a candidate key.

• The minimal set of attributes that can uniquely identify a record.

• A candidate key must contain unique values, ensuring that no two rows have
the same value in the candidate key’s columns.

• Every table must have at least a single candidate key.

• A table can have multiple candidate keys but only one primary key.
Different Types of Database Keys
Example

STUD_NO is the candidate key for relation STUDENT.

STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

The candidate key can be simple (having only one attribute) or composite as
well.
Different Types of Database Keys
Primary Key
• There can be more than one candidate key in relation out of which one can
be chosen as the primary key. For Example, STUD_NO, as well as
STUD_PHONE, are candidate keys for relation STUDENT but STUD_NO can be
chosen as the primary key (only one out of many candidate keys).
• A primary key is a unique key, meaning it can uniquely identify each record
(tuple) in a table.
• It must have unique values and cannot contain any duplicate values.
• A primary key cannot be NULL, as it needs to provide a valid, unique
identifier for every record.
• A primary key does not have to consist of a single column. In some cases,
a composite primary key (made of multiple columns) can be used to
uniquely identify records in a table.
• Databases typically store rows ordered in memory according to primary key
for fast access of records using primary key.
Different Types of Database Keys
• Example

STUDENT table -> Student(STUD_NO, SNAME, ADDRESS, PHONE) , STUD_NO is a

primary key

STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

Different Types of Database Keys
Alternate Key

• An alternate key is any candidate key in a table that is not chosen as

the primary key. In other words, all the keys that are not selected as the
primary key are considered alternate keys.

• An alternate key is also referred to as a secondary key because it can

uniquely identify records in a table, just like the primary key.

• An alternate key can consist of one or more columns (fields) that can
uniquely identify a record, but it is not the primary key

• Eg:- SNAME, and ADDRESS is Alternate keys

Different Types of Database Keys
Consider the table shown above.
STUD_NO, as well as PHONE both,
are candidate keys for relation STUDENT but
PHONE will be an alternate key
(only one out of many candidate keys).
Different Types of Database Keys
Foreign Key
• A foreign key is an attribute in one table that refers to the primary key in
another table. The table that contains the foreign key is called
the referencing table, and the table that is referenced is called
the referenced table.

• A foreign key in one table points to the primary key in another table,
establishing a relationship between them.
• It helps connect two or more tables, enabling you to create relationships
between them. This is essential for maintaining data integrity and preventing
data redundancy.
• They act as a cross-reference between the tables.
• For example, DNO is a primary key in the DEPT table and a non-key in EMP
Different Types of Database Keys
Refer Table STUDENT shown above.
STUD_NO in STUDENT_COURSE is a
foreign key to STUD_NO in STUDENT relation.

STUD_NO TEACHER_NO COURSE_NO

1 005 C001

2 056 C005
Different Types of Database Keys
• It may be worth noting that, unlike the Primary Key of any given relation,
Foreign Key can be NULL as well as may contain duplicate tuples i.e. it need
not follow uniqueness constraint.

• For Example, STUD_NO in the STUDENT_COURSE relation is not unique. It has

been repeated for the first and third tuples. However, the STUD_NO in
STUDENT relation is a primary key and it needs to be always unique, and it
cannot be null.
Different Types of Database Keys
Composite Key

• Sometimes, a table might not have a single column/attribute that uniquely

identifies all the records of a table. To uniquely identify rows of a table, a
combination of two or more columns/attributes can be used. It still can give
duplicate values in rare cases. So, we need to find the optimal set of
attributes that can uniquely identify rows in a table.

• It acts as a primary key if there is no primary key in a table

• Two or more attributes are used together to make a composite key .

• Different combinations of attributes may give different accuracy in terms of

identifying the rows uniquely.
Different Types of Database Keys
FULLNAME + DOB can be combined
together to access the details of a student.
PPT Content Resources Reference Sample:

1. Book Reference
Kotler, P., & Keller, K. L. (2016). Marketing Management (15th ed.). Pearson Education.
2. Journal Article
Smith, J. A., & Brown, P. (2020). The impact of digital transformation on higher education. Journal of Education
Research, 45(3), 123-135.
3. Website Reference
World Health Organization. (2023). Global health trends. Retrieved December 26, 2024, from [Link]
4. Conference Presentation
Lee, R. (2022). The role of AI in higher education. Paper presented at the International Conference on Learning
Technologies, Boston, MA.
5. Report
Deloitte Insights. (2021). Technology trends 2021: Engineering the future. Deloitte Development LLC.
6. Sources
[Link]