· 6 years ago · Apr 05, 2019, 02:26 PM
1use project;
2-- MySQL Workbench Forward Engineering
3
4SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
5SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
6SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
7
8-- -----------------------------------------------------
9-- Schema project
10-- -----------------------------------------------------
11DROP SCHEMA IF EXISTS `project` ;
12
13-- -----------------------------------------------------
14-- Schema project
15-- -----------------------------------------------------
16
17Create DataBase if not exists project;
18USE `project` ;
19
20-- -----------------------------------------------------
21-- Table `project`.`Person`
22-- -----------------------------------------------------
23
24CREATE TABLE person (
25 personID mediumint,
26 FName varchar(255) default NULL,
27 LName varchar(255) default NULL,
28 Gender varchar(255) default NULL,
29 StreetAddress varchar(255) default NULL,
30 Phone varchar(100) default NULL,
31 Email varchar(255) default NULL,
32 PRIMARY KEY (personID)
33);
34INSERT INTO person (personID,FName,LName,Gender,StreetAddress,Phone,Email) VALUES (1,"Karleigh","Bright","M","5136 Pretium Avenue","(705) 935-8781","nec.urna.suscipit@scelerisque.edu");
35
36-- Table `project`.`Student`
37-- -----------------------------------------------------
38select * from person;
39
40CREATE TABLE Student (
41 StudentID mediumint,
42 Major varchar(255) default NULL,
43 GPA mediumint default NULL,
44 Grad_year varchar(255),
45 honors_YorN varchar(255) default NULL,
46 personID mediumint,
47 PRIMARY KEY (StudentID),
48 constraint stuID FOREIGN KEY (personID) REFERENCES person(personID)
49 );
50
51INSERT INTO student (StudentID,Major,GPA,Grad_year,honors_YorN) VALUES (1,"Finances",3,"25/10/2019","Y");
52
53
54-- -----------------------------------------------------
55-- Table `project`.`Mentor`
56-- -----------------------------------------------------
57
58CREATE TABLE Mentor (
59 mentorID mediumint,
60 highest_degree VARCHAR(100) default NULL,
61 business VARCHAR(200) default NULL,
62 positions VARCHAR(200) default NULL,
63 personID mediumint,
64 PRIMARY KEY (mentorID),
65 constraint mentID FOREIGN KEY (personID) REFERENCES person(personID)
66
67 )
68ENGINE = InnoDB;
69INSERT INTO Mentor (mentorID,highest_degree,business,positions) VALUES (76,"phD","Advising","Associated");
70
71-- -----------------------------------------------------
72-- Table `project`.`Assign`
73-- -----------------------------------------------------
74
75CREATE TABLE Assign (
76 assignID mediumint,
77 year varchar(200),
78 S_P_M int not NULL,
79 rating INT NULL,
80 StudentID mediumint,
81 mentorID mediumint,
82 PRIMARY KEY (assignID),
83 constraint studID_assign FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
84 constraint mentID_assign FOREIGN KEY (mentorID) REFERENCES Mentor(mentorID)
85 )
86ENGINE = InnoDB;
87INSERT INTO Assign (assignID,year,S_P_M,rating) VALUES (1,"Sep 23, 2019",7,8);
88
89-- -----------------------------------------------------
90-- Table `project`.`Events`
91-- -----------------------------------------------------
92
93CREATE TABLE Event (
94 eventID mediumint,
95 Title varchar(255) default NULL,
96 eventDescription TEXT default NULL,
97 Date_Time varchar(255),
98 lenght_min mediumint default NULL,
99 locationDescription varchar(255) default NULL,
100 ticketCost varchar(100) default NULL,
101 PRIMARY KEY (eventID))
102ENGINE = InnoDB;
103
104INSERT INTO Event (eventID,Title,eventDescription,Date_Time,lenght_min,locationDescription,ticketCost) VALUES (1,"Festival","adipiscing, enim mi tempor lorem, eget mollis lectus pede et","May 22, 2018",60,"","$55.13");
105
106-- -----------------------------------------------------
107-- Table `project`.`Attend`
108-- -----------------------------------------------------
109
110CREATE TABLE Attend (
111 assignID mediumint,
112 attendID mediumint,
113 eventID mediumint,
114 PRIMARY KEY (assignID, attendID),
115 constraint assignID_attend FOREIGN KEY (assignID) REFERENCES Assign(assignID),
116 constraint eventID_attend FOREIGN KEY (eventID) REFERENCES Event(eventID)
117)
118
119ENGINE = InnoDB;
120
121
122
123
124SET SQL_MODE=@OLD_SQL_MODE;
125SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
126SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
127
128
129CREATE INDEX name_index
130 ON project.person(Fname);
131SHOW index from project.person;
132
133-- CREATE TRIGGER FOR UPDATE AND DELETE EXAMPLE
134CREATE TRIGGER ahead_insert
135After INSERT ON person
136FOR EACH ROW
137insert into person (personID,FName,LName,Gender,StreetAddress,Phone,Email)
138VALUES (101,"Bobby","John","M","701 Faith Avenue","(704) 982-5485","aac.Lorth.BJohn@unca.edu");
139
140-- UPDATE
141UPDATE `project`.`event`
142SET `Title` = 'Mock Interview'
143WHERE eventID =100;
144
145SELECT * FROM event;
146
147-- DELETE
148DELETE FROM person
149WHERE personID = 101;
150
151
152-- REPORT 1
153SELECT StudentID, GPA, major
154FROM student
155WHERE GPA = 3
156ORDER BY StudentID DESC;
157
158-- REPORT 2
159SELECT person.personID,Fname, Lname, Assign.rating, Event.title
160FROM person
161JOIN Assign ON person.personID = Assign.assignID
162JOIN Event On event.eventID = person.personID;
163
164-- REPORT 3
165SELECT mentorID, positions, student.StudentID
166FROM mentor, student;
167
168-- Creates studentArchive table:
169Use project;
170DROP TABLE IF EXISTS `studentArchive`;
171/*!40101 SET @saved_cs_client = @@character_set_client */;
172/*!40101 SET character_set_client = utf8 */;
173 CREATE TABLE `studentArchive` ( `ID`
174 int(11) NOT NULL AUTO_INCREMENT,
175 `Major` varchar(255) DEFAULT NULL,
176 `action` varchar(25) DEFAULT NULL,
177 `changedon` datetime DEFAULT NULL,
178 PRIMARY KEY (`ID`)
179 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
180 /*!40101 SET character_set_client = @saved_cs_client */;
181
182 -- Trigger Execution:
183Use project;
184DROP TRIGGER IF EXISTS triggerAfterUpdate;
185DELIMITER $$
186CREATE TRIGGER triggerAfterUpdate
187BEFORE UPDATE ON student
188FOR EACH ROW BEGIN
189INSERT INTO studentArchive
190 SET action = 'update',
191 Major = OLD.Major,
192 changedon = NOW();
193 END$$
194 DELIMITER ;
195
196use project;
197UPDATE student
198SET Major = 'Finances'
199WHERE StudentID = 33;
200
201SELECT * FROM project.student;
202
203-- Query 1
204SELECT StudentID
205FROM Student
206WHERE Major = 'Accounting';
207
208-- Query 2
209SELECT Title
210FROM Event
211WHERE locationDescription = 'OffCampus';
212
213 -- Query 3
214SELECT student.StudentID
215FROM Assign
216JOIN student ON Assign.assignID = student.StudentID
217WHERE rating = 7;
218
219-- Query 4
220SELECT StudentID, GPA, Grad_year
221FROM Student
222WHERE honors_YorN = 'Y' AND GPA = 4;
223
224-- Report 3
225SELECT eventID, Title, locationDescription, Date_Time, ticketCost
226FROM Event WHERE ticketCost = (
227 SELECT min(ticketCost)
228 FROM Event
229 WHERE Title = 'Festival' AND locationDescription = 'InCampus');
230
231-- Report 4
232SELECT assignID, AVG(rating), year
233FROM Assign
234WHERE year > 'Sep 2, 2018'
235GROUP BY assignID, rating
236ORDER BY assignID, rating;
237
238
239-- Stored Procedure
240DELIMITER //
241
242CREATE PROCEDURE Rating()
243begin
244
245select * from assign where rating = '4';
246
247
248end //
249
250DELIMITER ;
251
252call Rating();
253
254drop procedure Rating;