1

IS3346 Construction Company Database Smith

Database Design Report

IS 3346

Professor: Sun

Samantha

Due Date: 11/24/2024

1
2
IS3346 Construction Company Database Smith

Database Design Report

1. Introduction

 Purpose: The database design report aims to outline the structure and relationships of the

database for a Civil Engineering Construction Company. The database will cover project

management, resource allocation, supplier and client relationships, and financial

oversight.

 Scope: This report includes the conceptual, logical, and physical design of the database.

 Audience: This report is intended for my professor, database administrators, developers,

and project managers.

2. Database Requirements

 Functional Requirements:

o Provide data retrieval for F&W Construction.

o Enable efficient tracking and management of project information.

o Facilitate resource allocation and financial oversight.

o Regulate contractor and client information.

o Stay up to date on the status of needed equipment and materials.

 Non-Functional Requirements:

o Data must be protected with access controls and encryption.

o Should ensure fast query processing, even with large volumes of data.

o Multiple users should be able to access the database.

o Should have regular backups and recovery options.

2
3
IS3346 Construction Company Database Smith

3. Conceptual Design

Figure 1

Entity Relationship Diagram

Note. The figure shown above is an Entity Relationship Diagram for F and W Construction Company.

3
4
IS3346 Construction Company Database Smith

 Entities:

o Client: Stores clients' names and important information.

o Contractor: Records contractor information.

o Equipment: Identifies equipment by its specs and status.

o Project: Captures project details that are the backbone of operations.

o Task: Tracks tasks the contractors are conducting.

o Supplier: Holds supplier details for contacting and locating them.

o Material: Stores each materials specs and costs.

o Sources: Defines the quantity of materials or equipment provided from each

source.

o EquipmentUsage: Logs equipment usage by its duration of use.

o Invoice: Tracks each invoice to ensure correct payment.

 Attributes:

Table 1

Project

Name Datatype PK NN UQ B UN ZF AI G Memo

projectID INT Y Y
projectName VARCHAR(50) Y
startDate DATE Y
endDate DATE Y
projectBudget DECIMAL(20,2) Y price
projectStatus VARCHAR(50) Y

Table 2

Client

Name Datatype PK NN UQ B UN ZF AI G Memo

4
5
IS3346 Construction Company Database Smith

clientID INT Y Y
clientName VARCHAR(50) Y
phone VARCHAR(50) Y
addressLine1 VARCHAR(50) Y
addressLine2 VARCHAR(50)
city VARCHAR(50) Y
state VARCHAR(50) Y
postalCode VARCHAR(50) zip
code
country VARCHAR(50) Y

Table 3

Contractor

Name Datatype PK NN UQ B UN ZF AI G Memo

contractorID INT Y Y
contractorName VARCHAR(50) Y
phone VARCHAR(50) Y
addressLine1 VARCHAR(50) Y
addressLine2 VARCHAR(50)
city VARCHAR(50) Y
state VARCHAR(50) Y
postalCode VARCHAR(50) zip
code
country VARCHAR(50) Y

Table 4

Task

Name Datatype PK NN UQ B UN ZF AI G Memo

taskID INT Y Y
taskName VARCHAR(50) Y
description TEXT(2000)
startDate DATE Y
endDate DATE Y
taskStatus VARCHAR(50) Y

Table 5

5
6
IS3346 Construction Company Database Smith

Equipment

Name Datatype PK NN UQ B UN ZF AI G Memo

equipmentID INT Y Y
equipmentNam VARCHAR(50) Y
e
equipmentType VARCHAR(50)
purchaseDate DATE Y
equipmentCost DECIMAL(20,2 Y Price
)
equipmentStatu VARCHAR(50) Y
s

Table 6

Equipment Usage

Name Datatype PK NN UQ B UN ZF AI G Memo

equiptmentID INT Y Y
taskID INT Y Y
equiptmentUsage VARCHAR(50) Y

Table 7

Material

Name Datatype PK NN UQ B UN ZF AI G Memo

