Webinar Alert : Mastering  Manual and Automation Testing! - Reserve Your Free Seat Now

sddsfsf

SQL constraints & Boyce-Codd Normal Form: In-depth Tutorial

 

Discover the power of SQL the language that lets you store, manipulate, and retrieve data effortlessly. It's the gold standard for managing databases. Get ready to harness the potential of constraints in SQL to handle and describe your data seamlessly. Learn to create table constraints in SQL and check constraint in SQL. Additionally, also learn about Boyce Codd standard form and various constraints. 

Different Types of Constraints in SQL

We will learn about how to create constraint SQL server and different types of SQL Constraints.

  • NOT NULL - This table constraint in SQL ensures that a column cannot have a NULL value

The syntax for implementing null constraint in a table using T-SQL is as follows

The syntax for implementing null constraint in a table using T-SQL is as follows

CREATE TABLE Persons (

ID int NOT NULL,

    LastName varchar(255) NOT NULL,

FirstName varchar(255) NOT NULL,

Age int

             );

The output looks like below

  • UNIQUE - This table constraint in SQL ensures that all values in a column are different

The syntax for unique constraints is as follows

CREATE TABLE Persons1 (

ID int NOT NULL UNIQUE,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int

);

The output looks like below

  • PRIMARY KEY - This constraint in SQL Server is a mix of both NOT NULL and UNIQUE. Uniquely identifies each row in a table

The syntax for declaring the primary key is as below

CREATE TABLE Persons2 (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

CONSTRAINT PK_Person PRIMARY KEY (ID, LastName)

);

The output looks like below

  • FOREIGN KEY - This SQL Constraint in dbms prevents actions that would destroy links between tables

The syntax for creating the foreign key is as below

CREATE TABLE Orders (

OrderID int NOT NULL PRIMARY KEY,

OrderNumber int NOT NULL,

PersonID int FOREIGN KEY REFERENCES Persons(PersonID)

);
  • CHECK - Check constraints in dbms makes sure that the values in a column satisfy a specific condition

The syntax for check constraint is as below

CREATE TABLE Persons5 (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int CHECK (Age>=18)

);

The output is as below

  • DEFAULT - This SQL Constraint sets a default value for a column if no value is specified

The syntax for the default constraint is as below

CREATE TABLE Persons6 (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

City varchar(255) DEFAULT 'Sandnes'

);

The output is as below

The syntax for creating an index is as below

CREATE INDEX index_name
ON table_name (column1, column2, ...);

In the above section, we learned about different create constraint in SQL Server. Next, we will learn about the advantages and disadvantages of database constraints in SQL.

Advantages of MySQL Constraints

The following are some of the benefits of SQL Constraints in DBMS: 

  • Prevent incorrect data entry into relevant tables.
  • Require database-level enforcement of business logic.
  • Detailed documentation of crucial database rules
  • Ensure that all tables are in a relational integrity state.
  • Make the database work better.
  • Reinforce individuality.

What is Normalisation?

The process of organizing data in a database is known as normalization. It includes forming tables and establishing relationships between them by rules that safeguard the data and make the database more adaptable by removing redundant and inconsistent dependencies. A vast database defined as a single relation may turn into data duplication. This repetition of data may result in:

  • Creating extensive relations.
  • Maintaining and updating data is problematic because it requires searching numerous related records.
  • Inefficient use of disk space and resources and waste
  • There is a greater chance of mistakes and inconsistencies.

Therefore, to deal with these issues, we ought to analyze and break down the redundant data into smaller, more straightforward, and well-structured relations with desirable properties. Normalization is the process of breaking down relationships into more manageable subsets.

In short, normalization means:

  • The process of organizing the database's data is known as normalization.
  • Normalization is used to reduce redundant relationships within a set. Additionally, Insertion, Update, and Deletion Anomalies are among the undesirable characteristics used to eliminate them.
  • Normalization uses relationships to connect the minor part of a larger table.
  • The database table's redundancy is reduced by using the standard form.

Why do We Need Normalization?

Eliminating these anomalies is the primary goal of normalizing the relationships. As the database grows, data redundancy, issues with data integrity, and other issues can arise if anomalies are not removed. A set of guidelines called "normalization" will help you put together a good database structure. One such normalization is the Boyce Codd Normal Form or BCNF. We'll talk about BCNF next.

What is Boyce Codd's Normal Form

In database normalization, the Boyce–Codd normal form (also known as BCNF or 3.5NF) is utilized. The third normal form is slightly stronger in this form. Raymond F. Boyce and Edgar F. Codd developed the BCNF in 1974 to deal with certain kinds of anomalies that the 3NF didn't cover in its original definition.

All redundancy based on functional dependency has been eliminated if a relational schema is in BCNF, although other types of redundancy may still exist. A relational schema R is in Boyce–Codd normal form if and only if at least one of the following is valid for each of its dependencies X  Y.

Boyce Codd Normal Form with Example

The more advanced variant of 3NF is called BCNF (Boyce Codd Normal Form). If every functional dependency in a table is X->Y, and X is the table's super key, then the table is in BCNF. The table should be in 3NF for BCNF and each FD. LHS is crucial.

A table is said to be in BCNF form when

  • Must be in 3NF
  • ∀ A->B, A should be Super Key.

Example

Consider a relation R with attributes (student, subject, teacher).

 

F: { (student, Teacher) -> subject

(student, subject) -> Teacher

Teacher -> subject}

Candidate keys are (student, teacher) and (student, subject).

Since there is no transitive dependency, the above relation is in 3NF. If X must be a key for each non-trivial FD X->Y, then a relation R is in BCNF.

Because the teacher is not a key in the FD (teacher->subject), the aforementioned relationship does not exist in BCNF. This relationship has anomalies. For instance, if we attempt to delete the student Subbu, we will lose the knowledge that R. Prasad teaches C. These issues are brought on by the fact that the teacher is a determinant but is not a candidate key.

Advantages of BCNF (BOYCE CODD Normal Form)

  • It is a more restricted form of normalization to prevent anomalies from appearing in the database.
  • Keys are used to enforce the business rules expressed in functional dependencies, and BCNF ensures that they are followed correctly.

SQL Training For Administrators & Developers

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available
cta13 icon

Conclusion

Over the last few paragraphs, we learned about different types of constraints and how to create a table in SQL with constraints. We also learned about the advantages of BCNF (Boyce Codd Normal Form). Hope this can be a building block for the students who want to study more about different types of Constraints in a database and BCNF.

Trending Courses

Cyber Security icon

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security icon1

Upcoming Class

17 days 02 Nov 2024

QA icon

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA icon1

Upcoming Class

10 days 26 Oct 2024

Salesforce icon

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce icon1

Upcoming Class

2 days 18 Oct 2024

Business Analyst icon

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst icon1

Upcoming Class

2 days 18 Oct 2024

MS SQL Server icon

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server icon1

Upcoming Class

2 days 18 Oct 2024

Data Science icon

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science icon1

Upcoming Class

9 days 25 Oct 2024

DevOps icon

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps icon1

Upcoming Class

2 days 18 Oct 2024

Hadoop icon

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop icon1

Upcoming Class

9 days 25 Oct 2024

Python icon

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python icon1

Upcoming Class

3 days 19 Oct 2024

Artificial Intelligence icon

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence icon1

Upcoming Class

17 days 02 Nov 2024

Machine Learning icon

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning icon1

Upcoming Class

30 days 15 Nov 2024

 Tableau icon

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau icon1

Upcoming Class

9 days 25 Oct 2024