150 likes | 282 Views
St. Edwards COSC 3337. Kasey Tague & Sean Dugan. J - do office managers manage more accounts than other non-office manages Brokers (number and stocks). Show all office manager accounts. SHOW ALL OFFICE MANAGER ACCOUNTS.
E N D
St. Edwards COSC 3337 Kasey Tague & Sean Dugan
J - do office managers manage more accounts than other non-office manages Brokers (number and stocks)
No support for NOT NULL with comparison operators in order to show all brokers who are not office managers. This is why normalization and good database design is important from the start. Work around SELECT * FROM table WHERE NOT (YourColumn NULL); Not standard SQL. Instead, restructure database design.
Report results • Currently office managers manage more accounts and a greater number of stocks than non-office managers.
Real Life Application • Here at Pie in the Sky we value integrity over self monetary gain • Our system involves the management of possibly millions of dollars so the need for auditing is very important
Auditing Trigger On New Trades • The Auditing system fires on the event of a new trade entering the system • Currently it logs the tradeID and the number of Stocks • Easily evolved to catch any legality issues of a high volume trade system
DELIMITER | CREATE TRIGGER audit_trade BEFORE INSERT ON Trades FOR EACH ROW BEGIN INSERT INTO trade_audit (tradeID,noStocks) values (NEW.tradeId,NEW.noStocks); DELETE FROM trade_audit WHERE noStocks<'10000'; END; | DELIMITER ;