materialID INT Y Y
materialName VARCHAR(50) Y
materialType VARCHAR(50)
materialCost DECIMAL(20,2) Y price

Table 8

Supplier

Name Datatype PK NN UQ B UN ZF AI G Memo

supplierID INT Y Y
supplierName VARCHAR(50) Y
phone VARCHAR(50) Y
addressLine1 VARCHAR(50) Y

6
7
IS3346 Construction Company Database Smith

addressLine2 VARCHAR(50)
city VARCHAR(50) Y
state VARCHAR(50)
postalCode VARCHAR(50) zip
code
country VARCHAR(50) Y

Table 9

Sources

Name Datatype PK NN UQ B UN ZF AI G Memo

taskID INT Y Y
materialID INT Y Y
quantity DECIMAL(10,2) Y amount

Table 10

Invoice

Name Datatype PK NN UQ B UN ZF AI G Memo

invoiceID INT Y Y
invoiceAmoun DECIMAL(20,2) Y
t
invoiceDate DATE Y
paymentStatus VARCHAR(50) Y

Note. Table 1-10 is an insertion of my Entity Table.

 Relationships:

o A Client commissions multiple Projects.

o A Project generates multiple Invoices.

o A Project includes multiple Tasks.

o A Contractor works on multiple Tasks.

o Tasks can use multiple Sources.

o Material contains multiple Sources.

7
8
IS3346 Construction Company Database Smith

o A Supplier provides multiple Materials.

o A certain Task shows many of its EquipmentUsages.

o Equipment has multiple EquipmentUsages.

4. Logical Design

 Schema Definition:

CREATE TABLE Client (

ClientID INT PRIMARY KEY,

ClientName VARCHAR(50),

Phone VARCHAR(50),

AddressLine1 VARCHAR(50),

AddressLine2 VARCHAR(50),

City VARCHAR(50),

State VARCHAR(50),

PostalCode VARCHAR(50),

Country VARCHAR(50)

);

CREATE TABLE Contractor (

ContractorID INT PRIMARY KEY,

ContractorName VARCHAR(50),

Phone VARCHAR(50),

AddressLine1 VARCHAR(50),

AddressLine2 VARCHAR(50),

City VARCHAR(50),

State VARCHAR(50),

PostalCode VARCHAR(50),

Country VARCHAR(50)

8
9
IS3346 Construction Company Database Smith

);

CREATE TABLE Equipment (

EquipmentID INT PRIMARY KEY,

EquipmentName VARCHAR(50),

EquipmentType VARCHAR(50),

PurchaseDate DATE,

EquipmentCost DECIMAL(20,2),

EquipmentStatus VARCHAR(50)

);

CREATE TABLE Project (

ProjectID INT PRIMARY KEY,

ProjectName VARCHAR(50),

StartDate DATE,

EndDate DATE,

ProjectBudget DECIMAL(20,2)

ClientID INT,

FOREIGN KEY(ClientID) REFRENCES Client(ClientID)

);

CREATE TABLE Task (

TaskID INT PRIMARY KEY,

TextName VARCHAR(50),

Description TEXT(2000)

StartDate DATE,

EndDate DATE,

TaskStatus VARCHAR(50),

ProjectID INT,

ContractorID,

FOREIGN KEY(ProjectID) REFRENCES Project(ProjectID)

FOREIGN KEY(ContractorID) REFRENCES Contarctor(ContractorID)

9
10
IS3346 Construction Company Database Smith

);

CREATE TABLE Supplier (

SupplierID INT PRIMARY KEY,

SupplierName VARCHAR(50),

Phone VARCHAR(50),

AddressLine1 VARCHAR(50),

AddressLine 2 VARCHAR(50),

City VARCHAR(50),

State VARCHAR(50),

PostalCode VARCHAR(50),

Country VARCHAR(50)

);

CREATE TABLE Material (

Material ID INT PRIMARY KEY,

MaterialName VARCHAR(50),

MaterialType VARCHAR(50),

MaterialCost DECIMAL(20,2),

SupplierID INT,

FOREIGN KEY(SupplierID) REFRENCES Supplier(SupplierID)

);

