SQL-Based Implementation and Data Loading of Company Database Schema
Abstract
<div> <h2>Design and Implementation of Relational Database Schema Using SQL DDL Statements</h2> <p><strong>Creating Tables on the Company Schema: Department, Dependent, Employees, Projects, WorksOn, and Location</strong></p> <h3>Definition of Department Table Structure</h3> <p>CREATE TABLE Department ( Dnumber INT PRIMARY KEY, Dname VARCHAR(50), Mgr_ssn CHAR(9), Mgr_start_date DATE );</p> <h3>Definition of Employee Table Structure</h3> <p>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 );</p> <h3>Definition of Project Table Structure</h3> <p>CREATE TABLE Project ( Pnumber INT PRIMARY KEY, Pname VARCHAR(50), Plocation VARCHAR(50), Dnum INT );</p> <h3>Definition of WorksOn Table Structure</h3> <p>CREATE TABLE WorksOn ( Essn CHAR(9), Pno INT, Hours DECIMAL(5,2), PRIMARY KEY (Essn, Pno) );</p> <h3>Definition of Dependent Table Structure</h3> <p>CREATE TABLE Dependent ( Essn CHAR(9), Dependent_name VARCHAR(50), Sex CHAR(1), Bdate DATE, Relationship VARCHAR(25), PRIMARY KEY (Essn, Dependent_name) );</p> <h3>Definition of Location Table Structure</h3> <p>CREATE TABLE Location ( Dnumber INT, Dlocation VARCHAR(50), PRIMARY KEY (Dnumber, Dlocation) );</p> <h3>Establishing Referential Integrity Using ALTER TABLE Statements</h3> <p>ALTER TABLE Employee ADD FOREIGN KEY (Dno) REFERENCES Department(Dnumber);</p> <p>ALTER TABLE Employee ADD FOREIGN KEY (Super_ssn) REFERENCES Employee(Ssn);</p> <p>ALTER TABLE Department ADD FOREIGN KEY (Mgr_ssn) REFERENCES Employee(Ssn);</p> <p>ALTER TABLE Project ADD FOREIGN KEY (Dnum) REFERENCES Department(Dnumber);</p> <p>ALTER TABLE WorksOn ADD FOREIGN KEY (Essn) REFERENCES Employee(Ssn);</p> <p>ALTER TABLE WorksOn ADD FOREIGN KEY (Pno) REFERENCES Project(Pnumber);</p> <p>ALTER TABLE Dependent ADD FOREIGN KEY (Essn) REFERENCES Employee(Ssn);</p> <p>ALTER TABLE Location ADD FOREIGN KEY (Dnumber) REFERENCES Department(Dnumber);</p> <h2>Data Population of Database Tables Using MySQL LOAD Command</h2> <p><strong>Loading Data to the Location Table</strong></p> <p>LOAD DATA INFILE 'location.dat' INTO TABLE Location FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';</p> <p><strong>Loading Data to the Project Table</strong></p> <p>LOAD DATA INFILE 'project.dat' INTO TABLE Project FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';</p> <p><strong>Loading Data to the WorksOn Table</strong></p> <p>LOAD DATA INFILE 'workson.dat' INTO TABLE WorksOn FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';</p> <p><strong>Loading Data to the Department Table</strong></p> <p>LOAD DATA INFILE 'department.dat' INTO TABLE Department FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';</p> <p><strong>Loading Data to the Dependent Table</strong></p> <p>LOAD DATA INFILE 'dependent.dat' INTO TABLE Dependent FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';</p> <p><strong>Loading Data to the Employee Table</strong></p> <p>LOAD DATA INFILE 'employee.dat' INTO TABLE Employee FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';</p> <h2>Verification of Data Integrity through SQL Query Execution</h2> <p>SELECT * FROM Location;</p> <p>SELECT * FROM Project;</p> <p>SELECT * FROM WorksOn;</p> <p>SELECT * FROM Department;</p> <p>SELECT * FROM Dependent;</p> <p>SELECT * FROM Employee;</p> <h2>Integrated Evaluation of Database Creation and Data Loading Process</h2> <p><strong>Conclusion</strong></p> <p>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.</p> </div>