· 7 years ago · Jan 19, 2019, 01:22 PM
1use aquaticstar;
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7
8-- -----------------------------------------------------
9-- Table `Students`
10-- -----------------------------------------------------
11DROP TABLE IF EXISTS `Students` ;
12
13CREATE TABLE IF NOT EXISTS `Students` (
14 `id` VARCHAR(10) NOT NULL ,
15 `studentName` VARCHAR(45) NOT NULL ,
16 `gender` CHAR NOT NULL ,
17 `birthDate` DATETIME NOT NULL ,
18 `mNo` VARCHAR(10) NOT NULL ,
19 `contactName` VARCHAR(45) NOT NULL ,
20 `contactEmail` VARCHAR(45) NOT NULL ,
21 `contactPhone` INT(10) NOT NULL ,
22 `startDate` DATETIME NOT NULL ,
23 `remarks` VARCHAR(200) NULL ,
24 PRIMARY KEY (`id`) )
25ENGINE = InnoDB;
26
27
28-- -----------------------------------------------------
29-- Table `Waiting List`
30-- -----------------------------------------------------
31DROP TABLE IF EXISTS `Waiting List` ;
32
33CREATE TABLE IF NOT EXISTS `Waiting List` (
34 `wait_id` VARCHAR(5) NOT NULL ,
35 `name` VARCHAR(45) NULL ,
36 `contactName` VARCHAR(45) NULL ,
37 `contactPhone` INT(10) NULL ,
38 `contactEmail` VARCHAR(45) NULL ,
39 `status` CHAR NULL ,
40 `remarks` VARCHAR(200) NULL ,
41 PRIMARY KEY (`wait_id`) )
42ENGINE = InnoDB;
43
44
45-- -----------------------------------------------------
46-- Table `Schedule`
47-- -----------------------------------------------------
48DROP TABLE IF EXISTS `Schedule` ;
49
50CREATE TABLE IF NOT EXISTS `Schedule` (
51 `lesson_id` VARCHAR(10) NOT NULL ,
52 `day` VARCHAR(3) NOT NULL ,
53 `branch` VARCHAR(30) NOT NULL ,
54 `level` VARCHAR(30) NOT NULL ,
55 `time` TIME NOT NULL ,
56 `ae` VARCHAR(45) NOT NULL ,
57 PRIMARY KEY (`lesson_id`) )
58ENGINE = InnoDB;
59
60
61-- -----------------------------------------------------
62-- Table `Link`
63-- -----------------------------------------------------
64DROP TABLE IF EXISTS `Link` ;
65
66CREATE TABLE IF NOT EXISTS `Link` (
67 `link_id` VARCHAR(10) NOT NULL ,
68 `id` VARCHAR(10) NOT NULL ,
69 `lesson_id` VARCHAR(10) NOT NULL ,
70 PRIMARY KEY (`link_id`) ,
71 INDEX `id_idx` (`id` ASC) ,
72 INDEX `lesson_id_idx` (`lesson_id` ASC) ,
73 CONSTRAINT `id`
74 FOREIGN KEY (`id` )
75 REFERENCES `Students` (`id` )
76 ON DELETE NO ACTION
77 ON UPDATE NO ACTION,
78 CONSTRAINT `lesson_id`
79 FOREIGN KEY (`lesson_id` )
80 REFERENCES `Schedule` (`lesson_id` )
81 ON DELETE NO ACTION
82 ON UPDATE NO ACTION)
83ENGINE = InnoDB;
84
85
86-- -----------------------------------------------------
87-- Table `Attendance`
88-- -----------------------------------------------------
89DROP TABLE IF EXISTS `Attendance` ;
90
91CREATE TABLE IF NOT EXISTS `Attendance` (
92 `date` DATETIME NOT NULL ,
93 `attendance` VARCHAR(5) NOT NULL ,
94 `link_id` VARCHAR(10) NOT NULL ,
95 INDEX `link_id_idx` (`link_id` ASC) ,
96 CONSTRAINT `link_id`
97 FOREIGN KEY (`link_id` )
98 REFERENCES `Link` (`link_id` )
99 ON DELETE NO ACTION
100 ON UPDATE NO ACTION)
101ENGINE = InnoDB;
102
103
104
105SET SQL_MODE=@OLD_SQL_MODE;
106SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
107SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
108
109-- -----------------------------------------------------
110-- Data for table `Students`
111-- -----------------------------------------------------
112START TRANSACTION;
113INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s001', 'Sam Khew', 'm', '12/12/1991', 'nm', 'May Khew', 'may@gmail.com', 0198829387, '12/07/2011', NULL);
114INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s002', 'Joe Biden', 'm', '13/03/2003', 'nm', 'Layla Biden', 'layla@gmail.com', 0199283763, '14/05/2011', NULL);
115INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s003', 'Bob Builder', 'm', '14/02/2002', 'LK920K', 'Mama Builder', 'mama@yahoo.com', 0167728376, '29/02/2012', NULL);
116INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s004', 'Kenny Koh', 'm', '18/02/1999', 'MM992', 'Lisa Koh', 'lk@hotmail.com', 0123160231, '19/01/2012', NULL);
117INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s005', 'Jane Doe', 'f', '29/09/1999', 'nm', 'Jackie Doe', 'jackied@gmail.com', 0127736254, '02/03/2012', NULL);
118INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s006', 'Lola Lai', 'f', '02/05/2004', 'nm', 'Mark Lai', 'mark@gmail.com', 0198827365, '11/09/2011', NULL);
119
120COMMIT;
121
122-- -----------------------------------------------------
123-- Data for table `Schedule`
124-- -----------------------------------------------------
125START TRANSACTION;
126INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat1_s4', 'Sat', 'Sunway', 'basic', '4pm', 'Aini');
127INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat1_s5', 'Sat', 'Sunway', 'basic', '5pm', 'Aini');
128INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat1_s6', 'Sat', 'Sunway', 'basic', '6pm', 'Aini');
129INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat2_s4', 'Sat', 'Sunway', 'advance', '4pm', 'Nina');
130INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat2_s5', 'Sat', 'Sunway', 'advance', '5pm', 'Nina');
131INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat3_s6', 'Sat', 'Sunway', 'pre-comp', '6pm', 'Marcus');
132
133COMMIT;
134
135-- -----------------------------------------------------
136-- Data for table `Link`
137-- -----------------------------------------------------
138START TRANSACTION;
139INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L001', 's001', 'sat1_s4');
140INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L002', 's002', 'sat1_s5');
141INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L003', 's003', 'sat1_s6');
142INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L004', 's004', 'sat2_s4');
143INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L005', 's005', 'sat1_s5');
144
145COMMIT;
146
147-- -----------------------------------------------------
148-- Data for table `Attendance`
149-- -----------------------------------------------------
150START TRANSACTION;
151INSERT INTO `Attendance` (`date`, `attendance`, `link_id`) VALUES ('26/9/2012', '1', NULL);
152
153COMMIT;
154
155Executing SQL script in server
156ERROR: Error 1005: Can't create table 'aquaticstar.link' (errno: 121)
157
158mysql> SHOW INNODB STATUSG
159
160SELECT
161 constraint_name,
162 table_name
163FROM
164 information_schema.table_constraints
165WHERE
166 constraint_type = 'FOREIGN KEY'
167AND table_schema = DATABASE()
168ORDER BY
169 constraint_name;