-- MySQL Administrator dump 1.4 -- -- ------------------------------------------------------ -- Server version 5.0.26 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /* -- -- Create schema apex -- CREATE DATABASE IF NOT EXISTS apex; */ USE ctlt_labstats; CREATE TABLE `ctlt_labstats`.`coc` ( `coc_id` int(11) NOT NULL default '0' COMMENT 'Computer Identification Tag', `coc_man` varchar(40) NOT NULL COMMENT 'Computer Manufacturer', `coc_model` varchar(40) NOT NULL COMMENT 'Computer Model', `coc_imgdate` date NOT NULL COMMENT 'Date the Computer was imaged', `fkimg` int(11) NOT NULL, `coc_aval` tinyint(1) NOT NULL COMMENT 'Computer Available for checkout not Available', `coc_invstatus` tinyint(1) NOT NULL COMMENT 'Computer In or out of inventory', PRIMARY KEY (`coc_id`), KEY `fkimg-img_id` (`fkimg`), CONSTRAINT `fkimg-img_id` FOREIGN KEY (`fkimg`) REFERENCES `img` (`img_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Computer Information Table'; INSERT INTO `ctlt_labstats`.`coc` VALUES (89230,'Gateway','E-3400','2006-08-21',1,1,1), (91562,'GATEWAY','E-3400','2006-12-15',1,0,1), (91564,'GATEWAY','E-3400','2006-08-21',1,0,0), (91565,'GATEWAY','E-3400','2006-12-15',1,0,0), (91568,'GATEWAY','E-3400','2006-12-15',1,1,0), (91569,'GATEWAY','E-3400','0000-00-00',1,0,0), (92624,'GATEWAY','E-3400','0000-00-00',1,0,0), (92625,'GATEWAY','E-3400','2006-12-15',1,1,1), (92626,'GATEWAY','E-3400','0000-00-00',1,0,0), (92627,'GATEWAY','E-3400','0000-00-00',1,0,0), (92628,'GATEWAY','E-3400','2006-12-15',1,0,0), (92629,'GATEWAY','E-3400','0000-00-00',1,0,1), (92630,'GATEWAY','E-3400','0000-00-00',1,0,0), (92631,'GATEWAY','E-3400','0000-00-00',1,0,0), (92632,'GATEWAY','E-3400','2006-12-15',1,0,0), (92633,'GATEWAY','E-3400','2006-12-15',1,0,0), (92635,'GATEWAY','E-3400','0000-00-00',1,0,0), (92636,'GATEWAY','E-3400','0000-00-00',1,0,0), (92637,'GATEWAY','E-3400','2006-12-15',1,0,0), (92638,'GATEWAY','E-3400','0000-00-00',1,0,0), (92639,'GATEWAY','E-3400','0000-00-00',1,0,0), (92641,'GATEWAY','E-3400','2006-12-15',1,0,0); INSERT INTO `ctlt_labstats`.`coc` VALUES (92642,'GATEWAY','E-3400','2006-12-15',1,0,0), (92643,'GATEWAY','E-3400','0000-00-00',1,0,0); CREATE TABLE `ctlt_labstats`.`cus` ( `cus_id` int(11) NOT NULL auto_increment COMMENT 'Customer ID Number', `cus_lname` varchar(40) NOT NULL COMMENT 'Customer Last Name', `cus_fname` varchar(40) NOT NULL COMMENT 'Customer First Name\n', `cus_address` varchar(100) NOT NULL COMMENT 'Customer Address', `cus_email` varchar(60) NOT NULL COMMENT 'Customer Email', `cus_hphone` varchar(14) default NULL COMMENT 'Customer Home Phone', `cus_mphone` varchar(14) default NULL COMMENT 'Customer Mobile Phone', PRIMARY KEY (`cus_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Customer Information'; INSERT INTO `ctlt_labstats`.`cus` VALUES (1,'O\'Keiffe','Colin','1103 College Courts','bewize@hotmail.com','1-270-809-2550',NULL), (2,'Lopez','Carlos','812 College Courts','carlos.lopez@murraystate.edu','','1-270-227-3631'), (3,'Mahit Ani','Dhami','105 College Courts','dhami.mahitani@murraystate.edu','1-270-809-6105',NULL), (4,'Fadiran','Tolu','604 College Courts','tolu.teddy@yahoo.com','1-270-809-4254',NULL), (5,'Agrawal','Ritesh','206 College Courts','ristesh.argawal@murraystate.edu','1-270-809-6256',NULL), (6,'Mulenga','Suzyo','913 College Courts','suzyo.mulengo@murraystate.edu','1-270-809-2356',NULL), (7,'Thi Hoang Le','Van','603 College Courts','','1-270-809-4572',NULL), (8,'Vadten','Bhargava','516 College Courts','bhargava.vettern@murraystate.edu','1-270-809-4006',NULL), (9,'Agrawal','Nidhi','206 College Courts','nidhi_jewels@yahoo.com','1-270-809-6256',NULL), (10,'Tumula','Ajay','203 College Courts','ajay12.t@gmail.com','1-270-809-6253',NULL), (11,'Liu','PengPeng','511 College Courts','lucy_pp@yahoo.com','1-270-809-4001',NULL), (12,'Mesh','Elias','201 College Courts','elias.mesh@murraystate.edu',NULL,NULL); INSERT INTO `ctlt_labstats`.`cus` VALUES (13,'Xin','Ge','304 College Courts','thomas.zhou@yahoo.com','1-270-809-4444',NULL), (14,'Mathew','Alexander','117 Clark College','alexander.mathew@murraystate.edu','1-270-809-3891',NULL), (15,'Utharala','Ramesh','912 College Courts','ramesh.utharala@murraystate.edu','','1-469-426-1933'), (16,'Sadodam','Vijay','','',NULL,NULL), (17,'Kumar','Chakkajagadish','404 College Courts','','1-270-809-4404',NULL), (18,'Zhan','Zhiying','','zhiyang.zhan@murraystate.edu','1-270-809-2458',NULL), (19,'Saeko','Yamanishi','1102 College Courts','saeko.yamanishi@murraystate.edu','1-270-809-2549',''), (20,'OBrian','Cory','','cory.obrian@murraystate.edu','1-270-809-6106',''); CREATE TABLE `ctlt_labstats`.`his` ( `his_id` int(11) NOT NULL auto_increment COMMENT 'Transaction ID', `his_tdate` date NOT NULL COMMENT 'Date of Transaction', `his_rdate` date default NULL COMMENT 'Date the Computer was returned', `fkcus` int(11) NOT NULL COMMENT 'Foreign Key pointing to Customer ID', `fkcoc` int(11) NOT NULL COMMENT 'Foreign Key Pointing to Computer ID', `fksem` int(11) NOT NULL COMMENT 'Foreign Key Pointing to Semester ID', PRIMARY KEY (`his_id`), KEY `fkcus-cus_id` (`fkcus`), KEY `fkcoc-coc_id` (`fkcoc`), KEY `fksem-sem_id` (`fksem`), CONSTRAINT `fkcus-cus_id` FOREIGN KEY (`fkcus`) REFERENCES `cus` (`cus_id`), CONSTRAINT `fkcoc-coc_id` FOREIGN KEY (`fkcoc`) REFERENCES `coc` (`coc_id`), CONSTRAINT `fksem-sem_id` FOREIGN KEY (`fksem`) REFERENCES `sem` (`sem_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Historical/Tranisitional Information'; INSERT INTO `ctlt_labstats`.`his` VALUES (1,'2006-12-19','0000-00-00',10,92637,2), (2,'2007-01-17','0000-00-00',17,92642,2), (3,'2007-01-17','0000-00-00',16,92641,2), (4,'2007-01-16','0000-00-00',14,92632,2), (5,'2007-03-05','0000-00-00',20,92629,2), (6,'2007-01-17','0000-00-00',15,92628,2), (7,'2007-02-27','0000-00-00',19,91568,2), (8,'2005-10-13','2006-05-12',6,92625,4), (9,'2005-10-11','2006-05-12',1,92633,4), (10,'2006-02-06','2006-05-12',8,92636,3), (11,'2006-01-17','2006-05-12',4,92641,3), (12,'2005-10-11','2006-05-12',2,92642,4); CREATE TABLE `ctlt_labstats`.`img` ( `img_id` int(11) NOT NULL auto_increment COMMENT 'Image Identification', `img_name` varchar(40) NOT NULL COMMENT 'Name of the Image', `img_cdate` date NOT NULL COMMENT 'Date the Image was created', `img_dsc` mediumtext NOT NULL COMMENT 'Descirption of Image', PRIMARY KEY (`img_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Image Table'; INSERT INTO `ctlt_labstats`.`img` VALUES (1,'12082006-apexco.gho','2006-12-08','Windows 2000 SP4\nFirefox 2.0\nGAIM 2.0\n'); CREATE TABLE `ctlt_labstats`.`sem` ( `sem_id` int(11) NOT NULL auto_increment COMMENT 'Semester ID', `sem_name` varchar(40) NOT NULL COMMENT 'Semester Name (Ref: Spring 2007)', `sem_sdate` date NOT NULL COMMENT 'Date the Semester starts', `sem_edate` date NOT NULL COMMENT 'Date the Semester Ends', `sem_ddate` date NOT NULL COMMENT 'Date the Computer is due back before the end of the Semester', PRIMARY KEY (`sem_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Semester Information Table'; INSERT INTO `ctlt_labstats`.`sem` VALUES (1,'Fall06','2006-08-01','2006-12-15','2006-12-14'), (2,'Spring07','2007-01-16','2007-05-12','2007-05-11'), (3,'Spring06','2006-01-01','2006-05-21','2006-05-12'), (4,'Fall05','2005-08-01','2005-12-17','2005-12-16'); /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;