rdb_build.rmd

---
title: "Design and build a RDb"
author: "Jai Iyyappa Vignesh Manivannan"
output:
  html_document:
    df_print: paged
---

####Implementing my ER diagram into relational table using MySQL.


```{r}
# Installing the RMySQL Library
library(RMySQL)


#  MySQL Settings
db_user <- 'admin'
db_password <- 'dbpass'
db_name <- 'dbname'
db_host <- 'hostname'
db_port <- 3306

# Establishing a connection to MySQL
mydb <-  dbConnect(MySQL(), user = db_user, password = db_password,
                  dbname = db_name, host = db_host, port = db_port)


```


### Load CSV Data


```{r loadCSV}
fn <- "BirdStrikesData.csv"

df.raw <- read.csv(file = fn,
                   header = T,
                   stringsAsFactors = F)
```


```{r displayLoadedcsv}
head(df.raw, 3)
```

### Omitting empty aircraft data
#### All entries which didn't have aircraft info didn't have the airport information, so omitting it.

```{r removeEmptyAircraftDataFromexcel}

totalRowBeforeOmit <- nrow(df.raw)  

df.newRaw <- df.raw

if(any(df.raw$ Airport..Name == "")) {
  rs = which(df.raw$ Airport..Name == "") 

  df.newRaw <- df.raw[-rs,]
}

totalNewRowAfterOmit <- nrow(df.newRaw)


```

```{r message=FALSE}

detach("package:RMySQL")

library(sqldf)
```
### Table creation based on the ER diagram

* The data is divided to 8 lookup table and 3 information table which is stored based on recordId

### Dropping reference table Accidents

```{sql connection=mydb}
DROP TABLE IF EXISTS Accidents
```


### Creation of table Airport

* airportId is the PRIMARY KEY

```{sql connection=mydb}
DROP TABLE IF EXISTS Airport
```


```{sql connection=mydb}
CREATE TABLE Airport(
airportId INT NOT NULL,
airportName VARCHAR(255) NOT NULL,
PRIMARY KEY (airportId)
)
```

### Extract the Airport Data from excel

```{r extractAirportData}

# Extracting the airport data from excel into a dataframe df.Airport.
df.Airport <- sqldf::sqldf("select 1 as airportId, `Airport..Name` as airportName from `df.newRaw` group by airportName")

# Calculating the total number of rows in df.Airport created. 

n.Airport <- nrow(df.Airport)

# Changing the sequence number of hard-coded airportId value for PK.
df.Airport[,1] <- seq(1, n.Airport)


```

### Inserting the airport data into table
```{r insertAirportDataIntoTable}
# dbWriteTable is used with append to insert the data from dataframe into table.
dbWriteTable(mydb, 'Airport', df.Airport, row.names=F,append=T, overwrite=F)
```

### Display Airport table

```{sql connection=mydb}
SELECT * FROM Airport
       LIMIT 5;
```



### Creation of table Airline

* airlineId is the PRIMARY KEY

```{sql connection=mydb}
DROP TABLE IF EXISTS Airline
```


```{sql connection=mydb}
CREATE TABLE Airline(
airlineId INT NOT NULL,
airlineOperator VARCHAR(255) NOT NULL,
PRIMARY KEY (airlineId)
)
```


### Extract the Airline Data from excel

```{r extractAirlineData}

# Extracting the Airline data from excel into a dataframe df.Airline
df.Airline <- data.frame(
                         airlineId = 1,
                         airlineOperator = unique(df.newRaw$Aircraft..Airline.Operator)
                         )

# Calculating the total number of rows in df.Airline created. 
n.Airline <- nrow(df.Airline)

# Changing the sequence number of hard-coded airlineId value for PK.
df.Airline[,1] <- seq(1, n.Airline)

```

### Inserting the Airline data into table
```{r insertAirlineDataIntoTable}
# dbWriteTable is used with append to insert the data from dataframe into table.
dbWriteTable(mydb, 'Airline', df.Airline, row.names=F, append=T, overwrite=F)
```

### Display Airline table

```{sql connection=mydb}
SELECT * FROM Airline
       LIMIT 5;
```



### Creation of table Aircraft

* aircraftId is the PRIMARY KEY

```{sql connection=mydb}
DROP TABLE IF EXISTS Aircraft
```


