HILCOE

Class of 2023

Real Estate
Development Project
Management

[Relational DataBase Management System]

Team member
1. Dawit Kassa
2. Eshetu Gezahegn
3. Helen Desta
4. Dilet Sisay
Contents
1. Introduction ........................................................................................................................................ 2
1.1. Background ................................................................................................................................. 2
1.2. Statement of the problem........................................................................................................... 3
1.3. Scope of the project .................................................................................................................... 4
1.4. Objective of the project .............................................................................................................. 5
1.4.1. General objective ................................................................................................................ 5
1.4.2. Specific objectives ............................................................................................................... 6
1.5. Database Development Methodology ........................................................................................ 7
1.5.1. Data Sources and Collection Methods ................................................................................ 7
1.5.2. Analysis and Design Methods .............................................................................................. 9
1.5.3. Development Tools ........................................................................................................... 11
2. Database Design ............................................................................................................................... 13
2.1. Conceptual database design of the new system ....................................................................... 13
2.1.1. Entities with their description ........................................................................................... 13
2.1.2. Attributes with their description ....................................................................................... 14
2.1.3. Relationships between the entities ................................................................................... 17
2.2. Logical Database Design ............................................................................................................ 22
2.2.1. ER-Relation Mapping ......................................................................................................... 22
2.2.2. Validating model with Normalization ................................................................................ 24
2.2.3. Relational Schema with referential Integrity after normalization ..................................... 32
2.3. Physical database design ........................................................................................................... 35
2.3.1. Physical design strategy .................................................................................................... 39
2.3.2. Database Deployment details ........................................................................................... 40
3. Implementation and testing ............................................................................................................. 42
3.1. SQL script for creating the database ......................................................................................... 42
3.2. SQL Scripts for creating the tables, view, and indexes .............................................................. 43
3.3. Testing....................................................................................................................................... 52
4. Reference .......................................................................................................................................... 59

1
1. Introduction

1.1. Background

Real estate development projects are inherently complex, involving multiple stakeholders,
extensive planning and coordination, and the management of a vast amount of data and
documentation. Effective project management is crucial for the successful delivery of these
projects, as it helps to ensure that all the necessary components are aligned and integrated
seamlessly.

At the core of real estate development project management are several key entities that must be
meticulously tracked and managed. These include the project itself, the property being
developed, the contractors and subcontractors involved, the various construction phases, the
procurement and management of materials, the stock of materials on-site, and the actual usage of
those materials during the construction process.

Traditionally, real estate development project managers have relied on a patchwork of disparate
systems, spreadsheets, and documents to manage this wealth of information. However, this
approach often leads to data silos, lack of integration, and inefficient information management,
hindering the project manager's ability to make informed decisions, coordinate stakeholders, and
optimize project performance.

To address these challenges, there is a pressing need for a robust and comprehensive Relational
Database Management System (RDBMS) that can serve as a centralized platform for managing
all the critical data and information related to real estate development projects. Such an RDBMS
would not only streamline the management of project-related data but also enable enhanced
collaboration, improved operational efficiency, and more effective decision-making.

Concluding Remark:

The inherent complexity of real estate development projects, combined with the current
limitations of fragmented data management practices, underscores the critical importance of
designing and developing a robust RDBMS that can serve as a unified and integrated solution for
real estate development project management. By addressing this need, the RDBMS project can

2
significantly enhance the overall performance, cost-effectiveness, and successful delivery of real
estate development initiatives.

1.2. Statement of the problem

The real estate development industry faces significant challenges in managing the vast amount of
data and information associated with complex projects. The current practices of relying on
disparate spreadsheets, documents, and legacy systems often result in data silos, lack of
integration, and inefficient information management. This lack of a centralized and
comprehensive data management system hinders the ability of project managers to effectively
coordinate various stakeholders, track project progress, control materials and costs, and make
data-driven decisions.

To address these challenges, there is a need to design and develop a robust Relational Database
Management System (RDBMS) that can serve as a unified platform for managing all the critical
data and information related to real estate development projects. The RDBMS should be capable
of integrating the key entities identified, including project, property, contractor, construction
phase, materials purchase, materials stock, and materials usage, to provide a seamless and
efficient data management solution.

The primary objectives of the RDBMS project are:

1. Centralized Data Management: Establish a centralized and structured repository for all
project-related data, eliminating the need for disparate and disconnected data sources.
2. Integrated Data Model: Design a comprehensive relational data model that captures the
relationships and interdependencies between the key entities involved in real estate
development projects.
3. Stakeholder Collaboration: Facilitate effective collaboration and communication
among various stakeholders, including project managers, contractors, suppliers, and the
owner, by providing a shared platform for data access and updates.
4. Improved Operational Efficiency: Streamline the management of project-related
processes, such as procurement, materials tracking, and cost control, through automated
workflows and data-driven insights.

3
 5. Enhanced Reporting and Analytics: Provide robust reporting and analytical capabilities
to support data-driven decision-making, project monitoring, and performance
optimization.
6. Regulatory Compliance and Risk Management: Ensure the RDBMS supports the
management of project-related documentation, permits, and compliance requirements to
mitigate risks and ensure adherence to industry regulations.

By addressing these objectives, the RDBMS project aims to empower real estate development
project managers with a comprehensive, integrated, and efficient data management solution that
enhances overall project performance, reduces operational costs, and ultimately contributes to
the successful delivery of real estate development projects.

1.3. Scope of the project

The scope of the RDBMS project for real estate development project management defined as
follows:

1. Entity Modeling and Relationships:

o Develop a comprehensive data model that captures the core entities involved in real
estate development projects.
o Establish clear relationships and interdependencies between these entities, enabling
the RDBMS to maintain a holistic view of the project lifecycle and the
interconnections between different aspects of the development process.
2. Database Creation:
o Establish a new database instance to serve as the central repository for the real estate
development project management data.
o Ensure the database is configured with the appropriate storage, security, and
performance settings to meet the project's requirements.
3. Table Creation:
o Identify the key entities required for the real estate development project management.
o Design and create the necessary tables to represent these entities, ensuring the data
model accurately captures their relationships and interdependencies.
4. View Creation:
4
 o Develop database views that provide a consolidated and abstracted view of the data,
simplifying the reporting and analytical requirements for project managers and
stakeholders.
o These views may combine data from multiple tables to present a more holistic
perspective of the project's status and performance.
5. Index Creation:
o Analyze the anticipated query patterns and data access requirements, and create
appropriate indexes to optimize the performance of the RDBMS.
o Ensure the indexes are designed to support the most common and critical queries that
will be executed against the database.
6. Testing and Validation:

o Implement a comprehensive testing plan to validate the integrity, reliability, and

performance of the RDBMS.

The scope of this RDBMS project does not include the development of any user-facing
applications, integration with external systems, or the implementation of advanced analytical
capabilities. The focus is solely on creating and testing the underlying database infrastructure to
serve as a reliable and scalable foundation for real estate development project management.

1.4. Objective of the project

The general and specific objectives of the RDBMS project for real estate development project
management can be defined as follows:

1.4.1. General objective

General Objective:

The general objective of this RDBMS project is to establish a robust and efficient database
system that serves as the central repository for managing data related to real estate development
projects. The RDBMS will provide a reliable and scalable foundation to support the varied data
management and reporting requirements of real estate development project stakeholders.

5
 1.4.2. Specific objectives

Specific Objectives:

1. Entity Modeling and Relationships:

o To construct a robust and scalable data model that captures the key entities central to
real estate development project management, including projects, properties,
contractors, construction phases, materials purchases, materials stock, and materials
usage.
o To define the relationships and interdependencies between these entities, ensuring
the data model reflects the interconnected nature of the real estate development
lifecycle and enables a comprehensive and holistic view of project data.
o To ensure the entity model is designed to support the varied reporting, analysis, and
decision-making requirements of project stakeholders, allowing them to quickly and
easily access the relevant information needed to manage the development process
effectively.
2. Table Creation:
o To identify the key entities required for the real estate development project
management.
o To design and create the necessary tables to represent these entities, ensuring the data
model accurately captures the relationships and interdependencies between them.
o To implement the necessary database tables to store and manage the data for the
identified entities, aligning the table structures and data types with the established
data model.
o To optimize the table designs to facilitate efficient data storage, retrieval, and
manipulation, taking into account performance considerations and the anticipated
usage patterns of the RDBMS.
o To enforce data integrity and consistency through the implementation of appropriate
constraints, validations, and referential integrity rules within the table structures.
3. View Creation:
o To develop database views that provide a consolidated and abstracted perspective of
the project data, simplifying the reporting and analytical requirements for project
managers and stakeholders.

6
 o To design the views to integrate data from multiple tables, presenting a holistic view
of the project's status, progress, and performance metrics.
o To ensure the views are optimized for common reporting and decision-support
queries, enhancing the overall usability and responsiveness of the RDBMS.
4. Index Creation:
o To create appropriate indexes to optimize the performance of the RDBMS.
o To ensure the indexes are designed to support the most common and critical queries
that will be executed against the database, enabling rapid data retrieval and efficient
data processing.
o To regularly monitor and maintain the indexes to ensure they continue to provide
optimal performance as the data volume and usage patterns evolve over time.
5. Testing and Validation:
o To implement a comprehensive testing plan to validate the integrity, reliability, and
performance of the RDBMS.

