/* Stormtrooper Java Database Created by Gene Locklear March 8, 2018 */ SELECT \

Question

/*
Stormtrooper Java Database
Created by Gene Locklear
March 8, 2018
*/

SELECT 'This is the starting script for our master database' AS 'Professors Locklear Message';

SELECT SLEEP(2) AS 'LOADING....';

DROP DATABASE IF EXISTS stormtrooper_java;
CREATE DATABASE IF NOT EXISTS stormtrooper_java;
USE stormtrooper_java;

CREATE TABLE imperial_battlegroup(
BGID CHAR(5) NOT NULL,
Designation VARCHAR(25) NOT NULL,
HQ_LocationX INT NOT NULL,
HQ_LocationY INT NOT NULL,
CONSTRAINT pk_ibg PRIMARY KEY(BGID)
);

SELECT 'Imperial Battle Groups Created' AS MSG;

CREATE TABLE stormtrooper_unit(
STUID CHAR(10) NOT NULL,
UnitCmd CHAR(5) NOT NULL,
UnitType ENUM('Infantry','Aslt Infantry','Reconnaissance') NOT NULL DEFAULT 'Infantry',
AssignedStrength INT NOT NULL DEFAULT 100,
Location_X INT NOT NULL,
Location_Y INT NOT NULL,
CONSTRAINT pk_stu PRIMARY KEY(STUID),
CONSTRAINT fk_stu FOREIGN KEY(UnitCmd) REFERENCES imperial_battlegroup(BGID)
);

SELECT 'Stormtrooper Units Created' AS MSG;

CREATE TABLE stormtroopers_officer(
STID CHAR(6) NOT NULL,
Rank ENUM('LT','CAPT','CMDR') NOT NULL,
Gender ENUM('Male','Female') NOT NULL DEFAULT 'Male',
ServiceYears INT NOT NULL DEFAULT 3,
Height INT NOT NULL DEFAULT 76,
Weight INT NOT NULL DEFAULT 215,
DutyCategory ENUM('Active','Reserve') NOT NULL DEFAULT 'Active',
DutyStatus ENUM('Full Duty','Wounded','Killed') NOT NULL DEFAULT 'Full Duty',
CONSTRAINT pk_stoff PRIMARY KEY(STID)
);

SELECT 'Stormtrooper Officers Created' AS MSG;

CREATE TABLE st_officer_assign(
STID CHAR(6) NOT NULL,
STUID CHAR(10) NOT NULL,
Role ENUM('Trooper','Communications','Medical','Demolitions','Scout','Assault') NOT NULL DEFAULT 'Trooper',
CONSTRAINT pk_sto_assign PRIMARY KEY(STID),
CONSTRAINT fk_sto_assign1 FOREIGN KEY(STID) REFERENCES stormtroopers_officer(STID),
CONSTRAINT fk_sto_assign2 FOREIGN KEY(STUID) REFERENCES stormtrooper_unit(STUID)
);

SELECT 'Stormtrooper Officer Assignments Created' AS MSG;

CREATE TABLE stormtroopers_nco(
STID CHAR(6) NOT NULL,
Rank ENUM('CPL','SGT','MSGT') NOT NULL,
Gender ENUM('Male','Female') NOT NULL DEFAULT 'Male',
ServiceYears INT NOT NULL DEFAULT 3,
Height INT NOT NULL DEFAULT 76,
Weight INT NOT NULL DEFAULT 215,
DutyCategory ENUM('Active','Reserve') NOT NULL DEFAULT 'Active',
DutyStatus ENUM('Full Duty','Wounded','Killed') NOT NULL DEFAULT 'Full Duty',
CONSTRAINT pk_stnco PRIMARY KEY(STID)
);

SELECT 'Stormtrooper NCOs Created' AS MSG;

CREATE TABLE st_nco_assign(
STID CHAR(6) NOT NULL,
STUID CHAR(10) NOT NULL,
Role ENUM('Trooper','Communications','Medical','Demolitions','Scout','Assault') NOT NULL DEFAULT 'Trooper',
CONSTRAINT pk_stnco_assign PRIMARY KEY(STID),
CONSTRAINT fk_stnco_assign1 FOREIGN KEY(STID) REFERENCES stormtroopers_nco(STID),
CONSTRAINT fk_stnco_assign2 FOREIGN KEY(STUID) REFERENCES stormtrooper_unit(STUID)
);

SELECT 'Stormtrooper NCO Assignments Created' AS MSG;

CREATE TABLE stormtroopers_troop(
STID CHAR(6) NOT NULL,
Rank ENUM('TRPR','SP') NOT NULL,
Gender ENUM('Male','Female') NOT NULL DEFAULT 'Male',
ServiceYears INT NOT NULL DEFAULT 1,
Height INT NOT NULL DEFAULT 76,
Weight INT NOT NULL DEFAULT 215,
DutyCategory ENUM('Active','Reserve') NOT NULL DEFAULT 'Active',
DutyStatus ENUM('Full Duty','Wounded','Killed') NOT NULL DEFAULT 'Full Duty',
CONSTRAINT pk_sttrp PRIMARY KEY(STID)
);

SELECT 'Stormtrooper Troops Created' AS MSG;