```{sql connection=mydb}
CREATE TABLE Aircraft(
aircraftId INT NOT NULL,
aircraftMake VARCHAR(100) NOT NULL,
PRIMARY KEY (aircraftId)
)
```

### Extract the Aircraft Data from excel

```{r extractAircraftData}

# Extracting the Aircraft data from excel into a dataframe df.Aircraft
df.Aircraft <- data.frame(
                         aircraftId = 1,
                         aircraftMake = unique(df.newRaw$Aircraft..Make.Model)
                         )

# Calculating the total number of rows in df.Aircraft created. 
n.Aircraft <- nrow(df.Aircraft)

# Changing the sequence number of hard-coded aircraftId value for PK.
df.Aircraft[,1] <- seq(1, n.Aircraft)


```

### Inserting the Aircraft data into table
```{r insertAircraftDataIntoTable}
# dbWriteTable is used with append to insert the data from dataframe into table.
dbWriteTable(mydb, 'Aircraft', df.Aircraft, row.names=F, append=T, overwrite=F)
```

### Display Aircraft table

```{sql connection=mydb}
SELECT * FROM Aircraft
       LIMIT 5;
```



### Creation of table PhaseOfFlight

* phaseId is the PRIMARY KEY

```{sql connection=mydb}
DROP TABLE IF EXISTS PhaseOfFlight
```


```{sql connection=mydb}
CREATE TABLE PhaseOfFlight(
phaseId INT NOT NULL,
phaseName VARCHAR(100) NOT NULL,
PRIMARY KEY (phaseId)
)
```


### Extract the PhaseOfFlight Data from excel

```{r extractPhaseOfFlightData}

# Extracting the PhaseOfFlight data from excel into a dataframe df.PhaseOfFlight with hardcoded phaseId
df.PhaseOfFlight <- data.frame(
                         phaseId = 1,
                         phaseName = unique(df.newRaw$When..Phase.of.flight)
                         )

# Calculating the total number of rows in df.PhaseOfFlight created. 
n.PhaseOfFlight <- nrow(df.PhaseOfFlight)

# Changing the sequence number of hard-coded phaseId value for PK.
df.PhaseOfFlight[,1] <- seq(1, n.PhaseOfFlight)


```

### Inserting the PhaseOfFlight data into table
```{r insertPhaseOfFlightDataIntoTable}
# dbWriteTable is used with append to insert the data from dataframe into table.
dbWriteTable(mydb, 'PhaseOfFlight', df.PhaseOfFlight, row.names=F, append=T, overwrite=F)
```

#### Display PhaseOfFlight table

```{sql connection=mydb}
SELECT * FROM PhaseOfFlight
       LIMIT 5;
```



### Creation of table Wildlife

* wildlifeId is the PRIMARY KEY

```{sql connection=mydb}
DROP TABLE IF EXISTS Wildlife
```


```{sql connection=mydb}
CREATE TABLE Wildlife(
wildlifeId INT NOT NULL,
wildlifeName VARCHAR(100) NOT NULL,
PRIMARY KEY (wildlifeId)
)
```


### Extract the Wildlife Data from excel

```{r extractWildlifeData}

# Extracting the Wildlife data from excel into a dataframe df.Wildlife with hardcoded wildlifeId
df.Wildlife <- data.frame(
                         wildlifeId = 1,
                         wildlifeName = unique(df.newRaw$Wildlife..Species)
                         )





# Calculating the total number of rows in df.Wildlife created. 
n.Wildlife <- nrow(df.Wildlife)

# Changing the sequence number of hard-coded wildlifeId value for PK.
df.Wildlife[,1] <- seq(1, n.Wildlife)


```

### Inserting the Wildlife data into table
```{r insertWildlifeIntoTable}
# dbWriteTable is used with append to insert the data from dataframe into table.
dbWriteTable(mydb, 'Wildlife', df.Wildlife, row.names=F, append=T, overwrite=F)
```


### Display Wildlife table

```{sql connection=mydb}
SELECT * FROM Wildlife
       LIMIT 5;
```

### Creation of table precipitateCondition

* pConditionId is the PRIMARY KEY

```{sql connection=mydb}
DROP TABLE IF EXISTS accidentPrecipitateMap
```

```{sql connection=mydb}
DROP TABLE IF EXISTS precipitateCondition
```