By achieving these specific objectives, the RDBMS project will establish a solid foundation for
real estate development project management, enabling stakeholders to effectively store, manage,
and retrieve the critical data required to drive the successful delivery of these complex initiatives.

1.5. Database Development Methodology

1.5.1. Data Sources and Collection Methods

An overview of the data sources and collection methods that can be considered for the RDBMS
project:

Data Sources:

1. Internal Data Sources:

o Project management system: This will be the primary data source, providing
information on projects, properties, construction phases, materials usage, and
other critical project-related data.
o Accounting/Finance system: Data on materials purchases, contractor invoices, and
other financial transactions related to the development projects.

7
 o Contractor/Vendor management system: Information on contractors,
subcontractors, suppliers, and their performance on the projects.
o Inventory management system: Data on materials stock levels, inventory
movements, and materials consumption.
2. External Data Sources:
o Regulatory agencies: Information on zoning, permits, and other regulatory
requirements for the development projects.
o Market research and analysis: Data on real estate market trends, property values,
and other external factors that may impact the project planning and execution.
o Weather/Climate data: Historical and forecasted weather data that can influence
construction timelines and materials usage.

Data Collection Methods:

1. Automated Data Extraction:

o Develop APIs or database connections to directly integrate the internal systems
(project management, accounting, inventory, etc.) with the RDBMS, enabling the
automated and continuous flow of data.
o Implement ETL (Extract, Transform, Load) processes to extract, transform, and
load the data from the various internal systems into the RDBMS.
2. Manual Data Entry:
o Establish processes and interfaces for manual data entry of information that may
not be available from the integrated systems, such as regulatory approvals, market
research insights, and other external data points.
o Ensure data entry forms and workflows are intuitive and user-friendly to
encourage consistent and accurate data input.
3. Data Scraping and Web Harvesting:
o Utilize web scraping techniques to collect and integrate relevant data from
external sources, such as regulatory agency websites, market research reports, and
weather data providers.
o Implement robust data validation and cleansing processes to ensure the integrity
of the scraped data before ingesting it into the RDBMS.
4. Periodic Data Uploads:

8
 o Establish a scheduled process to periodically upload static data sets, such as
contractor and supplier information, that may not require real-time integration.
o Ensure these data uploads are well-coordinated and aligned with the overall data
management strategy for the RDBMS.

By leveraging a combination of these data sources and collection methods, the RDBMS project
can establish a comprehensive and reliable data ecosystem to support the diverse information
needs of real estate development project management.

1.5.2. Analysis and Design Methods

some key analysis and design methods that can be applied in the RDBMS project for real estate
development project management:

1. Data Requirement Analysis:

o Conduct detailed stakeholder interviews and workshops to understand the specific
data requirements, reporting needs, and analytical use cases for the RDBMS.
o Identify the key entities, attributes, and relationships that must be captured in the
data model to support the various use cases.
o Prioritize the data requirements based on their importance and impact on project
management and decision-making.
2. Conceptual Data Modeling:
o Develop a high-level conceptual data model that represents the key entities, their
attributes, and the relationships between them.
o Use techniques like entity-relationship (ER) diagrams or class diagrams to
visually depict the conceptual model and facilitate discussions with stakeholders.
o Ensure the conceptual model aligns with the identified data requirements and
provides a solid foundation for the logical data model.
3. Logical Data Modeling:
o Translate the conceptual data model into a detailed logical data model, defining
the database tables, columns, data types, and primary/foreign key relationships.
o Normalize the data model to eliminate data redundancy and ensure data integrity,
while balancing the need for performance and query optimizations.
9
 o Incorporate industry best practices and design patterns for database schema
design, such as the use of surrogate keys, junction tables, and slowly changing
dimensions.
4. Physical Database Design:
o Based on the logical data model, design the physical database structure, including
the choice of RDBMS (e.g., PostgreSQL, MySQL, SQL Server), storage engine,
and other infrastructure-level considerations.
o Determine the appropriate indexing strategy, considering the anticipated query
patterns, data volumes, and performance requirements.
o Implement database security measures, such as user roles, permissions, and access
control mechanisms, to ensure the confidentiality and integrity of the data.
5. Data Flow and Integration Design:
o Map the data flow between the various internal and external data sources,
identifying the necessary ETL (Extract, Transform, Load) processes and data
transformation requirements.
o Design the data integration architecture, including the use of message queues,
batch processing, or real-time streaming, depending on the data volume and
latency requirements.
o Incorporate data quality checks and validation rules throughout the data ingestion
and integration processes to maintain data integrity.
6. Reporting and Analytics Design:
o Identify the key reporting and analytical requirements for project stakeholders,
including the types of reports, dashboards, and ad-hoc queries needed.
o Design the necessary database views, stored procedures, and materialized views
to support the reporting and analytical requirements efficiently.
o Incorporate data visualization techniques and tools to enhance the presentation
and interpretation of the project data.
7. Testing and Validation:
o Develop a comprehensive testing plan that includes unit tests, integration tests,
and end-to-end tests to ensure the RDBMS meets the functional and non-
functional requirements.
o Perform load and stress testing to validate the RDBMS's performance under peak
loads and identify any potential bottlenecks or scalability issues.
10
 o Incorporate data validation checks and automated testing mechanisms to maintain
data quality and integrity throughout the system's lifecycle.

By applying these analysis and design methods, the RDBMS project can deliver a robust,
scalable, and user-friendly database solution that effectively supports the real estate development
project management requirements.

1.5.3. Development Tools

some of the key development tools that can be considered for the RDBMS project in real estate
development project management:

1. SQL Server Management Studio (SSMS)

o is an integrated environment for accessing, configuring, managing, administering,
and developing all components of Microsoft SQL Server. It is a graphical user
interface (GUI) tool that provides a comprehensive set of tools for SQL Server
database administrators, developers, and business intelligence professionals.

Some key features and capabilities of SSMS include:

a. Database Management: SSMS allows you to create, configure, and manage SQL
Server databases, including tasks such as database creation, backup and restore, and
performance tuning.
b. Query Execution: SSMS provides a query editor with IntelliSense, syntax
highlighting, and other features to help you write and execute SQL statements, stored
procedures, and other database objects.
c. Object Explorer: The Object Explorer in SSMS provides a hierarchical view of all the
objects within your SQL Server instance, making it easy to navigate and manage
databases, tables, views, stored procedures, and other database objects.
d. Scripting and Automation: SSMS supports scripting and automating various database
management tasks, allowing you to create and execute scripts for repetitive or
complex operations.

11
 e. Reporting and Analysis: SSMS integrates with SQL Server Reporting Services,
enabling you to create, manage, and view reports within the SSMS environment.

2. SQL Server Configuration Manager

o is a graphical management tool that is part of the SQL Server suite of tools and
utilities. It is used to configure various settings and options for the SQL Server
database engine, services, and connectivity.

The main functionalities of the SQL Server Configuration Manager include:

a. Services Management:
o Start, stop, pause, and resume the SQL Server services (SQL Server, SQL Server
Agent, SQL Server Analysis Services, etc.).
o Configure the startup type and account settings for the SQL Server services.
b. SQL Server Network Configuration:
o Enable or disable the different network protocols (TCP/IP, Named Pipes, etc.) used
by the SQL Server instance.
o Configure the properties of the network protocols, such as port numbers, IP
addresses, and more.
c. SQL Server Client Configuration:
o Configure the client network protocols and settings, which determine how client
applications connect to the SQL Server instance.
o Manage aliases for SQL Server instances, which simplify the connection process for
client applications.
d. SQL Server Native Client Configuration:
o Manage the settings for the SQL Server Native Client, which is the data provider used
by many client applications to connect to SQL Server.
e. SQL Server Browser Service:
o Configure the SQL Server Browser service, which helps clients locate SQL Server
instances on the network.

12
2. Database Design

2.1. Conceptual database design of the new system

2.1.1. Entities with their description

Entities and their description
1. Project:
• Represents a real estate development project, including project name, start date,
end date, budget, project phase, and milestone.
• In the RDBMS, the project entity would be a central entity to store information
about various real estate development projects, timelines, and financial aspects.
2. Property:
• Represents the properties involved in the real estate development projects,
including details such as property address, owner, site/location, purpose, and
image.
• In the RDBMS, the property entity would store information about the properties
where the development projects are taking place, linking them to specific projects.
3. Contractor:
• Represents the contractors involved in the real estate development projects,
including details such as contractor name, contact information, specialty, grade,
and contractor projects.
• In the RDBMS, the contractor entity would store information about the
contractors working on different projects, their qualifications, and contact details.
4. Construction Phase:
• Represents the different construction phases within a real estate development
project, including details such as phase name, start date, end date, phase status,
phase progress, and phase documents.
• In the RDBMS, the construction phase entity would track the progress of each
project, detailing the various stages of construction and their timelines.
5. Materials Purchase:
• Represents the procurement of materials for real estate development projects,
including purchase date, material name, quantity, unit cost, and supplier name.
• In the RDBMS, the materials purchase entity would record information about the
materials acquired for each project, tracking their costs and quantities.
13
 6. Materials Stock:
• Represents the current stock of materials available for the real estate development
projects, including details such as material name, quantity, and location.
• In the RDBMS, the materials stock entity would manage the stock levels of
different materials, ensuring efficient utilization during construction.
7. Materials Usage:
• Represents the consumption of materials during the real estate development
projects, including details such as material name, quantity used, and date used.
• In the RDBMS, the materials usage entity would track the utilization of materials
in each phase of construction, helping in monitoring resource allocation and
project progress.

