createdb.sql

远程学生作业提交系统,所用技术JSP,Servelet,Ajax,Web Services,MySql

-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.0.26-community-nt


/*!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 test
--

CREATE DATABASE IF NOT EXISTS test;
USE test;

--
-- Definition of table `preventuser`
--

DROP TABLE IF EXISTS `preventuser`;
CREATE TABLE `preventuser` (
  `studentId` varchar(45) NOT NULL,
  `projectId` int(10) unsigned NOT NULL,
  `preventLate` int(10) unsigned NOT NULL default '0',
  `preventImpend` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`studentId`,`projectId`),
  KEY `FK_preventuser_2` (`projectId`),
  CONSTRAINT `FK_preventuser_1` FOREIGN KEY (`studentId`) REFERENCES `user` (`loginName`),
  CONSTRAINT `FK_preventuser_2` FOREIGN KEY (`projectId`) REFERENCES `project` (`projectId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `preventuser`
--

/*!40000 ALTER TABLE `preventuser` DISABLE KEYS */;
INSERT INTO `preventuser` (`studentId`,`projectId`,`preventLate`,`preventImpend`) VALUES 
 ('ben',1,1,1),
 ('ben',2,1,1),
 ('lulu',3,0,0),
 ('pierre',1,0,0),
 ('pierre',2,1,1);
/*!40000 ALTER TABLE `preventuser` ENABLE KEYS */;


--
-- Definition of table `project`
--