```{sql connection=mydb}
CREATE TABLE precipitateCondition(
pConditionId INT NOT NULL,
pConditionName VARCHAR(100) NOT NULL,
PRIMARY KEY (pConditionId)
)
```

### Extract the precipitateCondition Data from excel

```{r extractprecipitateConditionData}

s <- strsplit(unique(df.newRaw$Conditions..Precipitation) , ",") 

s <- unlist(s)

s <- gsub( " ", "", s) 

# Extracting the precipitateCondition data from excel into a dataframe df.precipitateCondition with hardcoded pConditionId
df.precipitateCondition <- data.frame(
                         pConditionId = 1,
                         pConditionName = unique(s)
                         )

# Calculating the total number of rows in df.precipitateCondition created.
n.precipitateCondition <- nrow(df.precipitateCondition)

# Changing the sequence number of hard-coded pConditionId value for PK.
df.precipitateCondition[,1] <- seq(1, n.precipitateCondition)

 
```


### Inserting the precipitateCondition data into table
```{r insertprecipitateConditionIntoTable}
# dbWriteTable is used with append to insert the data from dataframe into table.
dbWriteTable(mydb, 'precipitateCondition', df.precipitateCondition, row.names=F, append=T, overwrite=F)
```


### Display precipitateCondition table

```{sql connection=mydb}
SELECT * FROM precipitateCondition
       LIMIT 5;
```

### Creation of table accidentPrecipitateMap

* recordPrecipMapId is the PRIMARY KEY
* pConditionId is the FOREIGN KEY references the value in precipitateCondition table

```{sql connection=mydb}
DROP TABLE IF EXISTS accidentPrecipitateMap
```

```{sql connection=mydb}
CREATE TABLE accidentPrecipitateMap(
recordPrecipMapId INT NOT NULL,
recordId INT NOT NULL,
pConditionId INT NOT NULL,
PRIMARY KEY (recordPrecipMapId),
FOREIGN KEY (pConditionId) REFERENCES precipitateCondition(pConditionId)
)
```


```{r extractaccidentPrecipitateMapData}

# separating the multi valued column values into singular value.
totalPreciData <- strsplit(df.newRaw$Conditions..Precipitation , ",") 

# Creating a data frame with unique pricipitation value - removing redundant data.
# gsub removes space.

# Extracting the accidentPrecipitateMap data from excel into a dataframe df.accidentPrecipitateMap with hardcoded recordPrecipMapId
df.accidentPrecipitateMap <- data.frame(
                         recordPrecipMapId = 1,
                         recordId = rep(df.newRaw$ï..Record.ID, sapply(totalPreciData,length)),
                         pConditionId = gsub( " ", "",unlist(totalPreciData))
                         
                         )


# Calculating the total number of rows in df.accidentPrecipitateMap created.
n.accidentPrecipitateMap <- nrow(df.accidentPrecipitateMap)

# Changing the sequence number of hard-coded recordPrecipMapId value for PK.
df.accidentPrecipitateMap[,1] <- seq(1, n.accidentPrecipitateMap)


tempaAccidentPrecipitateMapRs <- df.accidentPrecipitateMap

# Linking the references from the lookup table with corresponding Id.
for (r in 1:nrow(tempaAccidentPrecipitateMapRs)){
  
  
  precipCond <- df.precipitateCondition$pConditionId[which(df.precipitateCondition$pConditionName == tempaAccidentPrecipitateMapRs$pConditionId[r])]
  
  tempaAccidentPrecipitateMapRs$pConditionId[r] <- precipCond
  
  
}

tempaAccidentPrecipitateMapRs$pConditionId <- as.integer(as.character(tempaAccidentPrecipitateMapRs$pConditionId))


df.accidentPrecipitateMap <- tempaAccidentPrecipitateMapRs

 
```


### Inserting the accidentPrecipitateMap data into table
```{r insertaccidentPrecipitateMapIntoTable}
# dbWriteTable is used with append to insert the data from dataframe into table.
dbWriteTable(mydb, 'accidentPrecipitateMap', df.accidentPrecipitateMap, row.names=F, append=T, overwrite=F)
```


### Display accidentPrecipitateMap table

```{sql connection=mydb}
SELECT * FROM accidentPrecipitateMap
       LIMIT 5;
```


### Drop of table accidentCondition 

