Navigating Database Management: DDL Commands and Database Design Fundamentals

Table of Contents

1. Introduction
2. Data Definition Language (DDL)

• 2.1. Key DDL Commands

3. Database Structures

• 3.1. Ali Database
• 3.2. University Database
• 3.3. Company Database

4. Conclusion

1. Introduction

This document outlines the structure and commands for managing three databases: Ali, University, and Company. It covers the Data Definition Language (DDL) commands for creating, altering, and deleting database objects, and includes SQL commands for each database’s tables.

2. Data Definition Language (DDL)

Data Definition Language (DDL) consists of SQL commands that define and manage database objects such as tables, indexes, and schemas. These commands help manage the structure and organization of data in the database.

2.1. Key DDL Commands

• CREATE: Used to create a new database object, such as a table, index, or database.
• ALTER: Used to modify an existing database object (e.g., adding columns to a table).
• DROP: Used to delete a database object (e.g., removing a table or database).
• TRUNCATE: Used to remove all records from a table but retain the table structure.
• RENAME: Used to change the name of a database object.

3. Database Structures

3.1. Ali Database

Purpose: To store and manage basic student information.

Table: info

• Columns:
• id (INT, PRIMARY KEY)
• std_name (VARCHAR (30))
• age (INT)
• City (VARCHAR (30))

To retrieve the current date and time from the database server.

See the version of the MySQL and see it in the result grid:

SQL Commands for Creating the Ali Database:

Inserting Data:

Selecting Data:

Query: SELECT * FROM info;

Or used another command for specific details of it

SELECT * FROM info WHERE age = 25;

SELECT * FROM info WHERE std_name = ‘HAMZA’;

SELECT std_name FROM info WHERE id BETWEEN 1 AND 5 AND age = 20;

SELECT id, std_name, age FROM info WHERE id != 5 AND age = 30;

Altering Table:

Add a new column city:

Query: ALTER TABLE info ADD COLUMN city VARCHAR (30);

Update city information:

Query: UPDATE info

SET city = CASE

WHEN id = 1 THEN “Birmingham”

WHEN id = 2 THEN “Leeds”

WHEN id = 3 THEN “Manchester”

WHEN id = 4 THEN “Sheffield”

WHEN id = 5 THEN “Bradford”

END

WHERE id IN (1, 2, 3, 4, 5);

After using a select command:

Change column name:
ALTER TABLE info CHANGE city city_name VARCHAR (30);

Rename the table:

RENAME TABLE info TO student_info;

3.2. University Database

Purpose: Manage student, course, and section information for a university.

Tables:

student: Information about students.

• std_name (VARCHAR (30)): Name of the student.
• student_number (INT, PRIMARY KEY): Unique identifier for each student.
• class (INT): Class level of the student.
• major (VARCHAR (30)): Major field of study.

Show the data using a select query:

course: Information about courses.

• course_name (VARCHAR (30)): Name of the course.
• course_number (INT, PRIMARY KEY): Unique identifier for the course.
• credits_hours (INT): Credit hours assigned to the course.
• department (VARCHAR (30): Department offering the course.

Show the data using a select query:

section: Sections of the courses.

• section_identifier (INT, PRIMARY KEY): Unique identifier for the course section.
• course_number (INT): Course number related to this section.
• semester (VARCHAR (10)): Semester when the course is offered.
• year_of (INT): Year when the course is offered.
• instructor (VARCHAR (30)): Name of the instructor for the section.

Show the data using a select query:

grade_report: Records of student grades.

• student_number (INT): Student’s unique identifier.
• section_identifier (INT): Identifier for the course section.
• grade (VARCHAR (5)): Grade received by the student.

Show the data using a select query:

prereqt: Course prerequisites.

• course_number (VARCHAR (30): The course number.
• prerequisite_number (VARCHAR (30)): The prerequisite course number.

Show the data using a select query:

Show the Table Command:

3.3. Company Database (Schema)

Purpose: To manage employees, department, and project information.

Tables:

EMPLOYEE: Information about employees.

• Fname (VARCHAR (30)): First name of the employee.
• Minit (CHAR (1)): Middle initial of the employee.
• Lname (VARCHAR (30)): Last name of the employee.
• Ssn (CHAR (9), PRIMARY KEY): Social Security Number, unique identifier for each employee.
• Bdate (DATE): Birthdate of the employee.
• Address (VARCHAR (100)): Address of the employee.
• gender (CHAR (1)): Gender of the employee (‘M’ for Male, ‘F’ for Female).
• Salary (DECIMAL (10, 2)): Salary of the employee.

DEPARTMENT: Information about company departments.

• Dname (VARCHAR(30)): Name of the department.
• Dnumber (INT, PRIMARY KEY): Unique number identifying the department.
• Mgr_ssn (CHAR(9)): Social Security Number of the department manager.
• Mgr_start_date (DATE): Date when the manager started managing the department.

DEPT_LOCATIONS: Locations of the company’s departments.

• Dnumber (INT): Department number (foreign key from the DEPARTMENT table).
• Dlocation (VARCHAR(50)): Location of the department.

PROJECT: Information about company projects.

• Pname (VARCHAR (30)): Name of the project.
• Pnumber (INT, PRIMARY KEY): Unique number identifying the project.
• Plocation (VARCHAR (50): Location where the project is being carried out.
• Dnum (INT): Department number responsible for the project.

• Essn (CHAR (9)): Social Security Number of the employee (foreign key from EMPLOYEE).
• Pno (INT): Project number (foreign key from PROJECT).
• Hours (DECIMAL (4, 2)): Number of hours worked on the project.

DEPENDENT: Information about employee dependents.

• Essn (CHAR (9)): Social Security Number of the employee (foreign key from EMPLOYEE).
• Dependent_name (VARCHAR (30)): Name of the dependent.
• Sex (CHAR (1)): Gender of the dependent (‘M’ for Male, ‘F’ for Female).
• Bdate (DATE): Birthdate of the dependent.
• Relationship (VARCHAR (20): Relationship of the dependent to the employee.

Conclusion

This document has outlined the use of Data Definition Language (DDL) commands to create, modify, and manage three distinct databases: Ali, University, and Company. Through examples of SQL queries, we demonstrated how to efficiently handle database structures and relationships. Whether managing student data, academic courses, or company employees, the principles covered enable effective database creation, manipulation, and querying, ensuring organized and accessible data management across various scenarios.