DROP TABLE IF EXISTS `project`;
CREATE TABLE `project` (
  `projectId` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(45) NOT NULL,
  `code` int(10) unsigned NOT NULL,
  `openDate` datetime default NULL,
  `closingDate` datetime default NULL,
  `description` varchar(200) NOT NULL,
  `url` varchar(45) NOT NULL,
  `semester` int(10) unsigned NOT NULL,
  `year` varchar(45) NOT NULL,
  `status` varchar(45) NOT NULL default 'C',
  PRIMARY KEY  (`projectId`),
  KEY `FK_project_1` (`code`),
  CONSTRAINT `FK_project_1` FOREIGN KEY (`code`) REFERENCES `subject` (`subjectId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The project representation';

--
-- Dumping data for table `project`
--

/*!40000 ALTER TABLE `project` DISABLE KEYS */;
INSERT INTO `project` (`projectId`,`name`,`code`,`openDate`,`closingDate`,`description`,`url`,`semester`,`year`,`status`) VALUES 
 (1,'Web services',1,'2006-10-28 00:00:00',NULL,'Implementing submission system with webservice','www.webservices.com',1,'2006','O'),
 (2,'Roundup',1,'2006-10-28 11:19:06',NULL,'Improve roundup installation','www.roundup.com',1,'2008','O'),
 (3,'Breakout game',2,'2003-12-30 01:02:05','2010-12-30 01:02:05','Do a J2ME breakout game','www.breakout.com',2,'2009','O');
/*!40000 ALTER TABLE `project` ENABLE KEYS */;


--
-- Definition of table `subject`
--

DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
  `name` varchar(45) NOT NULL,
  `subjectId` int(10) unsigned NOT NULL auto_increment,
  `lecturerId` varchar(45) NOT NULL,
  PRIMARY KEY  (`subjectId`),
  KEY `FK_subject_1` (`lecturerId`),
  CONSTRAINT `FK_subject_1` FOREIGN KEY (`lecturerId`) REFERENCES `user` (`loginName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The subject representation';

--
-- Dumping data for table `subject`
--

/*!40000 ALTER TABLE `subject` DISABLE KEYS */;
INSERT INTO `subject` (`name`,`subjectId`,`lecturerId`) VALUES 
 ('Engineering for internet application',1,'mike'),
 ('Mobile programming',2,'lars');
/*!40000 ALTER TABLE `subject` ENABLE KEYS */;


--
-- Definition of table `submission`
--

DROP TABLE IF EXISTS `submission`;
CREATE TABLE `submission` (
  `submissionId` int(10) unsigned NOT NULL auto_increment,
  `subjectId` int(10) unsigned NOT NULL,
  `projectId` int(10) unsigned NOT NULL,
  `studentId` varchar(45) NOT NULL,
  `submissionDate` datetime NOT NULL,
  `ipAddress` varchar(45) NOT NULL,
  PRIMARY KEY  (`submissionId`),
  KEY `FK_submission_1` (`subjectId`),
  KEY `FK_submission_2` (`projectId`),
  KEY `FK_submission_3` (`studentId`),
  CONSTRAINT `FK_submission_1` FOREIGN KEY (`subjectId`) REFERENCES `subject` (`subjectId`),
  CONSTRAINT `FK_submission_2` FOREIGN KEY (`projectId`) REFERENCES `project` (`projectId`),
  CONSTRAINT `FK_submission_3` FOREIGN KEY (`studentId`) REFERENCES `user` (`loginName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `submission`
--

/*!40000 ALTER TABLE `submission` DISABLE KEYS */;
INSERT INTO `submission` (`submissionId`,`subjectId`,`projectId`,`studentId`,`submissionDate`,`ipAddress`) VALUES 
 (1,1,1,'pierre','2003-12-31 01:02:03','127.0.0.1');
/*!40000 ALTER TABLE `submission` ENABLE KEYS */;


--
-- Definition of table `submissionfiles`
--

DROP TABLE IF EXISTS `submissionfiles`;
CREATE TABLE `submissionfiles` (
  `submissionId` int(10) unsigned NOT NULL auto_increment,
  `filename` varchar(45) NOT NULL,
  `fileData` longblob NOT NULL,
  `size` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`submissionId`,`filename`),
  CONSTRAINT `FK_submissionfiles_1` FOREIGN KEY (`submissionId`) REFERENCES `submission` (`submissionId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='All the file included in a submission';

--
-- Dumping data for table `submissionfiles`
--

/*!40000 ALTER TABLE `submissionfiles` DISABLE KEYS */;
INSERT INTO `submissionfiles` (`submissionId`,`filename`,`fileData`,`size`) VALUES 
 (1,'hihi.txt',0x68756D68756D,5),
 (1,'test.txt',0x617A6572747975696F70,17);
/*!40000 ALTER TABLE `submissionfiles` ENABLE KEYS */;


--
-- Definition of table `user`
--

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `loginName` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `role` varchar(45) NOT NULL,
  `name` varchar(45) NOT NULL,
  `email` varchar(45) NOT NULL,
  PRIMARY KEY  (`loginName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The user representation';

--
-- Dumping data for table `user`
--

/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` (`loginName`,`password`,`role`,`name`,`email`) VALUES 
 ('admin','test','admin','admin','[e-mail]'),
 ('ben','test','student','benjamin','[e-mail]'),
 ('lars','test','staff','lars','[e-mail]'),
 ('lulu','test','student','lulu','[e-mail]'),
 ('mike','test','staff','mike','[e-mail]'),
 ('pierre','test','student','pierre','[e-mail]');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;


--
-- Definition of table `usersubjects`
--

DROP TABLE IF EXISTS `usersubjects`;
CREATE TABLE `usersubjects` (
  `userId` varchar(45) NOT NULL,
  `subjectId` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`userId`,`subjectId`),
  KEY `FK_userSubjects_2` (`subjectId`),
  CONSTRAINT `FK_userSubjects_1` FOREIGN KEY (`userId`) REFERENCES `user` (`loginName`),
  CONSTRAINT `FK_userSubjects_2` FOREIGN KEY (`subjectId`) REFERENCES `subject` (`subjectId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='All the subjects chose by a student';

--
-- Dumping data for table `usersubjects`
--

/*!40000 ALTER TABLE `usersubjects` DISABLE KEYS */;
INSERT INTO `usersubjects` (`userId`,`subjectId`) VALUES 
 ('ben',1),
 ('pierre',1),
 ('ben',2),
 ('lulu',2);
/*!40000 ALTER TABLE `usersubjects` ENABLE KEYS */;




/*!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 */;