Database Design for SCIRT | ER Diagram, DDL, Data Requirements

This report discusses the database design for Stronger Christchurch Infrastructure Rebuild Team (SCIRT) including ER diagram, DDL, data requirements, and limitations. It explains the entities in the logical data model and their relationships. The report also highlights the assumptions and limitations of the system.

Running head: DATABASE DESIGN
Database Design
Name of the Student:
Name of the University:
Author Note

1
DATABASE DESIGN
ER Diagram
DDL
-- MySQL Script generated by MySQL Workbench
-- Sat Jun 30 12:04:09 2018
-- Model: New Model Version: 1.0
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

2
DATABASE DESIGN
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE,
SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Schema SCIRT
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `SCIRT` ;
-- -----------------------------------------------------
-- Schema SCIRT
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `SCIRT` DEFAULT CHARACTER SET utf8 ;
SHOW WARNINGS;
USE `SCIRT` ;
-- -----------------------------------------------------
-- Table `SCIRT`.`ProjectArea`

3
DATABASE DESIGN
-- -----------------------------------------------------
DROP TABLE IF EXISTS `SCIRT`.`ProjectArea` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `SCIRT`.`ProjectArea` (
`AreaID` INT NOT NULL,
`Name` VARCHAR(45) NOT NULL,
`Location` VARCHAR(45) NOT NULL,
PRIMARY KEY (`AreaID`))
ENGINE = InnoDB;
SHOW WARNINGS;
CREATE UNIQUE INDEX `AreaID_UNIQUE` ON `SCIRT`.`ProjectArea` (`AreaID` ASC);
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `SCIRT`.`AssementResult`

4
DATABASE DESIGN
-- -----------------------------------------------------
DROP TABLE IF EXISTS `SCIRT`.`AssementResult` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `SCIRT`.`AssementResult` (
`ResultID` INT NOT NULL,
`ConditionID` INT NOT NULL,
`Result` VARCHAR(45) NULL,
`Remarks` VARCHAR(45) NULL,
`HydraulicPerformance` VARCHAR(45) NULL,
`AssementResultcol` VARCHAR(45) NULL,
`PipeCondition` VARCHAR(45) NULL,
PRIMARY KEY (`ResultID`))
ENGINE = InnoDB;
SHOW WARNINGS;
CREATE UNIQUE INDEX `ResultID_UNIQUE` ON `SCIRT`.`AssementResult` (`ResultID`
ASC);

5
DATABASE DESIGN
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `SCIRT`.`CostofAssests`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `SCIRT`.`CostofAssests` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `SCIRT`.`CostofAssests` (
`CostID` INT NOT NULL,
`ConditionID` INT NOT NULL,
`Description` VARCHAR(45) NULL,
`Cost` INT NULL,
PRIMARY KEY (`CostID`))
ENGINE = InnoDB;
SHOW WARNINGS;
CREATE UNIQUE INDEX `CostID_UNIQUE` ON `SCIRT`.`CostofAssests` (`CostID` ASC);

