· 9 years ago · Dec 07, 2016, 06:02 AM
1DROP DATABASE if exists NotesDB;
2CREATE DATABASE NotesDB;
3
4USE NotesDB;
5
6CREATE TABLE users (
7 username VARCHAR(30) NOT NULL,
8 password VARCHAR(25) NOT NULL,
9 email VARCHAR(50) NOT NULL,
10 firstname VARCHAR(20),
11 lastname VARCHAR(20),
12 phonenumber VARCHAR(15),
13 gender CHAR(1),
14 passworduuid VARCHAR(50),
15
16 PRIMARY KEY (username),
17 UNIQUE (email)
18) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;;
19
20DELIMITER $$
21
22CREATE TRIGGER before_insert_check_gender
23 BEFORE INSERT ON users FOR EACH ROW
24 BEGIN
25 IF New.gender NOT IN ('M', 'F', 'U', 'N')
26 THEN
27 signal sqlstate '45000' set message_text = 'Cannot add user: gender must be M, F, U, or N.';
28# CALL 'Cannot add user: gender must be M, F, U, or N.';
29 END IF;
30 END;
31$$
32
33DELIMITER ;
34
35INSERT INTO `users` VALUES
36 ('admin','password','cprg352+admin@gmail.com','Admin','Admin','403-555-1212','U',null),
37 ('abe','password','cprg352+abe@gmail.com','Abe','Aberly','403-555-3434','M',null),
38 ('barb','password','cprg352+barb@gmail.com','Barb','Barker','403-555-5656','F',null);
39
40CREATE TABLE `notes` (
41 `noteid` int(11) NOT NULL AUTO_INCREMENT,
42 `datecreated` datetime NOT NULL,
43 `title` varchar(100) NOT NULL,
44 `contents` varchar(10000) NOT NULL,
45 `owner` varchar(25) NOT NULL,
46 PRIMARY KEY (`noteid`),
47 KEY `note_owner` (`owner`),
48 CONSTRAINT `note_owner` FOREIGN KEY (`owner`) REFERENCES `users` (`username`) ON DELETE CASCADE ON UPDATE CASCADE
49) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;
50
51INSERT INTO `notes` VALUES (1,'2015-10-28 22:52:47','title','test','abe'), (2,'2015-10-28 22:52:48','This is a test','test2','abe');
52
53CREATE TABLE `notecollaborators` (
54 `noteid` int(11) NOT NULL,
55 `username` varchar(25) NOT NULL,
56 PRIMARY KEY (`noteid`,`username`),
57 KEY `fk_NoteCollaborator_user1_idx` (`username`),
58 CONSTRAINT `fk_NoteCollaborator_note1` FOREIGN KEY (`noteid`) REFERENCES `notes` (`noteid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
59 CONSTRAINT `fk_NoteCollaborator_user1` FOREIGN KEY (`username`) REFERENCES `users` (`username`) ON DELETE NO ACTION ON UPDATE NO ACTION
60) ENGINE=InnoDB DEFAULT CHARSET=utf8;
61
62INSERT INTO `notecollaborators` VALUES (1, 'barb');
63
64CREATE TABLE `roles` (
65 `roleid` int(11) NOT NULL,
66 `rolename` varchar(25) NOT NULL,
67 PRIMARY KEY (`roleid`)
68) ENGINE=InnoDB DEFAULT CHARSET=utf8;
69
70INSERT INTO `roles` VALUES (1,'admin');
71
72CREATE TABLE `userroles` (
73 `username` varchar(25) NOT NULL,
74 `roleid` int(11) NOT NULL,
75 PRIMARY KEY (`username`,`roleid`),
76 KEY `userrole_role_idx` (`roleid`),
77 CONSTRAINT `userrole_role` FOREIGN KEY (`roleid`) REFERENCES `roles` (`roleid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
78 CONSTRAINT `userrole_user` FOREIGN KEY (`username`) REFERENCES `users` (`username`) ON DELETE NO ACTION ON UPDATE NO ACTION
79) ENGINE=InnoDB DEFAULT CHARSET=utf8;
80
81INSERT INTO `userroles` VALUES ('admin',1);