· 8 years ago · Jul 15, 2018, 05:30 PM
1--
2-- Table structure for table `results`
3--
4
5CREATE TABLE IF NOT EXISTS `results` (
6 `results_id` int(11) NOT NULL AUTO_INCREMENT,
7 `student_id` int(11) NOT NULL,
8 `math` int(11) NOT NULL,
9 `english` int(11) NOT NULL,
10 `swahili` int(11) NOT NULL,
11 `general_studies` int(11) NOT NULL,
12 `civics` int(11) NOT NULL,
13 `science` int(11) NOT NULL,
14 `history` int(11) NOT NULL,
15 `geography` int(11) NOT NULL,
16 PRIMARY KEY (`results_id`),
17 KEY `student_id_fk` (`student_id`)
18) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=100000 ;
19
20-- --------------------------------------------------------
21
22--
23-- Table structure for table `students`
24--
25
26CREATE TABLE IF NOT EXISTS `students` (
27 `student_id` int(11) NOT NULL AUTO_INCREMENT,
28 `student_fname` varchar(30) NOT NULL,
29 `student_lname` varchar(30) NOT NULL,
30 `student_enroll_date` date NOT NULL,
31 `batch_id` int(11) NOT NULL,
32 PRIMARY KEY (`student_id`),
33 KEY `batch_fk_batchid` (`batch_id`)
34) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10000 ;
35
36-- --------------------------------------------------------
37
38--
39-- Table structure for table `student_batch`
40--
41
42CREATE TABLE IF NOT EXISTS `student_batch` (
43 `batch_id` int(11) NOT NULL AUTO_INCREMENT,
44 `batch_start_time` time NOT NULL,
45 `batch_end_time` time NOT NULL,
46 `teacher_id` int(11) NOT NULL,
47 PRIMARY KEY (`batch_id`),
48 KEY `student_batch_fk_teacher` (`teacher_id`)
49) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
50
51-- --------------------------------------------------------
52
53--
54-- Table structure for table `teachers`
55--
56
57CREATE TABLE IF NOT EXISTS `teachers` (
58 `teacher_id` int(11) NOT NULL AUTO_INCREMENT,
59 `teacher_fname` varchar(30) NOT NULL,
60 `teacher_lname` varchar(30) NOT NULL,
61 `teacher_position` enum('lecturer','class_teacher','assistant_lecturer') NOT NULL DEFAULT 'lecturer',
62 PRIMARY KEY (`teacher_id`)
63) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1000 ;
64
65-- --------------------------------------------------------
66
67--
68-- Table structure for table `users`
69--
70
71CREATE TABLE IF NOT EXISTS `users` (
72 `username` varchar(30) NOT NULL,
73 `password` varchar(40) DEFAULT NULL,
74 `usersalt` varchar(8) NOT NULL,
75 `userid` varchar(32) DEFAULT NULL,
76 `userlevel` tinyint(1) unsigned NOT NULL,
77 `email` varchar(50) DEFAULT NULL,
78 `timestamp` int(11) unsigned NOT NULL,
79 `actkey` varchar(35) NOT NULL,
80 `ip` varchar(15) NOT NULL,
81 `regdate` int(11) unsigned NOT NULL,
82 PRIMARY KEY (`username`)
83) ENGINE=MyISAM DEFAULT CHARSET=latin1;
84
85--
86-- Dumping data for table `users`
87--
88
89INSERT INTO `users` (`username`, `password`, `usersalt`, `userid`, `userlevel`, `email`, `timestamp`, `actkey`, `ip`, `regdate`) VALUES
90('tony', '8b82b01ad130411d57fa9f96dc8e3b0b9a2ad24a', 'oC6Wy9pG', 'c32c51688233912964e198dd341d10a5', 9, 'tony.severine@go-finance.co', 1332331505, '1GBxboEbrOXBwJPy', '127.0.0.1', 1332314993),
91('Tony1', '466f585477e5be7f574923c115223494e56e37e6', 'j7vQckCz', '95dd2c9300d1cd1fcf55f41149841ede', 3, 'tony.severine@go-finance.co', 1332330407, 'IhaWLRpDOsMbMJQf', '127.0.0.1', 1332330387);
92
93--
94-- Constraints for dumped tables
95--
96
97--
98-- Constraints for table `attendance`
99--
100ALTER TABLE `attendance`
101 ADD CONSTRAINT `student_id_fk_att` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`);
102
103--
104-- Constraints for table `results`
105--
106ALTER TABLE `results`
107 ADD CONSTRAINT `student_id_fk` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`);
108
109--
110-- Constraints for table `students`
111--
112ALTER TABLE `students`
113 ADD CONSTRAINT `batch_fk_batchid` FOREIGN KEY (`batch_id`) REFERENCES `student_batch` (`batch_id`),
114 ADD CONSTRAINT `student_fk_batch` FOREIGN KEY (`batch_id`) REFERENCES `student_batch` (`batch_id`);
115
116--
117-- Constraints for table `student_batch`
118--
119ALTER TABLE `student_batch`
120 ADD CONSTRAINT `student_batch_fk_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`teacher_id`);