```{sql connection=mydb}
DROP TABLE IF EXISTS accidentCondition
```

### Creation of table originState


* stateId is the PRIMARY KEY

```{sql connection=mydb}
DROP TABLE IF EXISTS originState
```


```{sql connection=mydb}
CREATE TABLE originState(
stateId INT NOT NULL,
stateName VARCHAR(100) NOT NULL,
PRIMARY KEY (stateId)
)
```


### Extract the originState Data from excel

```{r extractoriginStateData}

# Extracting the originState data from excel into a dataframe df.originState with hardcoded stateId
df.originState <- data.frame(
                         stateId = 1,
                         stateName = unique(df.newRaw$Origin.State)
                         )

# Calculating the total number of rows in df.originState created. 
n.originState <- nrow(df.originState)

# Changing the sequence number of hard-coded stateId value for PK.
df.originState[,1] <- seq(1, n.originState)


```

### Inserting the originState data into table
```{r insertoriginStateIntoTable}
# dbWriteTable is used with append to insert the data from dataframe into table.
dbWriteTable(mydb, 'originState', df.originState, row.names=F, append=T, overwrite=F)
```

### Display originState table

```{sql connection=mydb}
SELECT * FROM originState
       LIMIT 5;
```



### Creation of table skyCondition

* sConditionId is the PRIMARY KEY

```{sql connection=mydb}
DROP TABLE IF EXISTS skyCondition
```


```{sql connection=mydb}
CREATE TABLE skyCondition(
sConditionId INT NOT NULL,
sConditionName VARCHAR(100) NOT NULL,
PRIMARY KEY (sConditionId)
)
```


### Extract the skyCondition Data from excel

```{r extractskyConditionData}

# Extracting the skyCondition data from excel into a dataframe df.skyCondition with hardcoded sConditionId
df.skyCondition <- data.frame(
                         sConditionId = 1,
                         sConditionName = unique(df.newRaw$Conditions..Sky)
                         )

# Calculating the total number of rows in df.skyCondition created.
n.skyCondition <- nrow(df.skyCondition)

# Changing the sequence number of hard-coded sConditionId value for PK.
df.skyCondition[,1] <- seq(1, n.skyCondition)
 
```

### Inserting the skyCondition data into table
```{r insertskyConditionIntoTable}
# dbWriteTable is used with append to insert the data from dataframe into table.
dbWriteTable(mydb, 'skyCondition', df.skyCondition, row.names=F, append=T, overwrite=F)

```
### Display skyCondition table

```{sql connection=mydb}
SELECT * FROM skyCondition
       LIMIT 5;
```

### Creation of table accidentCondition

* recordId is the PRIMARY KEY
* stateId is the FOREIGN KEY references the stateId from originState table
* sConditionId is the FOREIGN KEY references the sConditionId from skyCondition table

```{sql connection=mydb}
DROP TABLE IF EXISTS accidentCondition
```


```{sql connection=mydb}
CREATE TABLE accidentCondition(
recordId INT NOT NULL,
sConditionId INT NOT NULL,
pConditionName VARCHAR(100),
stateId INT NOT NULL,
PRIMARY KEY (recordId),
FOREIGN KEY (stateId) REFERENCES originState(stateId),
FOREIGN KEY (sConditionId) REFERENCES skyCondition(sConditionId)
)
```


### table accidentCondition extract data from excel
```{r accidentCondition}

# Extracting the accidentCondition data from excel into a dataframe df.accidentCondition with hardcoded recordId
df.accidentCondition <- data.frame(
                         recordId = df.newRaw$ï..Record.ID,
                         sConditionId = df.newRaw$Conditions..Sky,
                         pConditionName = df.newRaw$Conditions..Precipitation,
                         stateId = df.newRaw$Origin.State
                         
                         )


tempaccidentConditionRs <- df.accidentCondition

# linking the foreign key value from reference dataframes df.skyCondition and df.originState whcih are used to build table.

for (r in 1:nrow(tempaccidentConditionRs)){
  
  
  skyCondition <- df.skyCondition$sConditionId[which(df.skyCondition$sConditionName == tempaccidentConditionRs$sConditionId[r])]
  
  tempaccidentConditionRs$sConditionId[r] <- skyCondition
  
  

  state <- df.originState$stateId[which(df.originState$stateName == tempaccidentConditionRs$stateId[r])]
  
  tempaccidentConditionRs$stateId[r] <- state
  
  
  
}

tempaccidentConditionRs$sConditionId <- as.integer(as.character(tempaccidentConditionRs$sConditionId))
tempaccidentConditionRs$stateId <- as.integer(as.character(tempaccidentConditionRs$stateId))


df.accidentCondition <- tempaccidentConditionRs


```


