27. Designing of tables, applying DML statements, enforcing constraints.
V E S R N TECHNOLOGIES
Designing of tables, applying DML statements, enforcing constraints.
AimTo design database tables, apply DML commands and enforce constraints using MySQL.
Requirements
Computer system
MySQL / XAMPP / phpMyAdmin
Part – 1 : Create Database
CREATE DATABASE iti_college;
USE iti_college;
Part – 2 : Designing Tables (DDL Commands)
Create Department Table CREATE TABLE department ( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(50) NOT NULL UNIQUE );
Create Student Table
CREATE TABLE student ( stu_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, trade VARCHAR(30), marksINTCHECK (marks>=0 AND marks<=100),dept_id INT, FOREIGN KEY (dept_id) REFERENCES department(dept_id));
Part – 3 : Apply DML Statements
INSERT
INSERT INTO department(dept_name) VALUES ('Electrician'),('Fitter'),('Welder');
INSERT INTO student(name,trade,marks,dept_id) VALUES ('Raju','Electrician',78,1), ('Kumar','Fitter',82,2), ('Ravi','Welder',69,3);
SELECT
SELECT * FROM student;
UPDATE UPDATE student SET marks = 85 WHERE name='Raju';
DELETE
DELETE FROM student WHERE stu_id=3;
Part – 4 : Enforcing Constraints
Constraint Purpose
PRIMARY KEY Unique record identification
NOT NULL Prevent empty value
UNIQUE No duplicate data
CHECK Limit value range
FOREIGN KEY Maintain relationship
Example Violations
Duplicate Unique Value
INSERT INTO department(dept_name) VALUES ('Electrician'); -- Error occurs
Invalid Marks
INSERT INTO student(name,trade,marks,dept_id) VALUES('Test','Electrician',150,1);
-- CHECK constraint fails
Result Tables designed successfully. DML operations performed and constraints enforced properly.
Precautions
Insert parent table data before child table
Always use WHERE in UPDATE & DELETE
Follow correct data types
I practiced all under my trainer Venugopal Sir.
