Name:-NAKSHATRA MISHRA
Reg No.:-21bce1499
SQL DDL and DML Commands Assignment:
Complete Implementation Guide
This comprehensive assignment provides hands-on experience with SQL Data Definition
Language (DDL) and Data Manipulation Language (DML) commands using MySQL .
[1][2]
The assignment covers database creation, table design, data manipulation, and
advanced querying techniques essential for database management .
Database Schema for University Database showing table relationships
Software Requirements and Installation
MySQL Installation Process
The assignment requires MySQL Server (version 8.0 or later recommended) and MySQL
Workbench for visual database management .Students must download the MySQL
Installer from the official MySQL website and choose the "Full" setup option during
installation .
The installation process involves configuring the MySQL Server with "Development
Computer" settings, setting a secure password for the root account, and ensuring the
server runs on the default port 3306 . After installation, students should launch
[8][12]
MySQL Workbench and create a connection to the local MySQL server using standard
TCP/IP protocol .
Database Schema Design
The assignment utilizes a university database system with five interconnected tables:
Students, Departments, Professors, Courses, and Enrollments . This design
[14][15]
demonstrates proper database normalization principles and referential integrity through
foreign key relationships .
The Students table serves as the primary entity storing student information with
constraints ensuring data quality through CHECK constraints on GPA values and UNIQUE
constraints on email addresses . The Departments table maintains institutional
[18][19]
structure, while the Professors table links faculty to their respective departments through
foreign key relationships .
DDL Commands Implementation
Database and Table Creation
The DDL implementation begins with creating the university database and selecting it for
use . The CREATE TABLE statements demonstrate various constraint types including
[1][22]
PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT constraints .
[18][23]
The table creation sequence follows dependency order: Departments and Students tables
first (no foreign key dependencies), followed by Professors (references Departments),
then Courses (references both Departments and Professors), and finally Enrollments
(references both Students and Courses) .
[15][16]
Table Modification Commands
ALTER TABLE commands demonstrate schema modification capabilities including adding
new columns, modifying existing column properties, and adding table-level constraints [24]
. These operations show how database schemas can evolve over time while
[25]
maintaining data integrity .
[4][5]
DML Commands Implementation
Data Insertion and Retrieval
The DML implementation starts with INSERT statements that populate all five tables with
sample data [26][2]
. The insertion order respects foreign key constraints, ensuring parent
table records exist before inserting child table records .
SELECT statements demonstrate various querying techniques including basic data
retrieval, conditional filtering using WHERE clauses, result ordering with ORDER BY, and
data grouping with GROUP BY and aggregate functions. These queries illustrate the
power of SQL for data analysis and reporting .
[29][30]
Data Modification and Deletion
UPDATE commands show how to modify existing data while respecting integrity
constraints . DELETE operations demonstrate selective data removal with proper
[26][7]
WHERE clause usage to avoid accidental data loss .
[31][32]
Step-by-Step Execution Guide
The assignment includes a detailed execution workflow that guides students through
each phase of implementation [35][37]
. The process is divided into five phases: setup and
installation, DDL commands implementation, DML commands implementation, advanced
queries, and documentation [6][37]
.
Each step includes specific time estimates, expected outputs, and screenshot
requirements to ensure comprehensive documentation . The guide provides
[35][12]
troubleshooting assistance for common errors including syntax errors, constraint
violations, and connection issues .
[31][27]
SQL Assignment Execution Workflow
Expected Outputs and Verification
Command Execution Results
Database creation commands should return "Database created successfully" messages,
while table creation produces "Table created successfully" confirmations . INSERT
[22][23]
statements generate "X row(s) affected" messages where X represents the number of
inserted records .
[26][2]
SELECT queries return result sets displaying requested data in tabular format, with
column headers and properly formatted values . UPDATE and DELETE operations
[28][30]
provide feedback on the number of affected rows, ensuring students can verify their
modifications .
[26][7]
DDL vs DML Commands Distribution in SQL Assignment
DML operations encompass 5 INSERT statements populating all tables, 7 different SELECT
query variations, 3 UPDATE commands, and 1 DELETE operation . Advanced features
[26][7]
include multiple JOIN types, subqueries, and aggregate functions with GROUP BY clauses
Result:
Successfully executed all DDL and DML commands on a sample database table.
Conclusion:
This experiment demonstrated the use of SQL DDL commands to define the structure of
a database and DML commands to manage the data stored in the database.