### Insert accidentCondition Data into table

```{r accidentConditionToTable}
# dbWriteTable is used with append to insert the data from dataframe into table.
dbWriteTable(mydb, "accidentCondition", df.accidentCondition, row.names=F,  append=T, overwrite=F)

```


### Display Accidents table

```{sql connection=mydb}
SELECT * FROM accidentCondition
       LIMIT 5;
```


### Creation of table damageData

* recordId is the PRIMARY KEY

```{sql connection=mydb}
DROP TABLE IF EXISTS damageData
```


```{sql connection=mydb}
CREATE TABLE damageData(
recordId INT NOT NULL,
impactToFlight VARCHAR(255),
IndicatedDamage VARCHAR(255),
wildlifeRemainsCollected BOOLEAN,
wildlifeRemainsSmithsonian BOOLEAN,
numberOfPeopleInjured INT,
cost INT,
remarks VARCHAR(1000) default 'NA',
PRIMARY KEY (recordId)
)
```


### Extract the damage Data from excel

```{r DamageData}

# Extracting the damage data from excel into a dataframe df.damageData

df.damageData <- data.frame(
                         recordId = df.newRaw$ï..Record.ID,
                         impactToFlight = df.newRaw$Effect..Impact.to.flight,
                         IndicatedDamage = df.newRaw$Effect..Indicated.Damage,
                         wildlifeRemainsCollected = df.newRaw$Remains.of.wildlife.collected.,
                         wildlifeRemainsSmithsonian = df.newRaw$Remains.of.wildlife.sent.to.Smithsonian,
                         numberOfPeopleInjured = df.newRaw$Number.of.people.injured,
                         cost = df.newRaw$Cost..Total..,
                         remarks = gsub("'", "''", df.newRaw$Remarks)
                         )



n.damageData <- nrow(df.damageData)


```


### Inserting the damageData data into table
```{r insertdamageDataIntoTable}
# dbWriteTable is used with append to insert the data from dataframe into table.
dbWriteTable(mydb, "damageData", df.damageData, row.names=F, append=T, overwrite=F)
```

### Display damageData table

```{sql connection=mydb}
SELECT * FROM damageData
       LIMIT 5;
```


### Creation of table Accidents

* recordId is the PRIMARY KEY
* airportId is the FOREIGN KEY references the airportId from Airport table
* airlineId is the FOREIGN KEY references the airlineId from Airline table
* aircraftId is the FOREIGN KEY references the aircraftId from Aircraft table
* phaseOfFlight is the FOREIGN KEY references the phaseOfFlight from PhaseOfFlight table
* wildlifeSpecies is the FOREIGN KEY references the wildlifeSpecies from Wildlife table


```{sql connection=mydb}
DROP TABLE IF EXISTS Accidents
```


```{sql connection=mydb}
CREATE TABLE Accidents(
recordId INT NOT NULL,
airportId INT NOT NULL,
airlineId INT NOT NULL,
aircraftId INT NOT NULL,
flightDate DATE,
feetAboveGround INT,
WildlifeNumberStruckActual INT,
phaseOfFlight INT,
wildlifeSpecies INT,
PRIMARY KEY (recordId),
FOREIGN KEY (airportId) REFERENCES Airport(airportId),
FOREIGN KEY (airlineId) REFERENCES Airline(airlineId),
FOREIGN KEY (aircraftId) REFERENCES Aircraft(aircraftId),
FOREIGN KEY (phaseOfFlight) REFERENCES PhaseOfFlight(phaseId),
FOREIGN KEY (wildlifeSpecies) REFERENCES Wildlife(wildlifeId)
)
```


### Accidents Data

