YqDKtQju

1create database IndividualProject;
2use IndividualProject;
3
4-- phpMyAdmin SQL Dump
5-- version 3.4.10.1deb1
6-- http://www.phpmyadmin.net
7--
8-- Host: localhost
9-- Generation Time: Mar 08, 2013 at 02:58 AM
10-- Server version: 5.5.29
11-- PHP Version: 5.3.10-1ubuntu3.5
12
13SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
14SET time_zone = "+00:00";
15
16
17/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
18/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
19/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
20/*!40101 SET NAMES utf8 */;
21
22--
23-- Database: `IndividualProject`
24--
25
26-- --------------------------------------------------------
27
28--
29-- Table structure for table `EventAssignments`
30--
31
32CREATE TABLE IF NOT EXISTS `EventAssignments` (
33  `id` int(11) NOT NULL AUTO_INCREMENT,
34  `eventid` int(11) NOT NULL,
35  `userid` int(11) NOT NULL,
36  `adminid` int(11) NOT NULL,
37  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
38  `done` tinyint(1) NOT NULL DEFAULT '0',
39  PRIMARY KEY (`id`),
40  UNIQUE KEY `eventid_2` (`eventid`,`userid`),
41  KEY `eventid` (`eventid`),
42  KEY `userid` (`userid`),
43  KEY `adminid` (`adminid`)
44) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
45
46--
47-- Dumping data for table `EventAssignments`
48--
49
50INSERT INTO `EventAssignments` (`id`, `eventid`, `userid`, `adminid`, `timestamp`, `done`) VALUES
51(1, 1, 4, 1, '2013-03-08 07:50:38', 0),
52(2, 2, 4, 1, '2013-03-08 07:51:50', 0),
53(3, 2, 3, 1, '2013-03-08 07:53:30', 0);
54
55-- --------------------------------------------------------
56
57--
58-- Table structure for table `Events`
59--
60
61CREATE TABLE IF NOT EXISTS `Events` (
62  `id` int(11) NOT NULL AUTO_INCREMENT,
63  `startTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
64  `endTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
65  `duration` int(11) NOT NULL,
66  `numslots` int(11) NOT NULL,
67  `location` varchar(30) NOT NULL,
68  `supervisor` varchar(20) NOT NULL,
69  `title` varchar(30) NOT NULL,
70  PRIMARY KEY (`id`),
71  KEY `startTime` (`startTime`)
72) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
73
74--
75-- Dumping data for table `Events`
76--
77
78INSERT INTO `Events` (`id`, `startTime`, `endTime`, `duration`, `numslots`, `location`, `supervisor`, `title`) VALUES
79(1, '2013-03-09 15:00:00', '2013-03-09 23:00:00', 30, 16, 'CS 2311', 'Michael TA', 'CSE308 Grading'),
80(2, '2013-03-08 12:00:00', '2013-03-08 22:00:00', 45, 13, 'CS 2900', 'Howard Taft', '380 Grading');
81
82-- --------------------------------------------------------
83
84--
85-- Table structure for table `Reservations`
86--
87
88CREATE TABLE IF NOT EXISTS `Reservations` (
89  `id` int(11) NOT NULL AUTO_INCREMENT,
90  `userid` int(11) DEFAULT NULL,
91  `eventid` int(11) DEFAULT NULL,
92  `timereserved` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
93  `startTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
94  `slotnum` int(11) NOT NULL,
95  PRIMARY KEY (`id`),
96  UNIQUE KEY `user_event_key` (`userid`,`eventid`),
97  KEY `eventid` (`eventid`)
98) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
99
100--
101-- Dumping data for table `Reservations`
102--
103
104INSERT INTO `Reservations` (`id`, `userid`, `eventid`, `timereserved`, `startTime`, `slotnum`) VALUES
105(1, 3, 2, '2013-03-08 07:54:14', '2013-03-08 21:00:00', 12),
106(2, 4, 2, '2013-03-08 07:54:52', '2013-03-08 12:00:00', 0),
107(3, 4, 1, '2013-03-08 07:55:00', '2013-03-09 22:30:00', 15);
108
109-- --------------------------------------------------------
110
111--
112-- Table structure for table `Users`
113--
114
115CREATE TABLE IF NOT EXISTS `Users` (
116  `id` int(11) NOT NULL AUTO_INCREMENT,
117  `username` varchar(20) DEFAULT NULL,
118  `password` varchar(20) DEFAULT NULL,
119  `fullname` varchar(50) DEFAULT NULL,
120  `usertype` int(11) DEFAULT NULL,
121  PRIMARY KEY (`id`),
122  UNIQUE KEY `username` (`username`)
123) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
124
125--
126-- Dumping data for table `Users`
127--
128
129INSERT INTO `Users` (`id`, `username`, `password`, `fullname`, `usertype`) VALUES
130(1, 'admin', 'admin', 'Administrator', 0),
131(2, 'viewer', 'viewer', 'Michael Viewer', 1),
132(3, 'user', 'user', 'Joey User', 2),
133(4, 'user2', 'user2', 'Kenny User2', 2);
134
135--
136-- Constraints for dumped tables
137--
138
139--
140-- Constraints for table `EventAssignments`
141--
142ALTER TABLE `EventAssignments`
143  ADD CONSTRAINT `EventAssignments_ibfk_1` FOREIGN KEY (`eventid`) REFERENCES `Events` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
144  ADD CONSTRAINT `EventAssignments_ibfk_2` FOREIGN KEY (`userid`) REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
145  ADD CONSTRAINT `EventAssignments_ibfk_3` FOREIGN KEY (`adminid`) REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
146
147--
148-- Constraints for table `Reservations`
149--
150ALTER TABLE `Reservations`
151  ADD CONSTRAINT `Reservations_ibfk_4` FOREIGN KEY (`userid`) REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
152  ADD CONSTRAINT `Reservations_ibfk_5` FOREIGN KEY (`eventid`) REFERENCES `Events` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
153
154/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
155/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
156/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;