7. APPENDICES
7.1. APPENDIX A: SQL SCHEMA
7.1. APPENDIX A: SQL SCHEMA
CREATE TABLE SCHEMA Table: game
DROP TABLE IF EXISTS `game`;
CREATE TABLE `game` (
`gameNo` int(10) unsigned NOT NULL auto_increment, `gameTitle` text NOT NULL,
`genre` text NOT NULL, PRIMARY KEY (`gameNo`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8;
Table: center
DROP TABLE IF EXISTS `center`;
CREATE TABLE `center` (
`centerNo` int(10) unsigned NOT NULL auto_increment, `name` text NOT NULL,
PRIMARY KEY (`centerNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Table: promotion
DROP TABLE IF EXISTS `promotion`;
CREATE TABLE `promotion` (
`promotionNo` int(10) unsigned NOT NULL auto_increment, `description` text NOT NULL,
`ratetype` enum('amt','per') NOT NULL, `rate` int(3) unsigned NOT NULL,
`minspent` int(2) NOT NULL default '0',
`promoType` enum('lan','bill','arc','all') NOT NULL, PRIMARY KEY (`promotionNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Table: member
DROP TABLE IF EXISTS `member`;
CREATE TABLE `member` (
`memberNo` int(10) unsigned NOT NULL auto_increment, `name` text NOT NULL,
`identityNo` text NOT NULL, `gender` enum('M','F') NOT NULL, `dob` date NOT NULL,
`address` text NOT NULL, `email` text NOT NULL,
`contact` int(10) unsigned NOT NULL, `occupation` text NOT NULL,
`memberCardNo` int(10) unsigned NOT NULL, `joinedDate` date NOT NULL,
`points` int(10) unsigned NOT NULL,
`pointsExpiry` date NOT NULL, PRIMARY KEY (`memberNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Table: arcadecard
DROP TABLE IF EXISTS `arcadecard`;
CREATE TABLE `arcadecard` (
`arcadeCardNo` int(10) unsigned NOT NULL auto_increment, `normalCardPrice` decimal(5,2) NOT NULL,
`memberCardPrice` decimal(5,2) NOT NULL, `cardValue` decimal(5,2) NOT NULL,
PRIMARY KEY (`arcadeCardNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Table: redeemitem
DROP TABLE IF EXISTS `redeemitem`;
CREATE TABLE `redeemitem` (
`redeemItemNo` int(10) unsigned NOT NULL auto_increment, `redeemDesc` text NOT NULL,
`pointsCost` int(10) unsigned NOT NULL, PRIMARY KEY (`redeemItemNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Table: facility
DROP TABLE IF EXISTS `facility`;
CREATE TABLE `facility` (
`facilityNo` int(10) unsigned NOT NULL auto_increment, `centerNo` int(10) unsigned default NULL,
`facilityType` enum('lan','bill','arc') NOT NULL, `facilityStatus` enum('0','1') NOT NULL default '0', `normalPrice` decimal(5,2) NOT NULL default '0.00', `MemberPrice` decimal(5,2) default '0.00',
`gameNo` int(10) unsigned default NULL, PRIMARY KEY (`facilityNo`),
KEY `Facility_Index1` USING BTREE (`centerNo`), KEY `Facility_Index2` USING BTREE(`gameNo`),
CONSTRAINT `FK_facility_1` FOREIGN KEY (`centerNo`) REFERENCES `center` (`centerNo`)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT `FK_facility_2` FOREIGN KEY (`gameNo`) REFERENCES `game` (`gameNo`)
ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Table: inventory
DROP TABLE IF EXISTS `inventory`;
CREATE TABLE `inventory` (
`facilityNo` int(10) unsigned NOT NULL, `toDeployAt` int(10) unsigned default NULL, PRIMARY KEY (`facilityNo`),
KEY `Inventory_Index1` USING BTREE (`facilityNo`), CONSTRAINT `FK_inventory_1` FOREIGN KEY (`facilityNo`) REFERENCES `facility` (`facilityNo`)
ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Table: inventorylog
DROP TABLE IF EXISTS `inventorylog`;
CREATE TABLE `inventorylog` (
`facilityNo` int(10) unsigned NOT NULL, `invLogDatetime` datetime NOT NULL, `invDescription` text NOT NULL,
PRIMARY KEY (`facilityNo`,`invLogDatetime`),
KEY `InventoryLog_Index1` USING BTREE (`facilityNo`), CONSTRAINT `FK_inventorylog_1` FOREIGN KEY (`facilityNo`) REFERENCES `facility` (`facilityNo`)
ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Table: centerpromotion
DROP TABLE IF EXISTS `centerpromotion`;
CREATE TABLE `centerpromotion` (
`centerNo` int(10) unsigned NOT NULL, `promotionNo` int(10) unsigned NOT NULL, `startDate` datetime NOT NULL,
`endDate` datetime NOT NULL,
PRIMARY KEY (`centerNo`,`promotionNo`,`startDate`,`endDate`), KEY `CenterPromotion_Index1` USING BTREE (`centerNo`),
KEY `CenterPromotion_Index2` USING BTREE (`promotionNo`), CONSTRAINT `FK_centerpromotion_1` FOREIGN KEY (`centerNo`) REFERENCES `center` (`centerNo`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_centerpromotion_2` FOREIGN KEY (`promotionNo`) REFERENCES `promotion` (`promotionNo`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Table: sales
DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales` (
`salesNo` int(10) unsigned NOT NULL auto_increment, `salesType` enum('lan','bill','arc') NOT NULL, `salesDatetime` datetime NOT NULL,
`amtPayable` decimal(10,2) NOT NULL, `memberNo` int(10) unsigned default NULL, `startTime` time NULL,
`endTime` time NULL,
`facilityNo` int(10) unsigned default NULL, `arcadeCardNo` int(10) unsigned NULL,
`centerNo` int(10) unsigned default NULL, PRIMARY KEY (`salesNo`),
KEY `Sales_Index1` USING BTREE (`memberNo`), KEY `Sales_Index2` USING BTREE (`facilityNo`), KEY `Sales_Index3` USING BTREE (`arcadeCardNo`), KEY `Sales_Index4` USING BTREE (`centerNo`), CONSTRAINT `FK_sales_1` FOREIGN KEY (`memberNo`) REFERENCES `member` (`memberNo`)
ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `FK_sales_2` FOREIGN KEY (`facilityNo`) REFERENCES `facility` (`facilityNo`)
ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `FK_sales_3` FOREIGN KEY (`arcadeCardNo`) REFERENCES `arcadecard` (`arcadeCardNo`)
ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `FK_sales_4` FOREIGN KEY (`centerNo`) REFERENCES `center` (`centerNo`)
ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Table: redemption
DROP TABLE IF EXISTS `redemption`;
CREATE TABLE `redemption` (
`redeemDatetime` datetime NOT NULL,
`redeemItemNo` int(10) unsigned NOT NULL,
`memberNo` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`redeemDatetime`,`redeemItemNo`,`memberNo`), KEY `Redemption_Index1` USING BTREE (`redeemItemNo`), KEY `Redemption_Index2` USING BTREE (`memberNo`), CONSTRAINT `FK_redemption_1` FOREIGN KEY (`memberNo`) REFERENCES `member` (`memberNo`)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT `FK_redemption_2` FOREIGN KEY (`redeemItemNo`) REFERENCES `redeemitem` (`redeemItemNo`)
ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE VIEWS SCHEMA
View: centerpromoview DELIMITER //
DROP VIEW IF EXISTS `centerpromoview` //
#Showing the centers which has or does not has a promotion and the details on the current promotion. Only the current promotion for each center is retrieved.
CREATE VIEW `centerpromoview` AS SELECT
centerNo AS centerNo,
promotionNo AS promotionNo, name AS centerName,
startDate AS promoStartDate, endDate AS promoEndDate, description AS promoDesc, rateType AS promoRateType, rate AS promoRate,
minspent AS promoMinSpent,
FROM center LEFT JOIN centerpromotion USING (centerNo) LEFT JOIN promotion using (promotionNo) WHERE endDate >= CURDATE() OR endDate IS NULL;
//
DELIMITER ;
View: facilitycenterpromoview DELIMITER //
DROP VIEW IF EXISTS `facilitycenterpromoview` //
#Showing the facility which has or does not has a promotion and the details on the current promotion and the center which the facility is in. Only the current promotion for each center is retrieved.
CREATE VIEW `facilitycenterpromoview` AS SELECT
facilityNo AS facilityNo, centerNo AS centerNo,
promotionNo AS promotionNo, facilityType AS facilityType, normalPrice AS normalPrice, memberPrice AS memberPrice, gameTitle AS arcadeGameTitle, genre AS arcadeGenre,
name AS centerName,
startDate AS promoStartDate, endDate AS promoEndDate, description AS promoDesc, rateType AS promoRateType, rate AS promoRate,
minspent AS promoMinSpent, promoType as promoType
FROM facility LEFT JOIN center USING (centerNo) LEFT JOIN centerpromotion USING (centerNo) LEFT JOIN promotion USING (promotionNo) LEFT JOIN game USING (gameNo) WHERE endDate >=
CURDATE() OR endDate IS NULL;
//
DELIMITER ;
CREATE TRIGGER SCHEMA Trigger: before_facility_insert DELIMITER //
DROP TRIGGER IF EXISTS `before_facility_insert` //
CREATE TRIGGER `before_facility_insert` BEFORE INSERT ON facility FOR EACH ROW
BEGIN
SET @center = NEW.centerNo;
SET NEW.centerNo = NULL;
IF NEW.facilityStatus <> 0 THEN SET NEW.facilityStatus = '0';
END IF;
IF NEW.facilityType <> 'arc' THEN SET NEW.gameNo = NULL;
END IF;
IF NEW.facilityType = 'arc' AND NEW.gameNo IS NULL THEN SET NEW.gameNo = 'gameNo_not_defined';
END IF;
END //
DELIMITER;
Trigger: after_facility_insert DELIMITER //
DROP TRIGGER IF EXISTS `after_facility_insert` //
CREATE TRIGGER `after_facility_insert` AFTER INSERT ON facility FOR EACH ROW
BEGIN
SET @newFacility = NEW.facilityNo;
INSERT INTO inventory (facilityNo, toDeployAt) VALUES (@newFacility,@center);
CALL LogInventory(@newFacility,@center,1);
END //
DELIMITER;
Trigger: before_facility_update DELIMITER //
DROP TRIGGER IF EXISTS `before_facility_update` //
CREATE TRIGGER `before_facility_update` BEFORE UPDATE ON facility FOR EACH ROW
BEGIN
SET @facilityStatus = NEW.facilityStatus;
SET @facilityStatusOld = OLD.facilityStatus;
SET @facilityNo = NEW.facilityNo;
SET @centerNo = NEW.centerNo;
SET @centerNoOld = OLD.centerNo;
IF @facilityStatus = 0 THEN
INSERT INTO inventory (facilityNo, toDeployAt) VALUES (@facilityNo, @centerNo);
CALL LogInventory(@FacilityNo,@centerNo,2);
SET NEW.centerNo = NULL;
END IF;
IF @facilityStatus = 1 AND @facilityStatus != @facilityStatusOld THEN SET NEW.centerNo = (SELECT toDeployAt FROM inventory WHERE
facilityNo = @facilityNo);
DELETE FROM inventory WHERE facilityNo = @facilityNo;
CALL LogInventory(@facilityNo,NEW.centerNo,3);
END IF;
IF @centerNoOld <> @centerNo AND @facilityStatus = '1' THEN INSERT INTO inventory (facilityNo, toDeployAt)
VALUES (@facilityNo, @centerNo);
CALL LogInventory(@facilityNo,@centerNo,4);
SET NEW.centerNo = NULL;
SET NEW.facilityStatus = '0';
END IF;
END //
DELIMITER;
Trigger: after_sales_insert DELIMITER //
DROP TRIGGER IF EXISTS `after_sales_insert` //
FOR EACH ROW BEGIN
CALL AddPoints(NEW.memberNo,FLOOR(NEW.amtpayable*10));
END //
DELIMITER;
CREATE STORED PROCEDURE SCHEMA Stored procedure: AddFacility
DELIMITER //
DROP PROCEDURE IF EXISTS `AddFacility` //
CREATE PROCEDURE `AddFacility`
(
facilityType CHAR(4), normalPrice DECIMAL(5,2), memberPrice DECIMAL(5,2), gameNo INTEGER
) BEGIN
IF facilityType NOT IN ('lan','bill','arc') THEN
SELECT 'Facility Type is invalid. Use \'lan\', \'bill\' or \'arc\'' AS Error;
ELSEIF facilityType = 'arc' AND gameNo IS NULL THEN
SELECT 'Please provide game number for the arcade facility.' AS Error;
ELSE
INSERT INTO `facility` (
`facilityType`,
`normalPrice`,
`MemberPrice`,
`gameNo`
)
VALUES (facilityType, normalPrice, memberPrice, gameNo);
END IF;
END//
DELIMITER ;
Stored procedure: InvToInventory DELIMITER //
DROP PROCEDURE IF EXISTS `InvToInventory` //
CREATE PROCEDURE `InvToInventory`
(
facNo INTEGER )
BEGIN
SET @facilityNo = facNo;
If getFacilityCenter(@facilityNo) IS NOT NULL THEN
UPDATE facility SET facilityStatus = '0' WHERE facilityNo =
@facilityNo;
ELSE
SELECT 'Facility is already in inventory.' AS Error;
END IF;
END//
DELIMITER ;
Stored procedure: InvToCenter DELIMITER //
DROP PROCEDURE IF EXISTS `InvToCenter` //
CREATE PROCEDURE `InvToCenter`
(
facNo INTEGER )
proc:BEGIN
SET @facilityNo = facNo;
IF getDeployAt(@facilityNo) IS NULL THEN
SELECT 'Facility has not set which center to deploy to.' AS Error;
LEAVE proc;
END IF;
If getFacilityCenter(@facilityNo) IS NULL THEN
UPDATE facility SET facilityStatus = '1' WHERE facilityNo =
@facilityNo;
ELSE
SELECT 'Facility is already in a center.' AS Error;
END IF;
END proc;
//
DELIMITER ;
Stored procedure: InvChangeCenter DELIMITER //
DROP PROCEDURE IF EXISTS `InvChangeCenter` //
CREATE PROCEDURE `InvChangeCenter`
(
facNo INTEGER, cenNo INTEGER )
BEGIN
SET @facilityNo = facNo;
SET @changeCenter = cenNo;
If getFacilityCenter(@facilityNo) IS NULL THEN
UPDATE inventory SET toDeployAt = @changeCenter WHERE facilityNo =
@facilityNo;
ELSE
UPDATE facility SET centerNo = @changeCenter WHERE facilityNo =
@facilityNo;
END IF;
END//
DELIMITER ;
Stored procedure: LogInventory DELIMITER //
DROP PROCEDURE IF EXISTS `LogInventory` //
CREATE PROCEDURE `LogInventory`
(
facilityNo INTEGER, centerNo INTEGER, logType INTEGER )
BEGIN
DECLARE description TEXT;
IF logType != '' THEN
SET @centerName = (SELECT getCenterName(centerNo));
CASE logType
WHEN 1 THEN SET description = CONCAT('Facility ',facilityNo,' (',
@facilityType,') is added to inventory.');
WHEN 2 THEN SET description = CONCAT('Facility ',facilityNo,' (',
@facilityType,') is sent to inventory for maintainance.');
WHEN 3 THEN SET description = CONCAT('Facility ',facilityNo,' (',
@facilityType,') is sent back to Center ', centerNo, ' (',@centerName,').');
WHEN 4 THEN SET description = CONCAT('Facility ',facilityNo,' (',
@facilityType,') is sent back to inventory to be sent to Center ', centerNo, ' (',@centerName,').');
END CASE;
INSERT INTO inventorylog VALUES (facilityNo, NOW(), description);
END IF;
END //
DELIMITER;
Stored procedure: AddPoints DELIMITER //
DROP PROCEDURE IF EXISTS `AddPoints` //
CREATE PROCEDURE `AddPoints`
(
memNo INTEGER, pts INTEGER )
proc:BEGIN
SET @memberNo = memNo;
SET @points = pts;
UPDATE member SET points = points + @points WHERE memberNo =
@memberNo;
END proc;
//
DELIMITER ;
Stored procedure: RedeemItem DELIMITER //
DROP PROCEDURE IF EXISTS `RedeemItem` //
CREATE PROCEDURE `RedeemItem`
(
memNo INTEGER, reItem INTEGER )
proc:BEGIN
SET @memberNo = memNo;
SET @redeemItemNo = reItem;
#Get the points to redeem the redeem item and check if redeem item is exist.
SET @pointsRedeemed = (SELECT getRedeemItemPoints(@redeemItemNo));
IF @pointsRedeemed IS NULL THEN
SELECT 'Procedure call ends. Redeem Item does not exist. ' AS Error;
LEAVE proc;
END IF;
#Get the points of the member and check if member is exist.
SET @pointsLeft = (SELECT getMemberPoints(@memberNo));
IF @pointsLeft IS NULL THEN
SELECT 'Procedure call ends. Member does not exist. ' AS Error;
LEAVE proc;
END IF;
IF @pointsLeft > @pointsRedeemed THEN
INSERT INTO redemption VALUES (NOW(),@redeemItemNo,@memberNo);
CALL AddPoints(@memberNo,-@pointsRedeemed);
ELSE
SELECT 'Member does not have enough points to redeem the item.' AS Error;
END IF;
END proc;
//
DELIMITER ;
Stored procedure: MemberPointChecking DELIMITER //
DROP PROCEDURE IF EXISTS `MemberPointChecking` //
CREATE PROCEDURE `MemberPointChecking`
( )
proc:BEGIN
UPDATE member SET points = 0, pointsExpiry =
DATE_ADD(pointsExpiry,INTERVAL 1 YEAR) WHERE pointsExpiry <=
CURDATE();
END proc;
//
DELIMITER ;
Stored procedure: AddSales DELIMITER //
DROP PROCEDURE IF EXISTS `AddSales` //
CREATE PROCEDURE `AddSales`
(
facNo INTEGER, startTime TIME, endTime TIME, memNo INTEGER )
proc:BEGIN
SET @price = 0.0;
SET @facilityNo = facNo;
#Check IF member number is valid. IF not valid, leave procedure. Only NULL or valid member number is accepted.
IF memNo IS NOT NULL THEN
IF isMember(memNo) IS NOT NULL THEN SET @memberNo = memNo;
SET @Price = getMemberPrice(@facilityNo);
ELSE
SELECT 'Procedure call ends. Member does not exist. ' AS Error;
LEAVE proc;
END IF;
ELSE
SET @memberNo = NULL;
SET @price = getNormalPrice(@facilityNo);
END IF;
IF @centerNo IS NOT NULL THEN
SET @facilityType = getFacilityType(@facilityNo);
IF @facilityType != 'arc' THEN SET @amtpayable =
calculatePayable(startTime,endTime,@price,@centerNo,@facilityType,@me mberNo);
INSERT INTO sales VALUES
(NULL,@facilityType,NOW(),@amtpayable,@memberNo,startTime,endTime,@fa cilityNo,NULL,@centerNo);
ELSE
SELECT 'Facility type is Arcade. Not Applicable for this procedure call.' AS Error;
END IF;
ELSE
SELECT 'Facility is in Inventory or invalid.' AS Error;
END IF;
END proc;
//
DELIMITER ;
Stored procedure: AddSalesArcadeCard DELIMITER //
DROP PROCEDURE IF EXISTS `AddSalesArcadeCard` //
CREATE PROCEDURE `AddSalesArcadeCard`
(
cenNo INTEGER, arCardNo INTEGER, memNo INTEGER )
proc:BEGIN
SET @price = 0.0;
SET @centerNo = cenNo;
SET @arcadeCardNo = arCardNo;
IF isCenterExist(@centerNo) IS NOT NULL THEN
#Check IF member number is valid. IF not valid, leave procedure.
Only NULL or valid member number is accepted.
IF memNo IS NOT NULL THEN
IF isMember(memNo) IS NOT NULL THEN SET @memberNo = memNo;
SET @Price = getMemberPriceArcadeCard(@arcadeCardNo);
ELSE
SET @price = getNormalPriceArcadeCard(@arcadeCardNo);
SET @promoType = getPromotionType(@centerNo);
SET @minspent = getPromotionMinimumSpent(@centerNo);
IF (@promoType = 'arc' OR @promoType = 'all') AND @price >=
@minspent THEN
SET @rateType = getPromotionRateType(@centerNo);
SET @rate = getPromotionRate(@centerNo);
END IF;
END IF;
INSERT INTO sales VALUES
(NULL,'arc',NOW(),@price,@memberNo,NULL,NULL,NULL,@arcadeCardNo,@cent erNo);
ELSE
SELECT 'Arcade Center does not exist.' AS Error;
END IF;
END proc;
//
DELIMITER ;
Stored procedure: AddPromotionToCenter DELIMITER //
DROP PROCEDURE IF EXISTS `AddPromotionToCenter` //
CREATE PROCEDURE `AddPromotionToCenter`
(
cenNo INTEGER, promoNo INTEGER, startDate DATE, endDate DATE )
proc:BEGIN
SET @centerNo = cenNo;
SET @promotionNo = promoNo;
IF getCenterPromotion(@centerNo) IS NULL THEN INSERT INTO centerpromotion VALUES
(@centerNo,@promotionNo,startDate,endDate);
ELSE
SELECT CONCAT('Center ', @centerNo, ' is currently having promotion.') AS Error;
END IF;
END proc;
//
DELIMITER ;