CREATE TABLE Sources (

TaskID INT PRIMARY KEY,

MaterialID INT PRIMARY KEY,

Quantity Decimal(20,2),

FOREIGN KEY(TaskID) REFRENCES Task(TaskID)

);

CREATE TABLE EquipmentUsage (

EquipmentID INT PRIMARY KEY,

TaskID INT PRIMARY KEY,

10
11
IS3346 Construction Company Database Smith

EquipmentUsage VARCHAR(50),

FOREIGN KEY(EquipmentID) REFRENCES Equipment(EquipmentID),

FOREIGN KEY(TaskD) REFRENCES Task(TaskID)

);

CREATE TABLE Invoice (

InvoiceID INT PRIMARY KEY,

InvoiceAmount DECIMAL(20,2),

InvoiceDate DATE,

PaymentStatus VARCHAR(50),

ProjectID INT,

FOREIGN KEY(ProjectID) REFRENCES Project(ProjectID)

);

 Primary Keys:

o Client: ClientID

o Contractor: ContractorID

o Equipment: EquipmentID

o Project: ProjectID

o Task: TaskID

o Supplier: SupplierID

o Material: MaterialID

o Sources: TaskID MaterialID

o EquipmentUsage: EquipmentID TaskID

o Invoice: InvoiceID

 Foreign Keys:

o Project: ClientID references Client(ClientID)

11
12
IS3346 Construction Company Database Smith

o Task: ProjectID references Project(ProjectID), ContractorID references

Contractor(ContractorID)

o Material: SupplierID references Supplier(SupplierID)

o Sources: TaskID references Task(TaskID), MaterialID references

Material(MaterialID)

o EquipmentUsage: EquipmentID references Equipment(EquipmentID), TaskID

references Task(TaskID)

o Invoice: ProjectID references Project(ProjectID)

5. Physical Design

Table Structure:

-- Client table

CREATE TABLE `Client` (

`clientID` int NOT NULL PRIMARY KEY

`clientName` varchar(50) NOT NULL,

`phone` varchar(50) NOT NULL,

`addressLine1` varchar(50) NOT NULL,

`addressLine2` varchar(50) DEFAULT NULL,

`city` varchar(50) NOT NULL,

`state` varchar(50) NOT NULL,

`postalCode` varchar(50) DEFAULT NULL,

`country` varchar(50) NOT NULL

);

-- Contractor table

CREATE TABLE `Contractor` (

`contractorID` int NOT NULL PRIMARY KEY,

12
13
IS3346 Construction Company Database Smith

`contractorName` varchar(50) NOT NULL,

`phone` varchar(50) NOT NULL,

`addressLine1` varchar(50) NOT NULL,

`addressLine2` varchar(50) DEFAULT NULL,

`city` varchar(50) NOT NULL,

`state` varchar(50) NOT NULL,

`postalCode` varchar(50) DEFAULT NULL,

`country` varchar(50) NOT NULL

);

-- Equipment table

CREATE TABLE `Equipment` (

`equipmentID` int NOT NULL PRIMARY KEY,

`equipmentName` varchar(50) NOT NULL,

`equipmentType` varchar(50) DEFAULT NULL,

`purchaseDate` date NOT NULL,

`equipmentCost` decimal(20,2) NOT NULL,

`equpimentStatus` varchar(50) NOT NULL

);

-- Project table

CREATE TABLE `Project` (

`projectID` int NOT NULL PRIMARY KEY,

`projectName` varchar(50) NOT NULL,

`startDate` date NOT NULL,

`endDate` date NOT NULL,

`projectBudget` decimal(20,2) NOT NULL,

`projectStatus` varchar(50) NOT NULL,

`clientID` int NOT NULL,

FOREIGN KEY (`clientID`) REFERENCES `Client` (`clientID`)

);

13
14
IS3346 Construction Company Database Smith

-- Task table