6
DATABASE DESIGN
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `SCIRT`.`ConditionInfo`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `SCIRT`.`ConditionInfo` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `SCIRT`.`ConditionInfo` (
`ConditionID` INT NOT NULL,
`Description` VARCHAR(45) NULL,
`Status` VARCHAR(45) NOT NULL,
`AssetID` INT NOT NULL,
`AssementResult_ResultID` INT NOT NULL,
`CostofAssests_CostID` INT NOT NULL,
`CCTV` VARCHAR(45) NULL,
`ManholeLevel` VARCHAR(45) NULL,

7
DATABASE DESIGN
`PoleCamera` VARCHAR(45) NULL,
`PipeProfile` VARCHAR(45) NULL,
PRIMARY KEY (`ConditionID`),
CONSTRAINT `fk_ConditionInfo_AssementResult1`
FOREIGN KEY (`AssementResult_ResultID`)
REFERENCES `SCIRT`.`AssementResult` (`ResultID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_ConditionInfo_CostofAssests1`
FOREIGN KEY (`CostofAssests_CostID`)
REFERENCES `SCIRT`.`CostofAssests` (`CostID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SHOW WARNINGS;
CREATE UNIQUE INDEX `ConditionID_UNIQUE` ON `SCIRT`.`ConditionInfo`
(`ConditionID` ASC);

8
DATABASE DESIGN
SHOW WARNINGS;
CREATE INDEX `fk_ConditionInfo_AssementResult1_idx` ON `SCIRT`.`ConditionInfo`
(`AssementResult_ResultID` ASC);
SHOW WARNINGS;
CREATE INDEX `fk_ConditionInfo_CostofAssests1_idx` ON `SCIRT`.`ConditionInfo`
(`CostofAssests_CostID` ASC);
SHOW WARNINGS;
-- -----------------------------------------------------
-- Table `SCIRT`.`Assests`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `SCIRT`.`Assests` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `SCIRT`.`Assests` (
`AssetID` INT NOT NULL,
`Item` VARCHAR(45) NULL,

9
DATABASE DESIGN
`AreaID` INT NOT NULL,
`ProjectArea_AreaID` INT NOT NULL,
`ConditionInfo_ConditionID` INT NOT NULL,
PRIMARY KEY (`AssetID`),
CONSTRAINT `fk_Assests_ProjectArea`
FOREIGN KEY (`ProjectArea_AreaID`)
REFERENCES `SCIRT`.`ProjectArea` (`AreaID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Assests_ConditionInfo1`
FOREIGN KEY (`ConditionInfo_ConditionID`)
REFERENCES `SCIRT`.`ConditionInfo` (`ConditionID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SHOW WARNINGS;
CREATE UNIQUE INDEX `AssetID_UNIQUE` ON `SCIRT`.`Assests` (`AssetID` ASC);

10
DATABASE DESIGN
SHOW WARNINGS;
CREATE INDEX `fk_Assests_ProjectArea_idx` ON `SCIRT`.`Assests` (`ProjectArea_AreaID`
ASC);
SHOW WARNINGS;
CREATE INDEX `fk_Assests_ConditionInfo1_idx` ON `SCIRT`.`Assests`
(`ConditionInfo_ConditionID` ASC);
SHOW WARNINGS;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SCIRT information/data requirements
The case study which is involved for this report is that of the Stronger Christchurch
Infrastructure Rebuild Team. The organization is looking to rebuild the public own facilities and
the infrastructures which were damaged by the earthquakes. In addition to this, the core of
organization is formed by three to five other smaller organizations. It was funded by the New
Zealand tax payers at first and later on the rate payers added their investment. The organization

11
DATABASE DESIGN
is looking to repair and improve the overall conditions. In addition to this, the organization is
also looking to involve the constructor partners and address the issues regarding the area and
disabilities in the area of the systems. The team therefore has to store the details of the area that
requires to be evaluated and make the changes in the area accordingly. In addition to this all the
details of the area are stored in the system along with the assessment results. The tools required
for the development of the area are also very costly and hence, for the development of the are
some development strategies are formed and the details are stored in the system accordingly.
According to the business rules of the organization the GIS function is required to includes a
number of data storing procedures in the system and hence the need of a database came into
being. The data requirements of the SCRIT organization are Storing condition information and
assessment results, storing asset information, identifying project areas and attributing costs to
individual assets. Hence the organization requires to store the data of the assets, the areas in
which the assets are present. In addition to this, the database should also store the results of
conditions of the assets and the cost for the assets. In addition to this the remarks for the
conditions of the assets are also stored in the database.
Descriptions of the entities in the logical data model realized as the ER
diagram
There are five entities in the ER diagram which are assets, project area, ConditionInfo,
CostOfAssests and Assesment results. The Assets table store the details of the assets in the area
indexed by the assetID, additionally the information about the project area are also stored in the
system which provides the information about the project and the location details. The
conditioninfo provides the details of the condition of the assets and the costofassets table provide
the details of the cost of the assets. In the ER diagram there are five entities that is assets, project

12
DATABASE DESIGN
area, condition info, assessment result, cost of assets. The one to many relationships can be seen
between the entities that is assets and project area. While one to one relation can be easily seen
between the entity assets and conditional info. One to one relationship can be seen between the
entities that is assessment result and condition info. One to one relationship can be seen between
cost of assets and condition info. The asset entity consists of various kinds of attribute like
AssetID INT, Item VARCHAR (45), AREAID INT, ProjectArea_AreaID INT,
ConditionInfo_ConditionID Int. The entity consists of some primary keys like AssestID_Unique,
fk _Assests_ProjectArea_idx, fk_ Assests_conditioninfo 1_indx. The project area entity mainly
consists of some attribute like AreaID which is of INT type, Name which has VARCHAR (45),
location which has a VARCHAR (45). It comes up with primary key that is AreaID_ Unique.
The conditionInfo entity consist of large number of primary key like ConditionID_Unique. It
also has fk_conditioninfo_AssesementResult1_idx and fk_conditioninfo_cost of assets1_idx.
The primary entity consists of attribute like ResultID INT, condition ID INT, result which comes
up with VARCHAR (45) and remark VARCHAR (45). It has a primary key which has a
ResultID_unique. The last entity consists of five attribute which is of integer type that is CostID,
ConditionID, Cost and lastly description which has VARCHAR (45). It has primary key
costID_unique.

13
DATABASE DESIGN
Assumptions and limitations
It has been assumed that a single area can have a number of assets and for these assets an
evaluation report is done and the results of the report are stored in the assessment. The data
storage is limited only to the data in the assets table that means a result of condition info cannot
have the data of the assets which are not in the assets table. It has been assumed that the cost of
the tools in the system would be stored which can be managed manually to provide the team with
the full cost to develop the area. Additionally, there various other features of the system that
cannot be linked with this database. The development strategy cannot be stored in the system in
the form of a table and hence, it has not been including in the database. It is to be documented
separately in the system. Hence, these are the few limitations to the system. Additionally, the
total cost can be generated from the system, but the query for the system is to be done manually
for obtaining the result.

14
DATABASE DESIGN
Bibliography
Ahmed, S., Coulibaly, B., & Zlate, A. (2017). International financial spillovers to emerging
market economies: How important are economic fundamentals?. Journal of International
Money and Finance, 76, 133-152.
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
Elmasri, Ramez, and Shamkant B. Navathe. Fundamentals of Database Systems, Global Edition.
Pearson Education Limited, 2015.
Groom, C. R., Bruno, I. J., Lightfoot, M. P., & Ward, S. C. (2016). The Cambridge structural
database. Acta Crystallographica Section B: Structural Science, Crystal Engineering and
Materials, 72(2), 171-179.