2.1.2. Attributes with their description

1. Project
Attributes:
• ProjectID (Primary Key): Unique identifier for the project
• ProjectName: The name of the construction project
• ProjectLocation: The location/address of the project
• ProjectStartDate: The start date of the project
• ProjectEndDate: The expected end date of the project
• ProjectManager: The name of the project manager
• ProjectBudget: The overall budget for the project
• ProjectBudgetStatus: The current status of the project budget (on track, over budget,
under budget)
• ProjectPhases: A collection of project phases associated with this project
• ProjectProgress: The overall progress or completion percentage of the project
• ProjectMilestones: A collection of project milestones associated with this project
• PropertyID (Foreign Key): The property associated with this project
• ContractorID (Foreign Key): The contractor(s) working on this project
• MaterialsStockID (Foreign Key): The materials stock associated with this project
• MaterialsPurchaseID (Foreign Key): The materials purchases made for this project

14
2. Property
Attributes:
• PropertyID (Primary Key): Unique identifier for the property
• PropertyAddress: The full address of the property
• PropertySize: The size (area) of the property
• PropertySite: The site or location of the property
• PropertyCurrentUse: The current use of the property
• PropertyOwner: The name of the property owner
• ProjectID (Foreign Key): The project this property is associated with
• ContractorID (Foreign Key): The contractor(s) working on this property
• MaterialsStockID (Foreign Key): The materials stock associated with this property
• MaterialsPurchaseID (Foreign Key): The materials purchases made for this property
3. Contractor
Attributes:
• ContractorID (Primary Key): Unique identifier for the contractor
• ContractorName: The name of the contractor company
• ContractorAddress: The address of the contractor
• ContractorPhone: The phone number of the contractor
• ContractorSpecialty: The specialty or type of work the contractor focuses on
• ContractorRating: The overall rating or quality score for the contractor
• ContractorReviews: Any reviews or feedback about the contractor's work
• ProjectID (Foreign Key): The project(s) this contractor is associated with
• PropertyID (Foreign Key): The property(ies) this contractor is working on
• ConstructionPhaseID (Foreign Key): The construction phase(s) this contractor is
involved in
• MaterialsUsageID (Foreign Key): The materials usage associated with this contractor's
work
4. ConstructionPhase
Attributes:
• ConstructionPhaseID (Primary Key): Unique identifier for the construction phase
• PhaseName: The name or title of the construction phase
• PhaseStartDate: The start date of the construction phase
15
 • PhaseEndDate: The expected end date of the construction phase
• PhaseStatus: The current status of the construction phase (planned, in progress,
completed)
• PhaseProgress: The percentage completion of the construction phase
• ProjectID (Foreign Key): The project this construction phase is associated with
• ContractorID (Foreign Key): The contractor(s) working on this construction phase
• MaterialsUsageID (Foreign Key): The materials usage associated with this construction
phase

5. MaterialsPurchase
Attributes:
• MaterialsPurchaseID (Primary Key): Unique identifier for the materials purchase
• MaterialType: The type or category of material purchased
• MaterialQuantity: The amount or quantity of the material purchased
• MaterialUnitOfMeasure: The unit of measure for the material (e.g., cubic feet, square
meters, etc.)
• MaterialPricePerUnit: The price per unit of the material
• MaterialCost: The total cost of the material purchase
• MaterialSupplier: The name of the supplier the materials were purchased from
• PurchaseDate: The date the materials were purchased
• ProjectID (Foreign Key): The project these materials were purchased for
• PropertyID (Foreign Key): The property these materials were purchased for
• ContractorID (Foreign Key): The contractor(s) these materials were purchased for
6. MaterialsStock
Attributes:
• MaterialsStockID (Primary Key): Unique identifier for the materials stock
• MaterialType: The type or category of material in stock
• MaterialQuantity: The current quantity of the material in stock
• MaterialUnitOfMeasure: The unit of measure for the material (e.g., cubic feet, square
meters, etc.)
• MaterialCostPerUnit: The cost per unit of the material
• MaterialTotalCost: The total cost of the materials in stock
16
 • MaterialLocation: The physical location where the materials are stored
• InventoryDate: The date the inventory was recorded
• ProjectID (Foreign Key): The project these materials are associated with
• PropertyID (Foreign Key): The property these materials are associated with
• ContractorID (Foreign Key): The contractor(s) these materials are associated with
7. MaterialsUsage
Attributes:
• MaterialsUsageID (Primary Key): Unique identifier for the materials usage
• MaterialType: The type or category of material used
• MaterialQuantity: The amount or quantity of the material used
• MaterialUnitOfMeasure: The unit of measure for the material (e.g., cubic feet, square
meters, etc.)
• MaterialCostPerUnit: The cost per unit of the material
• MaterialTotalCost: The total cost of the materials used
• UsageDate: The date the materials were used
• ConstructionPhaseID (Foreign Key): The construction phase during which the materials
were used
• ContractorID (Foreign Key): The contractor(s) who used these materials
• ProjectID (Foreign Key): The project these materials were used for

2.1.3. Relationships between the entities

1. Project entity:
• One-to-many relationship with the Property entity: A project can be associated
with multiple properties, but each property is associated with only one project.
• One-to-many relationship with the Contractor entity: A project can have multiple
contractors working on it, but each contractor is associated with one or more
projects.
• One-to-many relationship with the MaterialsStock entity: A project can have
multiple materials stock records, but each record is associated with a single
project.

17
 • One-to-many relationship with the MaterialsPurchase entities: A project can
have multiple materials purchase records, but each record is associated with a
single project.
2. Property entity:
• One-to-many relationship with the Project entity: A property can be associated
with multiple projects, but each project is associated with only one property.
• One-to-many relationship with the Contractor entity: A property can have
multiple contractors working on it, but each contractor is associated with one or
more properties.
• One-to-many relationship with the MaterialsStock: A property can have multiple
materials stock records, but each record is associated with a single property.
• One-to-many relationship with MaterialsPurchase entity: A property can have
multiple materials purchase records, but each record is associated with a single
property.
3. Contractor entity:
• Many-to-many relationship with the Project entity: A contractor can work on
multiple projects, and a project can have multiple contractors.
• Many-to-many relationship with the Property entity: A contractor can work on
multiple properties, and a property can have multiple contractors.
• Many-to-many relationship with the ConstructionPhase entity: A contractor can
be involved in multiple construction phases, and a construction phase can have
multiple contractors.
• One-to-many relationship with the MaterialsUsage entity: A contractor can be
associated with multiple materials usage records, but each materials usage record
is associated with a single contractor.
4. ConstructionPhase entity:
• Many-to-many relationship with the Contractor entity: A construction phase can
involve multiple contractors, and a contractor can be involved in multiple
construction phases.
• One-to-many relationship with the MaterialsUsage entity: A construction phase
can have multiple materials usage records, but each materials usage record is
associated with a single construction phase.
5. MaterialsPurchase entity:
18
 • Many-to-one relationship with the Project entity: Multiple materials purchase
records can be associated with a single project, but each record is associated with
only one project.
• Many-to-one relationship with the Property entity: Multiple materials purchase
records can be associated with a single property, but each record is associated
with only one property.
• Many-to-one relationship with the Contractor entity: Multiple materials purchase
records can be associated with a single contractor, but each record is associated
with only one contractor.
6. MaterialsStock entity:
• Many-to-one relationship with the Project entity: Multiple materials stock
records can be associated with a single project, but each record is associated with
only one project.
• Many-to-one relationship with the Property entity: Multiple materials stock
records can be associated with a single property, but each record is associated
with only one property.
• Many-to-one relationship with the Contractor entity: Multiple materials stock
records can be associated with a single contractor, but each record is associated
with only one contractor.
7. MaterialsUsage entity:
• Many-to-one relationship with the ConstructionPhase entity: Multiple materials
usage records can be associated with a single construction phase, but each record
is associated with only one construction phase.
• Many-to-one relationship with the Contractor entity: Multiple materials usage
records can be associated with a single contractor, but each record is associated
with only one contractor.
• Many-to-one relationship with the Project entity: Multiple materials usage
records can be associated with a single project, but each record is associated with
only one project.

19
 2.1.4. E-R diagram

Project Property

PK ProjectID PK PropertyID

ProjectName PropertyAddress

ProjectLocation ProjectSize

ProjectStartDate (1, *) ProjectSite

ProjectEndDate ProjectCurrentUse

ProjectManager ProjectOwner

ProjectBudget ProjectID (FK)

ProjectBudgetStatus

ProjectPhases

ProjectProgress ConstructionPhase

ProjectMilestones PK ConstructionPhaseID

(1, *) PhaseName
(1, *) (1, *)
PhaseStartDate
(1, *)
PhaseEndDate

PhaseStatus

PhaseProgress
Contractor

(1, *) ProjectID (FK)

PK ContractorID
ContractorID (FK)
ContractorName

ContractorAddress

attribute name MaterialsPurchase

attribute name PK MaterialsPurchaseID

(1, *)
ContractorPhone MaterialType

ContractorSpeciality (1, *) MaterialQuantity

ContractorGrade MaterialUnitOfMeasure

ContractorReviews MaterialPricePerUnit

ProjectID (FK) MaterialCost

MaterialSupplier
(1, *)
PurchaseDate