CREATE TABLE `Task` (

`taskID` int NOT NULL PRIMARY KEY,

`taskName` varchar(50) NOT NULL,

`description` text,

`startDate` date NOT NULL,

`endDate` date NOT NULL,

`taskStatus` varchar(50) NOT NULL,

`projectID` int NOT NULL,

`contractorID` int NOT NULL,

`projectID` int NOT NULL,

`contractorID` int NOT NULL,

FOREIGN KEY (‘projectID’) REFERENCES ‘Project’(projectID)

FOREIGN KEY (‘contractorID’) REFERENCES ‘Contractor’(contractorID)

);

-- Supplier table

CREATE TABLE `Supplier` (

`supplierID` int NOT NULL PRIMARY KEY,

`supplierName` varchar(50) NOT NULL,

`phone` varchar(50) NOT NULL,

`addressLine1` varchar(50) NOT NULL,

`addressLine2` varchar(50) DEFAULT NULL,

`city` varchar(50) NOT NULL,

`state` varchar(50) NOT NULL,

`postalCode` varchar(50) DEFAULT NULL,

`country` varchar(50) NOT NULL

);

-- Material table

CREATE TABLE `Material` (

14
15
IS3346 Construction Company Database Smith

`materialID` int NOT NULL PRIMARY KEY,

`materialName` varchar(50) NOT NULL,

`materialType` varchar(50) DEFAULT NULL,

`materialCost` decimal(20,2) NOT NULL,

`supplierID` int NOT NULL,

FOREIGN KEY (`supplierID`) REFERENCES `Supplier` (`supplierID`)

);

-- Sources table

CREATE TABLE `Sources` (

`taskID` int NOT NULL PRIMARY KEY,

`materialID` int NOT NULL PRIMARY KEY,

`quantity` decimal(10,2) NOT NULL,

FOREIGN KEY (`taskID`) REFERENCES `Task` (`taskID`),

FOREIGN KEY (`materialID`) REFERENCES `Material` (`materialID`)

);

-- EquipmentUsage table

CREATE TABLE `EquipmentUsage` (

`equipmentID` int NOT NULL PRIMARY KEY,

`taskID` int NOT NULL PRIMARY KEY,

`equipmentUsage` varchar(50) DEFAULT NULL,

FOREIGN KEY (`equipmentID`) REFERENCES `Equipment` (`equipmentID`),

FOREIGN KEY (`taskID`) REFERENCES `Task` (`taskID`)

);

-- Invoices table

CREATE TABLE `Invoice` (

`invoiceID` int NOT NULL PRIMARY KEY,

`invoiceAmount` decimal(20,2) NOT NULL,

`invoiceDate` date NOT NULL,

`paymentStatus` varchar(50) NOT NULL,

15
16
IS3346 Construction Company Database Smith

`projectID` int DEFAULT NULL,

FOREIGN KEY (`projectID`) REFERENCES `Project` (`projectID`)

);

 Indexing:

o Indexes on Client(Phone) to speed up contact look ups.

o Indexes on Contractor(City) to quickly filter them by location.

o Indexes on Equipment(EquipmentStatus) to filter by availability.

o Indexes on Project(StartDate) to allow date-based project tracking.

o Indexes on Task(TaskStatus) for status-based filtering.

o Indexed on Supplier(SupplierName) to speed up supplier look ups.

o Indexes on Material(MaterialType) for material type filtering.

o Indexes on Sources(TaskID) for task-specific material usage tracking.

o Indexes on EquipmentUsage(EquipmentID) for equipment usage lookup.

o Indexes on Invoices(PaymentStatus) for filtering unpaid invoices.

 Storage:

o The database will be stored on a MySQL server with a storage engine optimized

for transactional processing.

6. Data Dictionary

 Table Definitions:

o Client:

 ClientD: INT, Primary Key

 ClientName: VARCHAR(50)

16
17
IS3346 Construction Company Database Smith

 Phone: VARCHAR(50)

 AddressLine1: VARCHAR(100)

 AddressLine2: VARCHAR(15)

 City: VARCHAR(50)

 State: VARCHAR(50)

 PostalCode: VARCHAR(50)

 Country: VARCHAR(50)

