Designing A Database For The SCIRT Project

Information/Data Requirements for the SCIRT Project

ER DiagramDDL

— MySQL Script generated by MySQL Workbench

— Sat Jun 30 12:04:09 2018

— Model: New Model    Version: 1.0

— MySQL Workbench Forward Engineering

SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @[email protected]@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`

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`

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);

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);

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,

  `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);

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,

  `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);

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 [email protected]_SQL_MODE;

SET [email protected]_FOREIGN_KEY_CHECKS;

SET [email protected]_UNIQUE_CHECKS;

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 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.

Developing a Logical Data Model of the Database Implementation

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 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.

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.

References

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.