ProjectID (FK)
(1, *)
MaterialsStock
PropertyID (FK)
PK MaterialsStockID
ContractorID (FK)
MaterialType

MaterialQuantity
(1, *)
MaterialCostPerUnit MaterialsUsage

MaterialTotalCost ProjectID (FK)

attribute name PK MaterialsUsageI D

MaterialLocation MaterialType

InventoryDate MaterialQuantity

ProjectID (FK) MaterialUnitOfMeasure

PropertyID (FK) MaterialCostPerUnit

ContractorID (FK) MaterialTotalCost

UsageDate

ConstructionPhaseID (FK)

ContractorID (FK)

20
erDiagram
PROJECT ||--o{ PROPERTY : "associated with"
PROJECT ||--o{ CONTRACTOR : "has"
PROJECT ||--o{ MATERIALSSTOCK : "has"
PROJECT ||--o{ MATERIALSPURCHASE : "has"

PROPERTY ||--o{ PROJECT : "associated with"

PROPERTY ||--o{ CONTRACTOR : "has"
PROPERTY ||--o{ MATERIALSSTOCK : "has"
PROPERTY ||--o{ MATERIALSPURCHASE : "has"

CONTRACTOR }|--o{ PROJECT : "works on"

CONTRACTOR }|--o{ PROPERTY : "works on"
CONTRACTOR }|--o{ CONSTRUCTIONPHASE : "involved in"
CONTRACTOR ||--o{ MATERIALSUSAGE : "has"

CONSTRUCTIONPHASE }|--o{ CONTRACTOR : "involves"

CONSTRUCTIONPHASE ||--o{ MATERIALSUSAGE : "has"

MATERIALPURCHASE }o--|| PROJECT : "associated with"

MATERIALPURCHASE }o--|| PROPERTY : "associated with"
MATERIALPURCHASE }o--|| CONTRACTOR : "associated with"

MATERIALSSTOCK }o--|| PROJECT : "associated with"

MATERIALSSTOCK }o--|| PROPERTY : "associated with"
MATERIALSSTOCK }o--|| CONTRACTOR : "associated with"

MATERIALSUSAGE }o--|| CONSTRUCTIONPHASE : "associated

with"
MATERIALSUSAGE }o--|| CONTRACTOR : "associated with"
MATERIALSUSAGE }o--|| PROJECT : "associated with"

21
 2.2. Logical Database Design

2.2.1. ER-Relation Mapping

1. Project:
• Attributes: Project ID, Project Name, Project Description, Project Start
Date, Project End Date
• Relationships:
• One-to-Many relationship with Property: A project can be
associated with multiple properties.
• One-to-Many relationship with Contractor: A project can have
multiple contractors working on it.
• One-to-Many relationship with MaterialsStock: A project can
have multiple materials stocks associated with it.
• One-to-Many relationship with MaterialsPurchase: A project can
have multiple material purchases associated with it.
• One-to-Many relationship with MaterialsUsage: A project can
have multiple material usages associated with it.
2. Property:
• Attributes: Property ID, Property Name, Property Address, Property Size
• Relationships:
• Many-to-One relationship with Project: A property can be
associated with multiple projects.
• One-to-Many relationship with Contractor: A property can have
multiple contractors working on it.
• One-to-Many relationship with MaterialsStock: A property can
have multiple materials stocks associated with it.
• One-to-Many relationship with MaterialsPurchase: A property can
have multiple material purchases associated with it.
3. Contractor:
22
 • Attributes: Contractor ID, Contractor Name, Contractor Contact,
Contractor Specialization
• Relationships:
• Many-to-One relationship with Project: A contractor can work on
multiple projects.
• Many-to-One relationship with Property: A contractor can work
on multiple properties.
• Many-to-One relationship with ConstructionPhase: A contractor
can be involved in multiple construction phases.
• One-to-Many relationship with MaterialsUsage: A contractor can
have multiple material usages associated with them.
4. ConstructionPhase:
• Attributes: Phase ID, Phase Name, Phase Start Date, Phase End Date
• Relationships:
• Many-to-One relationship with Contractor: A construction phase
can involve multiple contractors.
• One-to-Many relationship with MaterialsUsage: A construction
phase can have multiple material usages associated with it.
5. MaterialsStock:
• Attributes: Stock ID, Material Name, Material Quantity, Material Unit
• Relationships:
• Many-to-One relationship with Project: A materials stock can be
associated with multiple projects.
• Many-to-One relationship with Property: A materials stock can be
associated with multiple properties.
• Many-to-One relationship with Contractor: A materials stock can
be associated with multiple contractors.
6. MaterialsPurchase:

23
 • Attributes: Purchase ID, Material Name, Purchase Quantity, Purchase
Date, Purchase Price
• Relationships:
• Many-to-One relationship with Project: A materials purchase can
be associated with multiple projects.
• Many-to-One relationship with Property: A materials purchase
can be associated with multiple properties.
• Many-to-One relationship with Contractor: A materials purchase
can be associated with multiple contractors.
7. MaterialsUsage:
• Attributes: Usage ID, Material Name, Usage Quantity, Usage Date
• Relationships:
• Many-to-One relationship with Project: A materials usage can be
associated with multiple projects.
• Many-to-One relationship with Contractor: A materials usage can
be associated with multiple contractors.
• Many-to-One relationship with ConstructionPhase: A materials
usage can be associated with multiple construction phases.

2.2.2. Validating model with Normalization

[Link]. First Normal Form (1NF)

To validate the E-R model against the First Normal Form (1NF) requirements, the
following criteria were used:
1. Atomic Attributes:
• Each attribute in the tables should be atomic, meaning it should contain a
single value and not be further divisible.
• Composite or multi-valued attributes are not allowed in 1NF.
2. Primary Key:

24
 • Each table should have a primary key that uniquely identifies each
record in the table.
• The primary key should be composed of one or more attributes that,
together, uniquely identify a record.
The E-R model was evaluated based on these two main criteria:
1. Atomic Attributes:
• For each table, the attributes were checked to ensure that they were all
atomic and did not contain any composite or multi-valued attributes.
2. Primary Key:
• For each table, the presence of a primary key that uniquely identifies
each record was verified.
• The attributes that make up the primary key were checked to ensure they
collectively provide a unique identifier for each record.
By applying these criteria, the E-R model was validated to ensure that it satisfies the
First Normal Form (1NF) requirements. All the tables in the model have atomic
attributes, and each table has a primary key that uniquely identifies each record,
meeting the 1NF standards.

validate the E-R model against the First Normal Form (1NF) requirements.
1. Project:
• The attributes are all atomic, and the ProjectID serves as the primary
key, satisfying 1NF.
2. Property:
• The attributes are all atomic, and the PropertyID serves as the primary
key, satisfying 1NF.
3. Contractor:
• The attributes are all atomic, and the ContractorID serves as the primary
key, satisfying 1NF.

25
 4. ConstructionPhase:
• The attributes are all atomic, and the ConstructionPhaseID serves as the
primary key, satisfying 1NF.
5. MaterialsPurchase:
• The attributes are all atomic, and the MaterialsPurchaseID serves as the
primary key, satisfying 1NF.
6. MaterialsStock:
• The attributes are all atomic, and the MaterialsStockID serves as the
primary key, satisfying 1NF.
7. MaterialsUsage:
• The attributes are all atomic, and the MaterialsUsageID serves as the
primary key, satisfying 1NF.

[Link]. Second Normal form (2NF)

To validate an E-R model against the Second Normal Form (2NF) requirements, the
following criteria should be applied:
1. Full Functional Dependency:
• Ensure that all non-key attributes are fully functionally dependent on the
entire primary key.
• This means that each non-key attribute should depend on the whole
primary key, not just a subset of the primary key.
• If there are any non-key attributes that depend on only a part of the
primary key, the table violates 2NF.
2. Partial Dependency Elimination:
• If any non-key attributes are partially dependent on the primary key, they
should be moved to a separate table, creating a new relation.
• This ensures that each non-key attribute is fully dependent on the entire
primary key.
3. Transitive Dependency Elimination:
26
 • Identify any transitive dependencies, where a non-key attribute depends
on another non-key attribute.
• If such transitive dependencies are found, the related non-key attributes
should be moved to a separate table, creating a new relation.
• This ensures that each non-key attribute depends only on the primary
key, not on other non-key attributes.
To validate the E-R model against these 2NF criteria, the following steps can be
followed:
1. Full Functional Dependency:
• Examine each table and its non-key attributes.
• Ensure that each non-key attribute is fully dependent on the entire
primary key, not just a subset of the primary key.
2. Partial Dependency Elimination:
• If any non-key attributes are found to be partially dependent on the
primary key, create a new table for those attributes, and ensure that the
new table has a primary key that is a subset of the original table's
primary key.
3. Transitive Dependency Elimination:
• Identify any non-key attributes that are transitively dependent on other
non-key attributes.
• Create a new table for the related non-key attributes, ensuring that the
new table's primary key is the original table's primary key.
By applying these criteria, the E-R model is thoroughly validated against the Second
Normal Form (2NF) requirements and ensured that the model is structured in a way
that promotes data integrity and normalization.

To validate the E-R model against the Second Normal Form (2NF) requirements, I
will go through each table and check for the following:

27
1. Full Functional Dependency:
• Project: The ProjectID is the primary key, and all other attributes
(ProjectName, ClientName, StartDate, EndDate) are fully dependent on
the ProjectID, satisfying 2NF.
• Property: The PropertyID is the primary key, and all other attributes
(PropertyAddress, PropertyType, PropertySize) are fully dependent on
the PropertyID, satisfying 2NF.
• Contractor: The ContractorID is the primary key, and all other
attributes (ContractorName, ContractorPhone, ContractorEmail) are
fully dependent on the ContractorID, satisfying 2NF.
• ConstructionPhase: The ConstructionPhaseID is the primary key, and
all other attributes (PhaseName, StartDate, EndDate) are fully dependent
on the ConstructionPhaseID, satisfying 2NF.
• MaterialsPurchase: The MaterialsPurchaseID is the primary key, and
all other attributes (MaterialID, Quantity, UnitPrice, PurchaseDate) are
fully dependent on the MaterialsPurchaseID, satisfying 2NF.
• MaterialsStock: The MaterialsStockID is the primary key, and all other
attributes (MaterialID, Quantity, LastUpdated) are fully dependent on
the MaterialsStockID, satisfying 2NF.
• MaterialsUsage: The MaterialsUsageID is the primary key, and all other
attributes (MaterialID, Quantity, UsageDate, ConstructionPhaseID) are
fully dependent on the MaterialsUsageID, satisfying 2NF.
2. Partial Dependency Elimination:
• No partial dependencies were found in the E-R model. All non-key
attributes are fully dependent on the respective primary keys.
3. Transitive Dependency Elimination:
• No transitive dependencies were found in the E-R model. Each non-key
attribute depends only on the primary key, not on other non-key
attributes.

28
Based on the analysis, the E-R model satisfies the Second Normal Form (2NF)
requirements. All tables have a primary key, and all non-key attributes are fully
functionally dependent on the primary key. There are no partial or transitive
dependencies present in the model.

[Link]. Third Normal Form (3NF)

To validate an E-R model against the Third Normal Form (3NF) requirements, the
following criteria should be applied:
1. Elimination of Transitive Dependencies:
• Ensure that each non-key attribute in a table depends only on the primary
key and not on any other non-key attribute.
• Transitive dependencies, where a non-key attribute depends on another
non-key attribute, should be eliminated.
2. Determinant Identification:
• Identify the determinants in the tables, which are the attributes or sets of
attributes that uniquely identify each row.
• The primary key is always a determinant, but there may be other
determinants as well.
3. Dependency on the Determinant:
• Verify that each non-key attribute in a table is dependent on the
determinant(s) and not on any other non-key attribute.
• This ensures that there are no transitive dependencies.
To validate the E-R model against these 3NF criteria, the following steps can be
followed:
1. Elimination of Transitive Dependencies:
• Examine each table and identify any non-key attributes that depend on
other non-key attributes.

29
 • If such transitive dependencies are found, create a new table for the
related non-key attributes, ensuring that the new table's primary key is
the original table's primary key.
2. Determinant Identification:
• For each table, identify the determinants, which are the attributes or sets
of attributes that uniquely identify each row.
• The primary key is always a determinant, but there may be other
determinants as well.
3. Dependency on the Determinant:
• Verify that each non-key attribute in a table is dependent only on the
determinant(s) and not on any other non-key attribute.
• If any non-key attribute is found to be dependent on another non-key
attribute, create a new table for the related non-key attributes, ensuring
that the new table's primary key is the original table's primary key.
By applying these criteria, you can thoroughly validate the E-R model against the
Third Normal Form (3NF) requirements and ensure that the model is structured in a
way that promotes data integrity and normalization.

To validate the E-R model against the Third Normal Form (3NF) requirements, each
table is checked with the following criteria:
1. Elimination of Transitive Dependencies:
• Project: The ProjectID is the primary key, and all other attributes
(ProjectName, ClientName, StartDate, EndDate) depend directly on the
ProjectID, with no transitive dependencies.
• Property: The PropertyID is the primary key, and all other attributes
(PropertyAddress, PropertyType, PropertySize) depend directly on the
PropertyID, with no transitive dependencies.

30
 • Contractor: The ContractorID is the primary key, and all other
attributes (ContractorName, ContractorPhone, ContractorEmail) depend
directly on the ContractorID, with no transitive dependencies.
• ConstructionPhase: The ConstructionPhaseID is the primary key, and
all other attributes (PhaseName, StartDate, EndDate) depend directly on
the ConstructionPhaseID, with no transitive dependencies.
• MaterialsPurchase: The MaterialsPurchaseID is the primary key, and
all other attributes (MaterialID, Quantity, UnitPrice, PurchaseDate)
depend directly on the MaterialsPurchaseID, with no transitive
dependencies.
• MaterialsStock: The MaterialsStockID is the primary key, and all other
attributes (MaterialID, Quantity, LastUpdated) depend directly on the
MaterialsStockID, with no transitive dependencies.
• MaterialsUsage: The MaterialsUsageID is the primary key, and all other
attributes (MaterialID, Quantity, UsageDate, ConstructionPhaseID)
depend directly on the MaterialsUsageID, with no transitive
dependencies.
2. Determinant Identification:
• For each table, the primary key is the determinant that uniquely
identifies each row.
3. Dependency on the Determinant:
• In all tables, each non-key attribute is dependent only on the primary key
(the determinant) and not on any other non-key attribute.
Based on the analysis, the E-R model satisfies the Third Normal Form (3NF)
requirements. All tables have a primary key that is the determinant, and all non-key
attributes are dependent only on the primary key, with no transitive dependencies
present in the model.

31
 2.2.3. Relational Schema with referential Integrity after normalization

Based on the E-R model you provided, here is the relational schema with referential
integrity after normalization:
1. Project Table
• ProjectID (PK)
• ProjectName
• ProjectLocation
• ProjectStartDate
• ProjectEndDate
• ProjectManager
• ProjectBudget
• ProjectBudgetStatus
• ProjectPhases
• ProjectProgress
• ProjectMilestones
• PropertyID (FK, references [Link])
• ContractorID (FK, references [Link])
• MaterialsStockID (FK, references [Link])
• MaterialsPurchaseID (FK, references
[Link])
2. Property Table
• PropertyID (PK)
• PropertyAddress
• PropertySize
• PropertySite
• PropertyCurrentUse
• PropertyOwner
• ProjectID (FK, references [Link])

32
 • ContractorID (FK, references [Link])
• MaterialsStockID (FK, references [Link])
• MaterialsPurchaseID (FK, references
[Link])
3. Contractor Table
• ContractorID (PK)
• ContractorName
• ContractorAddress
• ContractorPhone
• ContractorSpecialty
• ContractorRating
• ContractorReviews
• ProjectID (FK, references [Link])
• PropertyID (FK, references [Link])
• ConstructionPhaseID (FK, references
[Link])
• MaterialsUsageID (FK, references [Link])
4. ConstructionPhase Table
• ConstructionPhaseID (PK)
• PhaseName
• PhaseStartDate
• PhaseEndDate
• PhaseStatus
• PhaseProgress
• ProjectID (FK, references [Link])
• ContractorID (FK, references [Link])
• MaterialsUsageID (FK, references [Link])
5. MaterialsPurchase Table
• MaterialsPurchaseID (PK)

33
 • MaterialType
• MaterialQuantity
• MaterialUnitOfMeasure
• MaterialPricePerUnit
• MaterialCost
• MaterialSupplier
• PurchaseDate
• ProjectID (FK, references [Link])
• PropertyID (FK, references [Link])
• ContractorID (FK, references [Link])
6. MaterialsStock Table
• MaterialsStockID (PK)
• MaterialType
• MaterialQuantity
• MaterialUnitOfMeasure
• MaterialCostPerUnit
• MaterialTotalCost
• MaterialLocation
• InventoryDate
• ProjectID (FK, references [Link])
• PropertyID (FK, references [Link])
• ContractorID (FK, references [Link])
7. MaterialsUsage Table

• MaterialsUsageID (PK)
• MaterialType
• MaterialQuantity
• MaterialUnitOfMeasure
• MaterialCostPerUnit

34
 • MaterialTotalCost
• UsageDate
• ConstructionPhaseID (FK, references [Link])
• ContractorID (FK, references [Link])
• ProjectID (FK, references [Link])
The referential integrity is ensured through the use of foreign key constraints, where
each foreign key column in a table references the primary key column of another
table. This helps maintain data consistency and integrity within the relational database
schema.

2.3. Physical database design

The physical database design for the tables.

1. Project Table

CREATE TABLE Project (

ProjectID INT PRIMARY KEY,
ProjectName VARCHAR(100),
ProjectLocation VARCHAR(200),
ProjectStartDate DATE,
ProjectEndDate DATE,
ProjectManager VARCHAR(100),
ProjectBudget DECIMAL(12,2),
ProjectBudgetStatus VARCHAR(50),
ProjectPhases VARCHAR(200),
ProjectProgress VARCHAR(50),
ProjectMilestones VARCHAR(200),
PropertyID INT FOREIGN KEY REFERENCES Property(PropertyID),
ContractorID INT FOREIGN KEY REFERENCES Contractor(ContractorID),
MaterialsStockID INT FOREIGN KEY REFERENCES
MaterialsStock(MaterialsStockID),

35
 MaterialsPurchaseID INT FOREIGN KEY REFERENCES
MaterialsPurchase(MaterialsPurchaseID)
);
2. Property Table

CREATE TABLE Property (

PropertyID INT PRIMARY KEY,
PropertyAddress VARCHAR(200),
PropertySize DECIMAL(10,2),
PropertySite VARCHAR(100),
PropertyCurrentUse VARCHAR(100),
PropertyOwner VARCHAR(100),
ProjectID INT FOREIGN KEY REFERENCES Project(ProjectID),
ContractorID INT FOREIGN KEY REFERENCES Contractor(ContractorID),
MaterialsStockID INT FOREIGN KEY REFERENCES
MaterialsStock(MaterialsStockID),
MaterialsPurchaseID INT FOREIGN KEY REFERENCES
MaterialsPurchase(MaterialsPurchaseID)
);
3. Contractor Table
CREATE TABLE Contractor (
ContractorID INT PRIMARY KEY,
ContractorName VARCHAR(100),
ContractorAddress VARCHAR(200),
ContractorPhone VARCHAR(20),
ContractorSpecialty VARCHAR(100),
ContractorRating DECIMAL(3,1),
ContractorReviews TEXT,
ProjectID INT FOREIGN KEY REFERENCES Project(ProjectID),
PropertyID INT FOREIGN KEY REFERENCES Property(PropertyID),
ConstructionPhaseID INT FOREIGN KEY REFERENCES
ConstructionPhase(ConstructionPhaseID),

36
 MaterialsUsageID INT FOREIGN KEY REFERENCES
MaterialsUsage(MaterialsUsageID)
);
4. ConstructionPhase Table

CREATE TABLE ConstructionPhase (

ConstructionPhaseID INT PRIMARY KEY,
PhaseName VARCHAR(100),
PhaseStartDate DATE,
PhaseEndDate DATE,
PhaseStatus VARCHAR(50),
PhaseProgress VARCHAR(50),
ProjectID INT FOREIGN KEY REFERENCES Project(ProjectID),
ContractorID INT FOREIGN KEY REFERENCES Contractor(ContractorID),
MaterialsUsageID INT FOREIGN KEY REFERENCES
MaterialsUsage(MaterialsUsageID)
);
5. MaterialsPurchase Table

CREATE TABLE MaterialsPurchase (

MaterialsPurchaseID INT PRIMARY KEY,
MaterialType VARCHAR(100),
MaterialQuantity DECIMAL(10,2),
MaterialUnitOfMeasure VARCHAR(20),
MaterialPricePerUnit DECIMAL(12,2),
MaterialCost DECIMAL(12,2),
MaterialSupplier VARCHAR(100),
PurchaseDate DATE,
ProjectID INT FOREIGN KEY REFERENCES Project(ProjectID),
PropertyID INT FOREIGN KEY REFERENCES Property(PropertyID),
ContractorID INT FOREIGN KEY REFERENCES Contractor(ContractorID)
);
6. MaterialsStock Table
37
 CREATE TABLE MaterialsStock (
MaterialsStockID INT PRIMARY KEY,
MaterialType VARCHAR(100),
MaterialQuantity DECIMAL(10,2),
MaterialUnitOfMeasure VARCHAR(20),
MaterialCostPerUnit DECIMAL(12,2),
MaterialTotalCost DECIMAL(12,2),
MaterialLocation VARCHAR(100),
InventoryDate DATE,
ProjectID INT FOREIGN KEY REFERENCES Project(ProjectID),
PropertyID INT FOREIGN KEY REFERENCES Property(PropertyID),
ContractorID INT FOREIGN KEY REFERENCES Contractor(ContractorID)
);
7. MaterialsUsage Table

CREATE TABLE MaterialsUsage (

MaterialsUsageID INT PRIMARY KEY,
MaterialType VARCHAR(100),
MaterialQuantity DECIMAL(10,2),
MaterialUnitOfMeasure VARCHAR(20),
MaterialCostPerUnit DECIMAL(12,2),
MaterialTotalCost DECIMAL(12,2),
UsageDate DATE,
ConstructionPhaseID INT FOREIGN KEY REFERENCES
ConstructionPhase(ConstructionPhaseID),
ContractorID INT FOREIGN KEY REFERENCES Contractor(ContractorID),
ProjectID INT FOREIGN KEY REFERENCES Project(ProjectID)
);

38
 2.3.1. Physical design strategy

The physical database design strategy for the real estate development project
management appears to be a relational database design that emphasizes the following
key aspects:
1. Entity-Relationship Modeling: The database schema consists of several
interconnected entities, such as Project, Property, Contractor,
ConstructionPhase, MaterialsPurchase, MaterialsStock, and MaterialsUsage.
These entities capture the various components and relationships involved in the
real estate development project management process.
2. Primary and Foreign Keys: Each entity has a unique primary key identifier,
such as ProjectID, PropertyID, ContractorID, ConstructionPhaseID,
MaterialsPurchaseID, MaterialsStockID, and MaterialsUsageID. These primary
keys are used to establish relationships between the entities through foreign
keys.
3. Normalization: The database design appears to be normalized to reduce data
redundancy and improve data integrity. For example, the project entity stores
project-specific information, and the unique ProjectID as the primary key,
ensures that each project record is uniquely identified, and property, contractor,
materialsPurchase and materialsStock entities reference the project through this
foreign key. Similarly, other entities stores entity-specific information, and
their unique entity ID as the primary key ensures that each entity record is
uniquely identified and other entities reference the specific entity through the
foreign key identified for each entity.
By normalizing the database design in this manner, the system can avoid data
redundancy, improve data integrity, and establish clear relationships between
the various entities involved in the real estate development project management
process.

39
 4. Data Modeling: The database design covers a wide range of information
related to real estate development projects, including project details, property
details, contractor information, construction phases, materials purchase and
stock, and materials usage.
5. Data Integrity Constraints: The design includes various data integrity
constraints, such as referential integrity constraints (foreign key relationships)
to ensure the consistency and validity of the data across the entities.
6. Scalability and Performance: The design allows for the addition of new
projects, properties, contractors, construction phases, and materials-related
data, making the RDBMS scalable to handle the growing needs of the real
estate development project management system.
7. Reporting and Analysis: The detailed data captured in the entities can support
various reporting and analytical requirements for project management,
budgeting, material tracking, and overall performance monitoring.
Overall, the physical database design strategy appears to be a well-structured
relational database that addresses the key requirements of a real estate development
project management system, focusing on data organization, relationships, integrity,
and scalability.

2.3.2. Database Deployment details

the key details for the database deployment of the real estate development project
management system with the specified tables and relationships:
1. Database Management System (DBMS): The DBMS for this system is likely
a relational database such as PostgreSQL, MySQL, or Microsoft SQL Server.
These are widely-used enterprise-grade DBMS options that can handle the
requirements of the project management system.
2. Database Schema: The database schema is designed to support the key entities
and relationships in the real estate development project management system.
The schema includes the following tables:
• Project
40
 • Property
• Contractor
• ConstructionPhase
• MaterialsPurchase
• MaterialsStock
• MaterialsUsage
The relationships between the tables are defined using foreign key constraints,
as shown in the table definitions.
3. Database Hosting: The database can be hosted either on-premises or in a
cloud-based platform, depending on the organization's infrastructure and
deployment preferences. Cloud-based options include Amazon RDS, Microsoft
Azure SQL Database, or Google Cloud SQL.
4. Database Backup and Recovery: Regular backups of the database should be
implemented to protect against data loss. This can be done using the built-in
backup and recovery features of the DBMS, with backups stored either on-
premises or in a cloud-based storage service. A disaster recovery plan should
also be in place to ensure the ability to restore the database in the event of a
system failure.
5. Database Security: The database deployment should include robust security
measures, such as:
• User authentication and access control
• Encryption for data at rest and in transit
• Monitoring and auditing of database activities
6. Database Performance Optimization: To ensure optimal performance, the
database deployment should include indexing on the appropriate columns,
query tuning, and configuration of database parameters based on the
application's workload and requirements.
7. Database Scalability and High Availability: Depending on the project's
growth and availability requirements, the database deployment should consider

41
 options for horizontal scaling (adding more database instances) and high
availability (e.g., database clustering, replication, or failover mechanisms).
8. Database Monitoring and Maintenance: Regular monitoring of database
performance metrics and maintenance activities, such as index maintenance,
database cleanup, and schema updates, should be implemented to ensure the
long-term health and efficiency of the database.
The specific implementation details may vary depending on the organization's IT
infrastructure, cloud platform, and overall requirements for the real estate
development project management system.

3. Implementation and testing

3.1. SQL script for creating the database

SQL script for creating database

-- Create the database

CREATE DATABASE [RealEstate Dev Project Management]
ON (
NAME = 'RealEstate Dev Project Management_Data',
FILENAME = 'C:\Users\Public\Documents\RealEstate Dev Project [Link]',
SIZE = 10MB,
MAXSIZE = 1000MB,
FILEGROWTH = 5MB
)
LOG ON (
NAME = 'RealEstate Dev Project Management_Log',
FILENAME = 'C:\Users\Public\Documents\RealEstate Dev Project Management_Log.ldf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 2MB
);
GO

42
This script will create the [RealEstate Dev Project Management] database with the
specified file locations, sizes, and growth settings.

3.2. SQL Scripts for creating the tables, view, and indexes

SQL Scripts for creating the tables

CREATE TABLE Project (

ProjectID INT PRIMARY KEY,
ProjectName VARCHAR(100),
ProjectLocation VARCHAR(200),
ProjectStartDate DATE,
ProjectEndDate DATE,
ProjectManager VARCHAR(100),
ProjectBudget DECIMAL(10,2),
ProjectBudgetStatus VARCHAR(50),
ProjectPhases VARCHAR(200),
ProjectProgress VARCHAR(50),
ProjectMilestones VARCHAR(200)
);

CREATE TABLE Property (

PropertyID INT PRIMARY KEY,
PropertyAddress VARCHAR(200),
PropertySize DECIMAL(10,2),
PropertySite VARCHAR(100),
PropertyCurrentUse VARCHAR(50),
PropertyOwner VARCHAR(100),
ProjectID INT FOREIGN KEY REFERENCES Project(ProjectID)
);

CREATE TABLE Contractor (

ContractorID INT PRIMARY KEY,
ContractorName VARCHAR(100),

43
 ContractorAddress VARCHAR(200),
ContractorPhone VARCHAR(20),
ContractorSpecialty VARCHAR(100),
ContractorGrade INT(3),
ContractorReviews TEXT,
ProjectID INT FOREIGN KEY REFERENCES Project(ProjectID)
);

CREATE TABLE ConstructionPhase (

ConstructionPhaseID INT PRIMARY KEY,
PhaseName VARCHAR(100),
PhaseStartDate DATE,
PhaseEndDate DATE,
PhaseStatus VARCHAR(50),
PhaseProgress VARCHAR(50),
ProjectID INT FOREIGN KEY REFERENCES Project(ProjectID),
ContractorID INT FOREIGN KEY REFERENCES Contractor(ContractorID)
);

CREATE TABLE MaterialsPurchase (

MaterialsPurchaseID INT PRIMARY KEY,
MaterialType VARCHAR(100),
MaterialQuantity DECIMAL(10,2),
MaterialUnitOfMeasure VARCHAR(20),
MaterialPricePerUnit DECIMAL(10,2),
MaterialCost DECIMAL(10,2),
MaterialSupplier VARCHAR(100),
PurchaseDate DATE,
ProjectID INT FOREIGN KEY REFERENCES Project(ProjectID),
PropertyID INT FOREIGN KEY REFERENCES Property(PropertyID),
ContractorID INT FOREIGN KEY REFERENCES Contractor(ContractorID)
);

CREATE TABLE MaterialsStock (

MaterialsStockID INT PRIMARY KEY,
MaterialType VARCHAR(100),
44
 MaterialQuantity DECIMAL(10,2),
MaterialUnitOfMeasure VARCHAR(20),
MaterialCostPerUnit DECIMAL(10,2),
MaterialTotalCost DECIMAL(10,2),
MaterialLocation VARCHAR(200),
InventoryDate DATE,
ProjectID INT FOREIGN KEY REFERENCES Project(ProjectID),
PropertyID INT FOREIGN KEY REFERENCES Property(PropertyID),
ContractorID INT FOREIGN KEY REFERENCES Contractor(ContractorID)
);

CREATE TABLE MaterialsUsage (

MaterialsUsageID INT PRIMARY KEY,
MaterialType VARCHAR(100),
MaterialQuantity DECIMAL(10,2),
MaterialUnitOfMeasure VARCHAR(20),
MaterialCostPerUnit DECIMAL(10,2),
MaterialTotalCost DECIMAL(10,2),
UsageDate DATE,
ConstructionPhaseID INT FOREIGN KEY REFERENCES ConstructionPhase(ConstructionPhaseID),
ContractorID INT FOREIGN KEY REFERENCES Contractor(ContractorID),
ProjectID INT FOREIGN KEY REFERENCES Project(ProjectID)
);
The SQL scripts for creating the tables for the real estate development project
management database schema are summarized with key points here.
1. Project Table: Stores information about the construction projects, including
details like project name, location, start/end dates, manager, budget, status,
phases, progress, and milestones.
2. Property Table: Stores details about the properties associated with the
construction projects, such as the address, size, site, current use, and owner. It
has a foreign key relationship with the Project table.

45
 3. Contractor Table: Stores information about the contractors involved in the
projects, including their name, address, phone, specialty, grade/rating, and
reviews. It also has a foreign key relationship with the Project table.
4. ConstructionPhase Table: Stores details about the various phases of the
construction project, such as the phase name, start/end dates, status, progress,
as well as the associated project and contractor.
5. MaterialsPurchase Table: Stores information about the materials purchased
for the projects, including the type, quantity, unit of measure, price, cost,
supplier, and purchase date. It has foreign key relationships with the Project,
Property, and Contractor tables.
6. MaterialsStock Table: Stores details about the materials in stock, including
the type, quantity, unit of measure, cost per unit, total cost, location, and
inventory date. It also has foreign key relationships with the Project, Property,
and Contractor tables.
7. MaterialsUsage Table: Stores information about the materials used during the
construction phases, including the type, quantity, unit of measure, cost per unit,
total cost, and usage date. It has foreign key relationships with the
ConstructionPhase, Contractor, and Project tables.

Once, tables are created. In order connect with the database, for example,
USE [RealEstate Dev Project Management]
GO

SELECT * FROM Project

GO

SQL Scripts for creating view

-- Project View
CREATE VIEW ProjectView AS
SELECT
[Link],
[Link],
46
 [Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM Project p;

-- Property View
CREATE VIEW PropertyView AS
SELECT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM Property pr
JOIN Project p ON [Link] = [Link];

-- Contractor View
CREATE VIEW ContractorView AS
SELECT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM Contractor c
JOIN Project p ON [Link] = [Link];

-- ConstructionPhase View
CREATE VIEW ConstructionPhaseView AS
SELECT
[Link],
47
 [Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM ConstructionPhase cp
JOIN Project p ON [Link] = [Link]
JOIN Contractor c ON [Link] = [Link];

-- MaterialsPurchase View
CREATE VIEW MaterialsPurchaseView AS
SELECT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM MaterialsPurchase mp
JOIN Project p ON [Link] = [Link]
JOIN Property pr ON [Link] = [Link]
JOIN Contractor c ON [Link] = [Link];

-- MaterialsStock View
CREATE VIEW MaterialsStockView AS
SELECT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
48
 [Link],
[Link]
FROM MaterialsStock ms
JOIN Project p ON [Link] = [Link]
JOIN Property pr ON [Link] = [Link]
JOIN Contractor c ON [Link] = [Link];

-- MaterialsUsage View
CREATE VIEW MaterialsUsageView AS
SELECT
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link],
[Link]
FROM MaterialsUsage mu
JOIN ConstructionPhase cp ON [Link] = [Link]
JOIN Contractor c ON [Link] = [Link]
JOIN Project p ON [Link] = [Link];

SQL script for creating indexes

-- Create index on Project table

CREATE INDEX idx_project_projectname
ON Project (ProjectName);

CREATE INDEX idx_project_projectmanager

ON Project (ProjectManager);

CREATE INDEX idx_project_projectstartdate

ON Project (ProjectStartDate);

49
CREATE INDEX idx_project_projectenddate
ON Project (ProjectEndDate);

-- Create index on Property table

CREATE INDEX idx_property_propertyaddress
ON Property (PropertyAddress);

CREATE INDEX idx_property_propertyowner

ON Property (PropertyOwner);

CREATE INDEX idx_property_projectid

ON Property (ProjectID);

-- Create index on Contractor table

CREATE INDEX idx_contractor_contractorname
ON Contractor (ContractorName);

CREATE INDEX idx_contractor_contractorspecialty

ON Contractor (ContractorSpecialty);

CREATE INDEX idx_contractor_projectid

ON Contractor (ProjectID);

-- Create index on ConstructionPhase table

CREATE INDEX idx_constructionphase_phasename
ON ConstructionPhase (PhaseName);

CREATE INDEX idx_constructionphase_projectid

ON ConstructionPhase (ProjectID);

CREATE INDEX idx_constructionphase_contractorid

ON ConstructionPhase (ContractorID);

50
-- Create index on MaterialsPurchase table
CREATE INDEX idx_materialspurchase_materialtype
ON MaterialsPurchase (MaterialType);

CREATE INDEX idx_materialspurchase_materialsupplier

ON MaterialsPurchase (MaterialSupplier);

CREATE INDEX idx_materialspurchase_projectid

ON MaterialsPurchase (ProjectID);

CREATE INDEX idx_materialspurchase_propertyid

ON MaterialsPurchase (PropertyID);

CREATE INDEX idx_materialspurchase_contractorid

ON MaterialsPurchase (ContractorID);

-- Create index on MaterialsStock table

CREATE INDEX idx_materialsstock_materialtype
ON MaterialsStock (MaterialType);

CREATE INDEX idx_materialsstock_materiallocation

ON MaterialsStock (MaterialLocation);

CREATE INDEX idx_materialsstock_projectid

ON MaterialsStock (ProjectID);

CREATE INDEX idx_materialsstock_propertyid

ON MaterialsStock (PropertyID);

CREATE INDEX idx_materialsstock_contractorid

ON MaterialsStock (ContractorID);

-- Create index on MaterialsUsage table

51
CREATE INDEX idx_materialsusage_materialtype
ON MaterialsUsage (MaterialType);

CREATE INDEX idx_materialsusage_constructionphaseid

ON MaterialsUsage (ConstructionPhaseID);

CREATE INDEX idx_materialsusage_contractorid

ON MaterialsUsage (ContractorID);

CREATE INDEX idx_materialsusage_projectid

ON MaterialsUsage (ProjectID);

These index scripts will create indexes on the most commonly queried columns in the
database tables, improving query performance. The indexes cover the following areas:
• Project table: indexes on project name, manager, start date, and end date
• Property table: indexes on property address, owner, and project ID
• Contractor table: indexes on contractor name, specialty, and project ID
• ConstructionPhase table: indexes on phase name, project ID, and contractor ID
• MaterialsPurchase table: indexes on material type, supplier, project ID, property ID, and
contractor ID
• MaterialsStock table: indexes on material type, location, project ID, property ID, and
contractor ID
• MaterialsUsage table: indexes on material type, construction phase ID, contractor ID, and
project ID

3.3. Testing

-- Validate database creation

IF DB_ID('RealEstate Dev Project Management') IS NULL
BEGIN
CREATE DATABASE [RealEstate Dev Project Management]
ON (
NAME = 'RealEstate Dev Project Management_Data',
FILENAME = 'C:\Users\Public\Documents\RealEstate Dev Project [Link]',

52
 SIZE = 10MB,
MAXSIZE = 1000MB,
FILEGROWTH = 5MB
)
LOG ON (
NAME = 'RealEstate Dev Project Management_Log',
FILENAME = 'C:\Users\Public\Documents\RealEstate Dev Project Management_Log.ldf',
SIZE = 5MB,
MAXSIZE = 500MB,
FILEGROWTH = 2MB
);
PRINT 'Database "RealEstate Dev Project Management" created successfully.'
END
ELSE
PRINT 'Database "RealEstate Dev Project Management" already exists.'
GO
test whether the "RealEstate Dev Project Management" database was created
successfully in SQL Server Management Studio:

SELECT name, database_id, create_date

FROM [Link]
WHERE name = 'RealEstate Dev Project Management'

-- Test database exists

IF DB_ID('RealEstate Dev Project Management') IS NOT NULL
BEGIN
PRINT 'Database "RealEstate Dev Project Management" exists.'

-- Test retrieving data from a table (assuming a table named "Project" exists)
SELECT *
FROM [RealEstate Dev Project Management].[dbo].[Project]
WHERE ProjectID = 1

PRINT 'Data retrieved successfully from the "Project" table.'

END
ELSE
PRINT 'Database "RealEstate Dev Project Management" does not exist.'
GO

Table creation validation test

-- Validate table creation for project, property, contractor, constructionphase, materialspurchase,

materialsstock, and materialsusage

BEGIN TRY
53
 USE [RealEstate Dev Project Management]

-- Create a table to hold entity information

CREATE TABLE [dbo].[EntityInfo](
[EntityID] [int] IDENTITY(1,1) NOT NULL,
[EntityName] [varchar](50) NOT NULL,
[EntityType] [varchar](50) NOT NULL,
CONSTRAINT [PK_EntityInfo] PRIMARY KEY CLUSTERED ([EntityID] ASC)
)

-- Insert entity information

INSERT INTO [dbo].[EntityInfo] ([EntityName], [EntityType])
VALUES
('Project', 'Table'),
('Property', 'Table'),
('Contractor', 'Table'),
('ConstructionPhase', 'Table'),
('MaterialsPurchase', 'Table'),
('MaterialsStock', 'Table'),
('MaterialsUsage', 'Table')

-- Validate table creation

DECLARE @EntityName VARCHAR(50)
DECLARE @EntityType VARCHAR(50)
DECLARE @TableExists BIT

DECLARE entity_cursor CURSOR FOR

SELECT [EntityName], [EntityType]
FROM [dbo].[EntityInfo]

OPEN entity_cursor
FETCH NEXT FROM entity_cursor INTO @EntityName, @EntityType

WHILE @@FETCH_STATUS = 0
BEGIN
-- Check if the table exists
SET @TableExists = CASE WHEN OBJECT_ID(QUOTENAME(@EntityName), 'U') IS NOT
NULL THEN 1 ELSE 0 END

IF @TableExists = 1
PRINT 'Table "' + @EntityName + '" created successfully.'
ELSE
PRINT 'Error creating table "' + @EntityName + '".'

FETCH NEXT FROM entity_cursor INTO @EntityName, @EntityType

END

CLOSE entity_cursor
DEALLOCATE entity_cursor

PRINT 'Validation complete.'

END TRY
BEGIN CATCH
PRINT 'Error validating table creation: ' + ERROR_MESSAGE()
54
END CATCH
GO

Validate Primary Key Constraints

SELECT
SCHEMA_NAME(t.schema_id) AS SchemaName,
[Link] AS TableName,
[Link] AS ColumnName,
[Link] AS IndexName
FROM
[Link] t
JOIN
[Link] i ON t.object_id = i.object_id
JOIN
sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN
[Link] c ON t.object_id = c.object_id AND ic.column_id = c.column_id
WHERE
i.is_primary_key = 1
ORDER BY
[Link], [Link];

Validate foreign key constraints

SELECT
OBJECT_SCHEMA_NAME(fk.object_id) AS ConstraintSchemaName,
[Link] AS ConstraintName,
OBJECT_SCHEMA_NAME(p.object_id) AS TableSchemaName,
[Link] AS TableName,
OBJECT_SCHEMA_NAME(r.object_id) AS ReferencedTableSchemaName,
[Link] AS ReferencedTableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferencedColumnName
FROM
sys.foreign_keys fk
JOIN
sys.foreign_key_columns fc ON fk.object_id = fc.constraint_object_id
JOIN
[Link] p ON fc.parent_object_id = p.object_id
JOIN
[Link] r ON fk.referenced_object_id = r.object_id
WHERE
[Link] IN ('Project', 'Property', 'Contractor', 'ConstructionPhase', 'MaterialsPurchase', 'MaterialsStock',
'MaterialsUsage')
ORDER BY
ConstraintSchemaName, ConstraintName;

55
Check for View Existence

SELECT * FROM INFORMATION_SCHEMA.VIEWS;

Verify View Definition

1. Project View:
SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'ProjectView';
2. Property View:
SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'PropertyView';
3. Contractor View:
SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'ContractorView';
4. Construction Phase View:
SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'ConstructionPhaseView';
5. Materials Purchase View:
SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'MaterialsPurchaseView';
6. Materials Stock View:
SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'MaterialsStockView';
7. Materials Usage View:
SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'MaterialsUsageView';

List all views

SELECT
TABLE_SCHEMA,

56
 TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS;

Test View Functionality

SELECT * FROM Project;
SELECT * FROM Property;
SELECT * FROM Contractor;
SELECT * FROM ConstructionPhase;
SELECT * FROM MaterialsPurchase;
SELECT * FROM MaterialsStock;
SELECT * FROM MaterialsUsage;

Validate indexes creation

List all indexes on the database
SELECT
[Link] AS IndexName,
[Link] AS SchemaName,
[Link] AS TableName,
i.type_desc AS IndexType
FROM
[Link] i
JOIN
[Link] t ON i.object_id = t.object_id
JOIN
[Link] s ON t.schema_id = s.schema_id
WHERE
i.is_disabled = 0 -- Exclude disabled indexes
ORDER BY
[Link], [Link], [Link];

2. Validate the indexes created in the script:

-- Project table indexes

57
SELECT * FROM [Link] WHERE name IN ('idx_project_projectname',
'idx_project_projectmanager', 'idx_project_projectstartdate', 'idx_project_projectenddate') AND
object_id = OBJECT_ID('Project');

-- Property table indexes

SELECT * FROM [Link] WHERE name IN ('idx_property_propertyaddress',
'idx_property_propertyowner', 'idx_property_projectid') AND object_id =
OBJECT_ID('Property');

-- Contractor table indexes

SELECT * FROM [Link] WHERE name IN ('idx_contractor_contractorname',
'idx_contractor_contractorspecialty', 'idx_contractor_projectid') AND object_id =
OBJECT_ID('Contractor');

-- ConstructionPhase table indexes

SELECT * FROM [Link] WHERE name IN ('idx_constructionphase_phasename',
'idx_constructionphase_projectid', 'idx_constructionphase_contractorid') AND object_id =
OBJECT_ID('ConstructionPhase');

-- MaterialsPurchase table indexes

SELECT * FROM [Link] WHERE name IN ('idx_materialspurchase_materialtype',
'idx_materialspurchase_materialsupplier', 'idx_materialspurchase_projectid',
'idx_materialspurchase_propertyid', 'idx_materialspurchase_contractorid') AND object_id =
OBJECT_ID('MaterialsPurchase');

-- MaterialsStock table indexes

SELECT * FROM [Link] WHERE name IN ('idx_materialsstock_materialtype',
'idx_materialsstock_materiallocation', 'idx_materialsstock_projectid',
'idx_materialsstock_propertyid', 'idx_materialsstock_contractorid') AND object_id =
OBJECT_ID('MaterialsStock');

-- MaterialsUsage table indexes

58
SELECT * FROM [Link] WHERE name IN ('idx_materialsusage_materialtype',
'idx_materialsusage_constructionphaseid', 'idx_materialsusage_contractorid',
'idx_materialsusage_projectid') AND object_id = OBJECT_ID('MaterialsUsage');

4. Reference
• Attachement: Implemented database files (*.mdf and *.ldf)

[Link]
Data E:\final\MYSQL\MSSQL16.MSSQLSERVER01\MSSQL\Binn\[Link]

[Link]
Log E:\final\MYSQL\MSSQL16.MSSQLSERVER01\MSSQL\Binn\[Link]

59