```{r AccidentsData}

# Extracting the Accidents data from excel into a dataframe df.Accidents

df.Accidents <- data.frame(
                         recordId = df.newRaw$ï..Record.ID,
                         airportId = df.newRaw$Airport..Name,
                         airlineId = df.newRaw$Aircraft..Airline.Operator,
                         aircraftId = df.newRaw$Aircraft..Make.Model,
                         flightDate = df.newRaw$FlightDate,
                         feetAboveGround = df.newRaw$Feet.above.ground,
                         WildlifeNumberStruckActual = df.newRaw$Wildlife..Number.Struck.Actual,
                         phaseOfFlight = df.newRaw$When..Phase.of.flight,
                         wildlifeSpecies = df.newRaw$Wildlife..Species
                         
                         )


tempAccidentRs <- df.Accidents

# linking the foreign key value from reference table

for (r in 1:nrow(tempAccidentRs)){
  
  
  airport <- df.Airport$airportId[which(df.Airport$airportName == tempAccidentRs$airportId[r])]
  
  tempAccidentRs$airportId[r] <- airport
  
  

  airline <- df.Airline$airlineId[which(df.Airline$airlineOperator == tempAccidentRs$airlineId[r])]
  
  tempAccidentRs$airlineId[r] <- airline
  
 
  
  aircraft <- df.Aircraft$aircraftId[which(df.Aircraft$aircraftMake == tempAccidentRs$aircraftId[r])]
  
  tempAccidentRs$aircraftId[r] <- aircraft
  
  
  
  flightPhase <- df.PhaseOfFlight$phaseId[which(df.PhaseOfFlight$phaseName == tempAccidentRs$phaseOfFlight[r])]
  
  tempAccidentRs$phaseOfFlight[r] <- flightPhase
  
  
  
  wildLifeName <- df.Wildlife$wildlifeId[which(df.Wildlife$wildlifeName == tempAccidentRs$wildlifeSpecies[r])]
  
  tempAccidentRs$wildlifeSpecies[r] <- wildLifeName
  
  
  
}

#Casting all characters to integer type
tempAccidentRs$airportId <- as.integer(as.character(tempAccidentRs$airportId))
tempAccidentRs$airlineId <- as.integer(as.character(tempAccidentRs$airlineId))
tempAccidentRs$aircraftId <- as.integer(as.character(tempAccidentRs$aircraftId))
tempAccidentRs$phaseOfFlight <- as.integer(as.character(tempAccidentRs$phaseOfFlight))
tempAccidentRs$wildlifeSpecies <- as.integer(as.character(tempAccidentRs$wildlifeSpecies))


# Converting the date format present
tempAccidentRs$flightDate <- as.Date(tempAccidentRs$flightDate, tryFormats = c("%m-%d-%Y", "%m/%d/%Y"),
        optional = FALSE)
  

df.Accidents <- tempAccidentRs


```

### Insert Accidents Data into table

```{r AccidentsDataToTable}
# dbWriteTable is used with append to insert the data from dataframe into table.
dbWriteTable(mydb, "Accidents", df.Accidents, row.names=F,  append=T, overwrite=F)

```


### Display Accidents table

```{sql connection=mydb}
SELECT * FROM Accidents
       LIMIT 5;
```


## Question 4

```{sql connection=mydb}
SELECT al.airlineOperator, COUNT(recordId) AS 'No of incidents'  FROM Accidents acc
      LEFT JOIN Airline al USING (airlineId)
      WHERE acc.phaseOfFlight = 1 OR
      acc.phaseOfFlight = 4
      GROUP BY al.airlineOperator; 
```


## Question 5

```{sql connection=mydb}
SELECT ap.airportName, COUNT(recordId) AS 'No of incidents'  FROM Accidents acc
      LEFT JOIN Airport ap USING (airportId)
      GROUP BY ap.airportName
      ORDER BY `No of incidents` DESC; 
```

## Question 6

```{sql connection=mydb}
SELECT YEAR(acc.flightDate) AS 'Year', COUNT(recordId) AS 'No of incidents'  FROM Accidents acc
      GROUP BY YEAR(acc.flightDate)
      ORDER BY YEAR(acc.flightDate); 
```


## Question 7