CREATE TABLE st_troop_assign(
STID CHAR(6) NOT NULL,
STUID CHAR(10) NOT NULL,
Role ENUM('Trooper','Communications','Medical','Demolitions','Scout','Assault') NOT NULL DEFAULT 'Trooper',
CONSTRAINT pk_sttroop_assign PRIMARY KEY(STID),
CONSTRAINT fk_sttroop_assign1 FOREIGN KEY(STID) REFERENCES stormtroopers_troop(STID),
CONSTRAINT fk_sttroop_assign2 FOREIGN KEY(STUID) REFERENCES stormtrooper_unit(STUID)
);

SELECT 'Stormtrooper Troop Assignments Created' AS MSG;

CREATE TABLE imperial_walker_type(
WTypeID CHAR(2) NOT NULL,
WType CHAR(5) NOT NULL,
Height INT NOT NULL,
Length INT NOT NULL,
Width INT NOT NULL,
Weight INT NOT NULL,
Crew INT NOT NULL,
TroopCapacity INT NOT NULL,
MaximumSpeed INT NOT NULL,
OpRange INT NOT NULL,
CONSTRAINT pk_iwt PRIMARY KEY(WTypeID),
CONSTRAINT uk_iwt UNIQUE KEY(WType)
);

CREATE TABLE walker_units(
WUID CHAR(6) NOT NULL,
BattleGroup CHAR(5) NOT NULL,
Location_X INT NOT NULL,
Location_Y INT NOT NULL,
CONSTRAINT pk_wu PRIMARY KEY(WUID),
CONSTRAINT fk_wu1 FOREIGN KEY(BattleGroup) REFERENCES imperial_battlegroup(BGID)
);

CREATE TABLE imperial_walkers_assign(
WID CHAR(6) NOT NULL,
WUID CHAR(6) NOT NULL,
WalkerType CHAR(5) NOT NULL,
Status ENUM('Operational','Damaged','Destroyed') NOT NULL,
CONSTRAINT pk_iw PRIMARY KEY(WID),
CONSTRAINT fk_iw1 FOREIGN KEY(WUID) REFERENCES walker_units(WUID),
CONSTRAINT fk_iw2 FOREIGN KEY(WalkerType) REFERENCES imperial_walker_type(WType)
);

SELECT 'Transferring to Troop Data Script' AS MSG;

source C:\Users\msgth\OneDrive\Documents\Java Course\trooperData.sql

General Create a single script that contains the following comments: Your Name Your Email Address The Date IS664 Exam 2F Utilize the instructions on the following slides to complete your required script tasks. Complete the task in the order that they are presented. Submit on Blackboard by 6 PM April 25, 2018. - a Cannot be Late. (will not grade otherwise) e Must be sql file. (will not grade otherwise) Do not use DB Build Script...create separate script. Use all available resources. REMEMBER THIS ISAN EXAM...DO NOT COLLABORATE

Explanation / Answer

Part 1)

1) Two tables are joined for this query and If statement is used to find the range between x and y location.
SELECT BGID, Designation, HQ_LocationX, HQ_LocationY, STUID, InitType, Location_X, Location_Y, IF(Location_Y > Location_X, (Location_Y - Location_X), (Location_X - Location_Y)) AS range
FROM imperial_battlegroup ig
INNER JOIN stormtrooper_unit su
ON ig.BGID = su.UnitCmd
WHERE UnitType = 'Aslt Infantry' AND Designation = 'Battle Group 1' AND range > 100;

2) 4 tables are joined in this query. CONCAT function is used to display the output in sampled way. If condition is used to find the range between the two location.
SELECT STID, Rank, Gender, DutyStatus, DutyCategory, Role, STUID, CONCAT(BGID,' ',Designation) AS HQ, Location_X, Location_Y, IF(Location_Y > Location_X, (Location_Y - Location_X), (Location_X - Location_Y)) AS UnitXYRange
FROM imperial_battlegroup ig
INNER JOIN stormtrooper_unit su
ON ig.BGID = su.UnitCmd
INNER JOIN st_officer_assign sfo
ON sfo.STID = su.STID
INNER JOIN stormtroopers_officer so
ON so.STID = sfo.STUID
WHERE DutyCategory = 'Reserve' AND Gender = 'Female' AND Role = 'Communications' AND UnitXYRange < 150;

3) In WHERE clause AND / OR conditions are nested.

SELECT STID, Rank, Gender, DutyStatus, DutyCategory, Role, STUID, CONCAT(BGID,' ',Designation) AS HQ, CONCAT('[HQ Range:', IF(HQ_LocationY > HQ_LocationX, (HQ_LocationY - HQ_LocationX), (HQ_LocationX - HQ_LocationY)), '][Unit Range:', IF(Location_Y > Location_X, (Location_Y - Location_X), (Location_X - Location_Y)),']') AS 'HQ-Unit XY Range'
FROM imperial_battlegroup ig
INNER JOIN stormtrooper_unit su
ON ig.BGID = su.UnitCmd
INNER JOIN st_officer_assign sfo
ON sfo.STID = su.STID
INNER JOIN stormtroopers_officer so
ON so.STID = sfo.STUID
WHERE Gender = 'Female' AND (STUID ='STU-24 OR STUID = 'STU-7');

Note- For rest of the solution please resubmit in parts.

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.