o Contractor:

 ContractorID: INT, Primary Key

 ContractorName: VARCHAR(100)

 Phone: VARCHAR(50)

 AddressLine1: VARCHAR(100)

 AddressLine2: VARCHAR(15)

 City: VARCHAR(50)

 State: VARCHAR(50)

 PostalCode: VARCHAR(50)

 Country: VARCHAR(50)

o Equipment:

 EquipmentID: INT, Primary Key

 EquipmentName: VARCHAR(50)

 EquipmentType: VARCHAR(50)

 PurchaseDate: DATE

 EquipmentCost: DECIMAL(20,2)

17
18
IS3346 Construction Company Database Smith

 EquipmentStatus: VARCHAR(50)

o Project:

 ProjectID: INT, Primary Key

 ProjectName: VARCHAR(50)

 StartDate: DATE

 EndDate: DATE

 ProjectBudget: DECIMAL(20,2)

 ProjectStatus: VARCHAR(50)

 ClientID: INT, Foreign Key

o Task:

 TaskID: INT, Primary Key

 TaskName: VARCHAR(50)

 Description: TEXT(2000)

 StartDate: DATE

 EndDate: DATE

 TaskStatus: VARCHAR(50)

 ProjectID: INT, Foreign Key

 ContractorID: INT, Foreign Key

o Supplier:

 SupplierID: INT, Primary Key

 SupplierName: VARCHAR(50)

 Phone: VARCHAR(50)

 AddressLine1: VARCHAR(50)

18
19
IS3346 Construction Company Database Smith

 AddressLine2: VARCHAR(50)

 City: VARCHAR(50)

 State: VARCHAR(50)

 PostalCode: VARCHAR(50)

 Country: VARCHAR(50)

o Material:

 MaterialID: INT, Primary Key

 MaterialName: VARCHAR(50)

 MaterialType: VARCHAR(50)

 MaterialCost: DECIMAL(20,2)

 SupplierID: INT, Foreign Key

o Sources:

 TaskID: INT, Primary Key, Foreign Key

 MaterialID: INT, Primary Key, Foreign Key

 Quantity: DECIMAL(10,2)

o EquipmentUsage:

 EquipmentID: INT, Primary Key, Foreign Key

 TaskID: INT, Primary Key, Foreign Key

 EquipmentUsage: VARCHAR(50)

o Invoice:

 InvoiceID: INT, Primary Key

 InvoiceAmount: DECIMAL(20,2)

 PaymentStatus: VARCHAR(50)

19
20
IS3346 Construction Company Database Smith

 ProjectID: INT, Foreign Key

 Sample Data:

o Client: (1, 'Bold Blueprints', '627-810-3908', '5450 Main St', 'Ste

17','Charleston','SC','35970','USA')

o Contractor: (101, 'Blue Sky Construction', '334-555-0187', '123 Main St', 'Ste 5',

'Montgomery', 'AL', '36104', 'USA')

o Equipment: (3001, 'Caterpillar 320', 'Excavator', '2022-01-15', 85000.00,

'Available')

o Project: (5001, 'River Crossing Bridge', '2023-01-10', '2024-06-15', 2500000.00,

'In Progress',1)

o Task: (6001, 'Steel Reinforcement', 'Installation of steel bars or mesh within

concrete to strengthen structural elements like bridges and retaining walls.', '2024-

05-02','2024-07-03', 'Not Started', 5001, 101)

o Supplier: (4001, 'Steel & Co.', '(555) 123-4567', '123 Steel Ave', 'Suite 100',

'Springfield', 'IL', '62701', 'USA')

o Material: (2001, 'Steel Reinforcement', 'Steel', 850.00, 4001)

o Sources: (6001, 2001, 150.00)

o EquipmentUsage: (3001, 6001, 'Not in Use')

o Invoice: (201, 150000.00, '2023-07-15', 'Paid', 5001)

7. SQL Statements for Database Setup

O I NSERTING S AMPLE D ATA

 Insert Sample Client

20
21
IS3346 Construction Company Database Smith

INSERT INTO Client (ClientID, ClientName, Phone, AddressLine1,

AddressLine2, City, State, PostalCode, Country) VALUES

(1, 'Bold Blueprints', '627-810-3908', '5450 Main St', 'Ste 17',

'Charleston', 'SC', '35970', 'USA');

 Insert Sample Contractor

INSERT INTO Contractor (ContractorID, ContractorName, Phone,

AddressLine1, AddressLine2, City, State, PostalCode, Country)

VALUES (101, 'Blue Sky Construction', '334-555-0187', '123 Main

St', 'Ste 5', 'Montgomery', 'AL', '36104', 'USA');

 Insert Sample Equipment

INSERT INTO Equipment (EquipmentID, EquipmentName, EquipmentType,

PurchaseDate, EquipmentCost, EquipmentStatus) VALUES

(3001,'Caterpillar 320','Excavator','2022-01-

15',85000.00,'Available');

 Insert Sample Project

INSERT INTO Project (ProjectID, ProjectName, StartDate, EndDate,

ProjectBudget, ProjectStatus, ClientID) VALUES (5001, 'River

Crossing Bridge', '2023-01-10', '2024-06-15', 2500000.00, 'In

Progress', 1);

 Insert Sample Task

INSERT INTO Task (TaskID, TaskName, Description, StartDate,

EndDate, TaskStatus, ProjectID, ContractorID) VALUES (6001,

'Steel Reinforcement', 'Installation of steel bars or mesh within

concrete to strengthen structural elements like bridges and

retaining walls.', '2024-05-02','2024-07-03', 'Not Started',

5001, 101);

 Insert Sample Supplier

21
22
IS3346 Construction Company Database Smith

INSERT INTO Supplier (SupplierID, SupplierName, Phone,

AddressLines1, AddressLine2, City, State, PostalCode, Country)

VALUES (4001, 'Steel & Co.', '(555) 123-4567', '123 Steel

Ave','Suite 100', 'Springfield','IL', '62701', 'USA');

 Insert Sample Material

INSERT INTO Material (MaterialID, MaterialName, MaterialType,

MaterialCost, SupplierID) VALUES (2001, 'Steel Reinforcement',

'Steel', 850.00, 4001);

 Insert Sample Sources

INSERT INTO Sources (TaskID, MaterialID, Quantity) VALUES (6001,

2001, 150.00);

 Insert Sample EquipmentUsage

INSERT INTO EquipmentUsage (EquipmentID, TaskID, EquipmentUsage)

VALUES (3001, 6001, 'Not in Use');

 Insert Sample Invoice

INSERT INTO Invoice (InvoiceID, InvoiceAmount, InvoiceDate,

PaymentStatus, ProjectID) VALUES (201, 150000.00, '2023-07-15',

'Paid', 5001);

o SQL S TATEMENTS FOR A PPLICATION F UNCTIONALITY

 Fetching All Clients

SELECT * FROM CLIENTS;

 Registering a New Contractor

INSERT INTO Contractor (ContractorID, ContractorName, Phone,

AddressLine1, AddressLine2, City, State, PostalCode, Country)

VALUES (101, 'Blue Sky Construction', '334-555-0187', '123 Main

St', 'Ste 5', 'Montgomery', 'AL', '36104', 'USA');

22
23
IS3346 Construction Company Database Smith

Figure 2

Add Contractor

 Begin Work Process

 Add New Piece of Equipment

INSERT INTO Equipment (EquipmentID, EquipmentName, EquipmentType,

PurchaseDate, EquipmentCost, EquipmentStatus) VALUES

(3001,'Caterpillar320','Excavator','2022-01-

15',85000.00,'Available');

Figure 3

Add Equipment

 Create New Project

INSERT INTO Project (ProjectID, ProjectName, StartDate, EndDate,

ProjectBudget, ProjectStatus, ClientID) VALUES (5001, 'River

Crossing Bridge', '2023-01-10', '2024-06-15', 2500000.00, 'In

Progress', 1);

Figure 4

23
24
IS3346 Construction Company Database Smith

Add Project

 Add New Task to a Project

INSERT INTO Task (TaskID, TaskName, Description, StartDate,

EndDate, TaskStatus, ProjectID, ContractorID) VALUES (6001,

'Steel Reinforcement', 'Installation of steel bars or mesh within

concrete to strengthen structural elements like bridges and

retaining walls.', '2024-05-02','2024-07-03', 'Not Started',

5001, 101);

Figure 5

Add Task

 Registering a New Supplier

24
25
IS3346 Construction Company Database Smith

INSERT INTO Supplier (SupplierID, SupplierName, Phone,

AddressLines1, AddressLine2, City, State, PostalCode, Country)

VALUES (4001, 'Steel & Co.', '(555) 123-4567', '123 Steel

Ave','Suite 100', 'Springfield','IL', '62701', 'USA');

Figure 6

Add Supplier

 Add New Material

INSERT INTO Material (MaterialID, MaterialName, MaterialType,

MaterialCost, SupplierID) VALUES (2001,'Steel Reinforcement',

'Steel',850.00,4001);

Figure 7

Add Material

25
26
IS3346 Construction Company Database Smith

 Specifying Material Sources for a Task

INSERT INTO Sources (TaskID, MaterialID, Quantity) VALUES (6001,

2001,150.00);

Figure 8

Sources

 Record Equipment Usage (as shown in figure 7)

INSERT INTO EquipmentUsage (EquipmentID, TaskID, EquipmentUsage)

VALUES(3001,6001,'NotinUse');

Figure 9

AddEquipmentUsage

 Generating an Invoice for a Project

26
27
IS3346 Construction Company Database Smith

INSERT INTO Invoice (InvoiceID, InvoiceAmount, InvoiceDate,

PaymentStatus, ProjectID) VALUES (201, 150000.00, '2023-07-15',

'Paid',5001);

Figure 10

Add Invoice

Note. Figures 2-10 are representations of adding data into the fandwconstruction

database.

o S UMMARY

This application setup allows you to:

 Manage Clients: Add, view, update, and remove client information, including

contact details and address, by interacting with the Client table.

 Register and Manage Contractors: Insert, update, or delete contractor records to

keep track of contractor details in the Contractor table.

 Inventory Equipment: Track, update, and retrieve information about equipment,

including type, status, purchase date, and cost, by querying and modifying the

Equipment table.

 Create and Track Projects: Register new projects, monitor their status, set

budgets, associate clients, and define project timelines through the Project table.

27
28
IS3346 Construction Company Database Smith

 Assign and Manage Tasks: Add tasks to specific projects, assign contractors, set

start and end dates, and monitor task status using the Task table.

 Manage Suppliers: Register new suppliers and store contact and address

information in the Supplier table to streamline material sourcing.

 Manage Materials: Track materials used in projects, record costs, and link them

to suppliers by interacting with the Material table.

 Log Material Usage for Tasks: Define the materials and their quantities used in

each task by inserting and managing records in the Sources table.

 Record Equipment Usage for Tasks: Log how and when specific equipment is

used for each task by updating the EquipmentUsage table.

 Generate and Track Invoices: Create and manage invoices for projects, including

the amount, payment status, and date, by inserting records into the Invoice table.

8. Conclusion

 Summary: The database design for the Civil Engineering Construction Company

provides a robust structure for managing clients, contractors, equipment, projects, tasks,

suppliers, materials, and invoices. It ensures efficient resource allocation, strong supplier

and client relationships, and comprehensive financial oversight.

 Future Work: Future enhancements may include adding modules for advanced project

scheduling, detailed cost analysis, and predictive maintenance for equipment.

28
29
IS3346 Construction Company Database Smith

References

MySQL Documentation Team. (2023). "MySQL Functions Reference." MySQL.

[Link]

Hoffer, J. A., Ramesh, V., & Topi, H. (2016). Modern database management. Pearson.

29