View Full Paper

Design and Implementation of Relational Database Schema Using SQL DDL Statements

Creating Tables on the Company Schema: Department, Dependent, Employees, Projects, WorksOn, and Location

Definition of Department Table Structure

CREATE TABLE Department ( Dnumber INT PRIMARY KEY, Dname VARCHAR(50), Mgr_ssn CHAR(9), Mgr_start_date DATE );

Definition of Employee Table Structure

CREATE TABLE Employee ( Ssn CHAR(9) PRIMARY KEY, Fname VARCHAR(20), Lname VARCHAR(20), Bdate DATE, Address VARCHAR(100), Sex CHAR(1), Salary DECIMAL(10,2), Super_ssn CHAR(9), Dno INT );

Definition of Project Table Structure

CREATE TABLE Project ( Pnumber INT PRIMARY KEY, Pname VARCHAR(50), Plocation VARCHAR(50), Dnum INT );

Definition of WorksOn Table Structure

CREATE TABLE WorksOn ( Essn CHAR(9), Pno INT, Hours DECIMAL(5,2), PRIMARY KEY (Essn, Pno) );

Definition of Dependent Table Structure

CREATE TABLE Dependent ( Essn CHAR(9), Dependent_name VARCHAR(50), Sex CHAR(1), Bdate DATE, Relationship VARCHAR(25), PRIMARY KEY (Essn, Dependent_name) );

Definition of Location Table Structure

CREATE TABLE Location ( Dnumber INT, Dlocation VARCHAR(50), PRIMARY KEY (Dnumber, Dlocation) );

Establishing Referential Integrity Using ALTER TABLE Statements

ALTER TABLE Employee ADD FOREIGN KEY (Dno) REFERENCES Department(Dnumber);

ALTER TABLE Employee ADD FOREIGN KEY (Super_ssn) REFERENCES Employee(Ssn);

ALTER TABLE Department ADD FOREIGN KEY (Mgr_ssn) REFERENCES Employee(Ssn);

ALTER TABLE Project ADD FOREIGN KEY (Dnum) REFERENCES Department(Dnumber);

ALTER TABLE WorksOn ADD FOREIGN KEY (Essn) REFERENCES Employee(Ssn);

ALTER TABLE WorksOn ADD FOREIGN KEY (Pno) REFERENCES Project(Pnumber);

ALTER TABLE Dependent ADD FOREIGN KEY (Essn) REFERENCES Employee(Ssn);

ALTER TABLE Location ADD FOREIGN KEY (Dnumber) REFERENCES Department(Dnumber);

Data Population of Database Tables Using MySQL LOAD Command

Loading Data to the Location Table

LOAD DATA INFILE 'location.dat' INTO TABLE Location FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Loading Data to the Project Table

LOAD DATA INFILE 'project.dat' INTO TABLE Project FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Loading Data to the WorksOn Table

LOAD DATA INFILE 'workson.dat' INTO TABLE WorksOn FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Loading Data to the Department Table

LOAD DATA INFILE 'department.dat' INTO TABLE Department FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Loading Data to the Dependent Table

LOAD DATA INFILE 'dependent.dat' INTO TABLE Dependent FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Loading Data to the Employee Table

LOAD DATA INFILE 'employee.dat' INTO TABLE Employee FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Verification of Data Integrity through SQL Query Execution

SELECT * FROM Location;

SELECT * FROM Project;

SELECT * FROM WorksOn;

SELECT * FROM Department;

SELECT * FROM Dependent;

SELECT * FROM Employee;

Integrated Evaluation of Database Creation and Data Loading Process

Conclusion

The implementation of the company database schema demonstrates the use of SQL for defining relational structures and enforcing referential integrity. The use of LOAD DATA commands enables efficient population of tables with large datasets. Verification through SELECT queries confirms successful data insertion and ensures the reliability of the database for further operations.