Section 1.1: Understanding Constraints

Constraints are essential for regulating the types of data that can be stored in a database. They serve to establish rules for the data held within a table, enhancing the accuracy and reliability of the information stored. By enforcing these constraints, we can prevent data transaction violations.

Section 1.2: Keys in DBMS

A key is defined as an attribute or a collection of attributes that uniquely identifies a record or tuple, which represents a single row in a table.

Section 1.3: Creating a Demo Table

To illustrate the concepts discussed, here’s how to create a sample table along with some data entries:

-- Table Creation

CREATE TABLE "AGENTS"

(

"AGENT_CODE" NVARCHAR(10) NOT NULL PRIMARY KEY,

"AGENT_NAME" NVARCHAR(30),

"WORKING_AREA" NVARCHAR(30),

"COMMISSION" bigint,

"PHONE_NO" NVARCHAR(20),

"COUNTRY" NVARCHAR(20)

);

-- Adding Data

INSERT INTO AGENTS VALUES ('A007', 'Ramasundar', 'Bangalore', 1, '077-25814763', null);

INSERT INTO AGENTS VALUES ('A003', 'Alex', 'London', 2, '075-12458969', '');

INSERT INTO AGENTS VALUES ('A008', 'Alford', 'New York', 3, '044-25874365', '');

INSERT INTO AGENTS VALUES ('A011', 'Ravi Kumar', 'Bangalore', 4, '077-45625874', '');

INSERT INTO AGENTS VALUES ('A010', 'Santakumar', 'Chennai', 5, '007-22388644', null);

INSERT INTO AGENTS VALUES ('A012', 'Lucida', 'San Jose', 6, '044-52981425', '');

INSERT INTO AGENTS VALUES ('A005', 'Anderson', 'Brisbane', 7, '045-21447739', null);

INSERT INTO AGENTS VALUES ('A001', 'Subbarao', 'Bangalore', 8, '077-12346674', '');

INSERT INTO AGENTS VALUES ('A002', 'Mukesh', 'Mumbai', 9, '029-12358964', null);

INSERT INTO AGENTS VALUES ('A006', 'McDen', 'London', 10, '078-22255588', '');

INSERT INTO AGENTS VALUES ('A004', 'Ivan', 'Toronto', 11, '008-22544166', null);

INSERT INTO AGENTS VALUES ('A009', 'Benjamin', 'Hampshire', 12, '008-22536178', '');

Section 2.1: Primary Key

A primary key is a candidate key selected by the database designer to uniquely identify records within a relation. The primary key must always contain unique values and cannot be null. While it is not mandatory to have a primary key, it is highly recommended to ensure data integrity.

Section 2.2: Foreign Key

A foreign key serves to link two tables, where an attribute or a set of attributes in one table references the primary key of another. This mechanism is vital for maintaining referential integrity, ensuring that the data remains consistent and accurate across the database.

Section 2.3: Candidate Key

A candidate key is an attribute or a set of attributes that can uniquely identify a tuple, and it must not contain any null values. Essentially, candidate keys are minimal super keys without any redundant attributes.

Section 2.4: Super Key

A super key comprises all possible attributes that can uniquely identify rows in a relation. It is a broader category that includes candidate keys, and a table can have multiple super keys, which may contain additional, unnecessary attributes.

Section 2.5: Alternate Key

Alternate keys are those candidate keys that were not selected as the primary key. In our Agents table, the AGENT_CODE is designated as the primary key, while all other attributes are considered alternate keys.

Section 2.6: Composite Key

A composite key consists of multiple attributes used together to identify a record uniquely. It is also referred to as a compound key and cannot include null values.