考核管理系统.zip

SET default_storage_engine=INNODB; ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE `Assessments` ( `Id` VARCHAR(250) NOT NULL, `DepartmentId` TEXT NULL, `Name` TEXT NULL, `Annual` BIT NOT NULL, `CreatedTime` DATETIME NOT NULL, PRIMARY KEY (`Id`) ); CREATE TABLE `Departments` ( `Id` VARCHAR(250) NOT NULL, `Name` TEXT NULL, `ParentId` VARCHAR(250) NULL, PRIMARY KEY (`Id`), CONSTRAINT `fk1` FOREIGN KEY (`ParentId`) REFERENCES `Departments` (`Id`) ON DELETE RESTRICT ); CREATE TABLE `Subjects` ( `Id` VARCHAR(250) NOT NULL, `Name` TEXT NULL, `SubjectType` INT NOT NULL, `SexLimitation` INT NOT NULL, `IsQualifiedConversion` BIT NOT NULL, `Unit` TEXT NULL, PRIMARY KEY (`Id`) ); CREATE TABLE `Person` ( `Id` VARCHAR(250) NOT NULL, `RealName` TEXT NULL, `Birthday` DATETIME NOT NULL, `Sex` INT NOT NULL, `DepartmentId` VARCHAR(250) NULL, PRIMARY KEY (`Id`), CONSTRAINT `fk2` FOREIGN KEY (`DepartmentId`) REFERENCES `Departments` (`Id`) ON DELETE RESTRICT ); CREATE TABLE `AssessmentSubject` ( `AssessmentId` VARCHAR(250) NOT NULL, `SubjectId` VARCHAR(250) NOT NULL, PRIMARY KEY (`SubjectId`, `AssessmentId`), CONSTRAINT `fk3` FOREIGN KEY (`AssessmentId`) REFERENCES `Assessments` (`Id`) ON DELETE CASCADE, CONSTRAINT `fk4` FOREIGN KEY (`SubjectId`) REFERENCES `Subjects` (`Id`) ON DELETE CASCADE ); CREATE TABLE `SubjectConversion` ( `Sex` INT NOT NULL, `SubjectId` VARCHAR(250) NOT NULL, PRIMARY KEY (`SubjectId`, `Sex`), CONSTRAINT `fk5` FOREIGN KEY (`SubjectId`) REFERENCES `Subjects` (`Id`) ON DELETE CASCADE ); CREATE TABLE `PersonGrades` ( `Id` VARCHAR(250) NOT NULL, `AssessmentId` VARCHAR(250) NULL, `PersonId` VARCHAR(250) NULL, `IsAbsent` BIT NOT NULL, `IsMakeup` BIT NOT NULL, PRIMARY KEY (`Id`), CONSTRAINT `fk6` FOREIGN KEY (`AssessmentId`) REFERENCES `Assessments` (`Id`) ON DELETE RESTRICT, CONSTRAINT `fk7` FOREIGN KEY (`PersonId`) REFERENCES `Person` (`Id`) ON DELETE RESTRICT ); CREATE TABLE `Users` ( `Id` VARCHAR(250) NOT NULL, `LoginName` TEXT NULL, `Password` TEXT NULL, `PersonId` VARCHAR(250) NULL, `IsAdmin` BIT NOT NULL, PRIMARY KEY (`Id`), CONSTRAINT `fk8` FOREIGN KEY (`PersonId`) REFERENCES `Person` (`Id`) ON DELETE RESTRICT ); CREATE TABLE `ConversionCell` ( `Score` DOUBLE NOT NULL, `SubjectId` VARCHAR(250) NOT NULL, `Sex` INT NOT NULL, `FloorAge` INT NOT NULL, `AgeRange_Minimum` INT NOT NULL, `AgeRange_Maximum` INT NOT NULL, `AgeRange_CellingAge` INT NOT NULL, `AgeRange_FloorAge` INT NOT NULL, `Grade_GradeValue` DOUBLE NOT NULL, PRIMARY KEY (`SubjectId`, `Sex`, `FloorAge`, `Score`), CONSTRAINT `fk9` FOREIGN KEY (`SubjectId`, `Sex`) REFERENCES `SubjectConversion` (`SubjectId`, `Sex`) ON DELETE CASCADE ); CREATE TABLE `SubjectGrade` ( `SubjectId` VARCHAR(250) NOT NULL, `PersonAssessmentGradeId` VARCHAR(250) NOT NULL, `Grade` DOUBLE NULL, `Score` DOUBLE NOT NULL, PRIMARY KEY (`PersonAssessmentGradeId`, `SubjectId`), CONSTRAINT `fk10` FOREIGN KEY (`PersonAssessmentGradeId`) REFERENCES `PersonGrades` (`Id`) ON DELETE CASCADE, CONSTRAINT `fk11` FOREIGN KEY (`SubjectId`) REFERENCES `Subjects` (`Id`) ON DELETE CASCADE ); CREATE INDEX `IX_AssessmentSubject_AssessmentId` ON `AssessmentSubject` (`AssessmentId`); CREATE INDEX `IX_Departments_ParentId` ON `Departments` (`ParentId`); CREATE INDEX `IX_Person_DepartmentId` ON `Person` (`DepartmentId`); CREATE INDEX `IX_PersonGrades_AssessmentId` ON `PersonGrades` (`AssessmentId`); CREATE INDEX `IX_PersonGrades_PersonId` ON `PersonGrades` (`PersonId`); CREATE INDEX `IX_SubjectGrade_SubjectId` ON `SubjectGrade` (`SubjectId`); CREATE INDEX `IX_Users_PersonId` ON `Users` (`PersonId`);