MBIS623 - Database Design: Implementation of SCIRT Logical Model

This report details a database design and implementation for the Stronger Christchurch Infrastructure Rebuild Team (SCIRT) project, focusing on surface infrastructure, damage assessment, and repair prioritization. The design includes an ER diagram and DDL script for MySQL, covering entities such as ProjectArea, AssementResult, CostofAssests, ConditionInfo, and Assests. The database aims to store condition information, assessment results, asset details, project areas, and associated costs. The report outlines the relationships between entities, key attributes, assumptions, and limitations of the database. The provided SQL script creates the necessary tables and defines relationships to support SCIRT's data requirements. Desklib offers a wealth of resources, including similar solved assignments and past papers, to aid students in their studies.

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