```{r}

# construct a SQL query
sqlCmdData1 = "SELECT YEAR(acc.flightDate) AS 'Year', COUNT(recordId) AS 'No of incidents'  FROM Accidents acc
	                WHERE acc.phaseOfFlight = 1 OR
	                      acc.phaseOfFlight = 4
		              GROUP BY YEAR(acc.flightDate)
			              HAVING `Year` >= 2008 AND
					                 `Year` <= 2011
			              ORDER BY `Year`"

# send the SQL query to the database
tempQ7Data1 = dbGetQuery(mydb, sqlCmdData1)



# construct a SQL query
sqlCmdData2 = "SELECT YEAR(acc.flightDate) AS 'Year', COUNT(recordId) AS 'No of incidents'  FROM Accidents acc
	                WHERE acc.phaseOfFlight = 2 OR
	                      acc.phaseOfFlight = 3 OR
                        acc.phaseOfFlight = 5
		              GROUP BY YEAR(acc.flightDate)
			            HAVING `Year` >= 2008 AND
				                 `Year` <= 2011
			            ORDER BY `Year`"

# send the SQL query to the database
tempQ7Data2 = dbGetQuery(mydb, sqlCmdData2)


```

### Graph Plot with year as X axis and number of incidents as Y

```{r}

# Graph Plot with year as X axis and number of incidents as Y

tempRs <- sqldf("select Year , al.'No of incidents' AS `I1`, ap.'No of incidents' AS `I2` from tempQ7Data1 al
                LEFT JOIN tempQ7Data2 ap USING (Year)")




#Taking the Dataset mentioned in the question
df = data.frame(Block = tempRs$Year, RTreg = tempRs$I1, RTrnd = tempRs$I2)

#Create a Matrix which will help in creating the plot
value_matrix = matrix( nrow = 2, ncol = 4)
value_matrix[1,] = df$RTreg 
value_matrix[2,] = df$RTrnd

#Note that the "beside" argument has to be kept "TRUE" in order to place the bars side by side
yearBar <- barplot(value_matrix, names.arg = df$Block, beside = TRUE, col = c("peachpuff", "skyblue"),
        main="Bird strike incidents in different phase of flight from 2008 to 2011",  ylab="Number of Incidents", 
        xlab = "Year", ylim = c(0, 3500), args.legend = list(title = "Phase of flight", x = "topright"),
        legend.text = c("Take-off/climbing","Descent/approach/landing"))

text(yearBar, value_matrix , labels = value_matrix, xpd = T, srt=90,  adj = c(2,0))

```


### Graph Plot with phase of flight as X axis and number of incidents as Y


```{r}
## Graph Plot with phase of flight as X axis and number of incidents as Y

col1 <- tempQ7Data1$`No of incidents`
col2 <- tempQ7Data2$`No of incidents`

legendcol <- tempQ7Data2$Year


data <- data.frame(col1,col2)
names(data) <- c("Take-off/climbing","Descent/approach/landing")


bar <- barplot(height=as.matrix(data), main="Incidents during different phase of flight from 2008 to 2011",  ylab="Number of Incidents", xlab = "Phase of flight" ,legend.text = legendcol, beside=TRUE,col=rainbow(4), ylim = c(0, 2500),
        args.legend = list(title = "Year", x = "topleft", inset = c(0.10, 0)))


text(bar, as.matrix(data) , labels = as.matrix(data), xpd = T, srt=90,  adj = c(2,0))
```

## Question 7

### Initial check based on condition and getting count

```{sql connection=mydb}

SELECT COUNT(*) 
 	FROM Accidents
	WHERE airlineId = (
	SELECT airlineId 
 	FROM Airline WHERE airlineOperator = 'AIR CANADA'
	);
	
```


### Drop procedure if exists

```{sql connection=mydb}
DROP PROCEDURE DeleteByAirlineName;
```


### Create the procedure with parameter passed into it

```{sql connection=mydb}


CREATE PROCEDURE DeleteByAirlineName(
	IN airLineName VARCHAR(255)
)
BEGIN
	DELETE
 	FROM Accidents
	WHERE airlineId = (
	SELECT airlineId 
 	FROM Airline WHERE airlineOperator = airLineName
	);
end;
```


### Procedure call with parameter

```{sql connection=mydb}

CALL DeleteByAirlineName('AIR CANADA');
	
```

### Initial check based on condition and getting count as 0 after deleting

```{sql connection=mydb}

SELECT COUNT(*) 
 	FROM Accidents
	WHERE airlineId = (
	SELECT airlineId 
 	FROM Airline WHERE airlineOperator = 'AIR CANADA'
	);
	
```

### Disconnectin the DB


```{r}
dbDisconnect(mydb)
```