0twLQQ0z

1-- MySQL dump 10.11
2--
3-- Host: localhost    Database: college
4-- ------------------------------------------------------
5-- Server version	5.0.75-0ubuntu10.5
6
7/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10/*!40101 SET NAMES utf8 */;
11/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12/*!40103 SET TIME_ZONE='+00:00' */;
13/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17
18--
19-- Table structure for table `course`
20--
21
22DROP TABLE IF EXISTS `course`;
23SET @saved_cs_client     = @@character_set_client;
24SET character_set_client = utf8;
25CREATE TABLE `course` (
26  `course_name` varchar(20) NOT NULL,
27  `description` varchar(100) default NULL,
28  PRIMARY KEY  (`course_name`)
29) ENGINE=InnoDB DEFAULT CHARSET=latin1;
30SET character_set_client = @saved_cs_client;
31
32--
33-- Dumping data for table `course`
34--
35
36LOCK TABLES `course` WRITE;
37/*!40000 ALTER TABLE `course` DISABLE KEYS */;
38INSERT INTO `course` VALUES ('ABC','Primary requirement');
39/*!40000 ALTER TABLE `course` ENABLE KEYS */;
40UNLOCK TABLES;
41
42--
43-- Table structure for table `department`
44--
45
46DROP TABLE IF EXISTS `department`;
47SET @saved_cs_client     = @@character_set_client;
48SET character_set_client = utf8;
49CREATE TABLE `department` (
50  `name` varchar(15) NOT NULL,
51  `phoneno` int(10) default NULL,
52  PRIMARY KEY  (`name`)
53) ENGINE=InnoDB DEFAULT CHARSET=latin1;
54SET character_set_client = @saved_cs_client;
55
56--
57-- Dumping data for table `department`
58--
59
60LOCK TABLES `department` WRITE;
61/*!40000 ALTER TABLE `department` DISABLE KEYS */;
62INSERT INTO `department` VALUES ('COET',1234);
63/*!40000 ALTER TABLE `department` ENABLE KEYS */;
64UNLOCK TABLES;
65
66--
67-- Table structure for table `employee`
68--
69
70DROP TABLE IF EXISTS `employee`;
71SET @saved_cs_client     = @@character_set_client;
72SET character_set_client = utf8;
73CREATE TABLE `employee` (
74  `ssn` int(9) NOT NULL,
75  `datefojoin` date default NULL,
76  `designation` varchar(15) default NULL,
77  `salary` int(6) default NULL,
78  PRIMARY KEY  (`ssn`),
79  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`ssn`) REFERENCES `person` (`ssn`)
80) ENGINE=InnoDB DEFAULT CHARSET=latin1;
81SET character_set_client = @saved_cs_client;
82
83--
84-- Dumping data for table `employee`
85--
86
87LOCK TABLES `employee` WRITE;
88/*!40000 ALTER TABLE `employee` DISABLE KEYS */;
89/*!40000 ALTER TABLE `employee` ENABLE KEYS */;
90UNLOCK TABLES;
91
92--
93-- Table structure for table `hired_by`
94--
95
96DROP TABLE IF EXISTS `hired_by`;
97SET @saved_cs_client     = @@character_set_client;
98SET character_set_client = utf8;
99CREATE TABLE `hired_by` (
100  `t_ssn` int(9) NOT NULL,
101  `dept_name` varchar(15) default NULL,
102  PRIMARY KEY  (`t_ssn`),
103  UNIQUE KEY `dept_name` (`dept_name`),
104  CONSTRAINT `hired_by_ibfk_1` FOREIGN KEY (`t_ssn`) REFERENCES `teachingstaff` (`essn`),
105  CONSTRAINT `hired_by_ibfk_2` FOREIGN KEY (`dept_name`) REFERENCES `department` (`name`)
106) ENGINE=InnoDB DEFAULT CHARSET=latin1;
107SET character_set_client = @saved_cs_client;
108
109--
110-- Dumping data for table `hired_by`
111--
112
113LOCK TABLES `hired_by` WRITE;
114/*!40000 ALTER TABLE `hired_by` DISABLE KEYS */;
115/*!40000 ALTER TABLE `hired_by` ENABLE KEYS */;
116UNLOCK TABLES;
117
118--
119-- Table structure for table `offers`
120--
121
122DROP TABLE IF EXISTS `offers`;
123SET @saved_cs_client     = @@character_set_client;
124SET character_set_client = utf8;
125CREATE TABLE `offers` (
126  `dname` varchar(15) NOT NULL,
127  `cname` varchar(20) default NULL,
128  `noofseats` int(4) default NULL,
129  PRIMARY KEY  (`dname`),
130  UNIQUE KEY `cname` (`cname`),
131  CONSTRAINT `offers_ibfk_1` FOREIGN KEY (`dname`) REFERENCES `department` (`name`),
132  CONSTRAINT `offers_ibfk_2` FOREIGN KEY (`cname`) REFERENCES `course` (`course_name`)
133) ENGINE=InnoDB DEFAULT CHARSET=latin1;
134SET character_set_client = @saved_cs_client;
135
136--
137-- Dumping data for table `offers`
138--
139
140LOCK TABLES `offers` WRITE;
141/*!40000 ALTER TABLE `offers` DISABLE KEYS */;
142/*!40000 ALTER TABLE `offers` ENABLE KEYS */;
143UNLOCK TABLES;
144
145--
146-- Table structure for table `person`
147--
148
149DROP TABLE IF EXISTS `person`;
150SET @saved_cs_client     = @@character_set_client;
151SET character_set_client = utf8;
152CREATE TABLE `person` (
153  `ssn` int(9) NOT NULL,
154  `name` varchar(50) default NULL,
155  `houseno` int(3) default NULL,
156  `city` varchar(15) default NULL,
157  `zipcode` int(6) default NULL,
158  `phoneno` int(10) default NULL,
159  `dateofbirth` date default NULL,
160  PRIMARY KEY  (`ssn`)
161) ENGINE=InnoDB DEFAULT CHARSET=latin1;
162SET character_set_client = @saved_cs_client;
163
164--
165-- Dumping data for table `person`
166--
167
168LOCK TABLES `person` WRITE;
169/*!40000 ALTER TABLE `person` DISABLE KEYS */;
170INSERT INTO `person` VALUES (8489348,'Pyrix M',0,'',0,0,'1990-05-21');
171/*!40000 ALTER TABLE `person` ENABLE KEYS */;
172UNLOCK TABLES;
173
174--
175-- Table structure for table `researcher`
176--
177
178DROP TABLE IF EXISTS `researcher`;
179SET @saved_cs_client     = @@character_set_client;
180SET character_set_client = utf8;
181CREATE TABLE `researcher` (
182  `s_ssn` int(9) NOT NULL,
183  `t_ssn` int(9) default NULL,
184  `areaofresearch` varchar(50) default NULL,
185  `noofpapers` int(3) default NULL,
186  PRIMARY KEY  (`s_ssn`),
187  UNIQUE KEY `t_ssn` (`t_ssn`),
188  CONSTRAINT `researcher_ibfk_1` FOREIGN KEY (`s_ssn`) REFERENCES `employee` (`ssn`),
189  CONSTRAINT `researcher_ibfk_2` FOREIGN KEY (`t_ssn`) REFERENCES `teachingstaff` (`essn`)
190) ENGINE=InnoDB DEFAULT CHARSET=latin1;
191SET character_set_client = @saved_cs_client;
192
193--
194-- Dumping data for table `researcher`
195--
196
197LOCK TABLES `researcher` WRITE;
198/*!40000 ALTER TABLE `researcher` DISABLE KEYS */;
199/*!40000 ALTER TABLE `researcher` ENABLE KEYS */;
200UNLOCK TABLES;
201
202--
203-- Table structure for table `student`
204--
205
206DROP TABLE IF EXISTS `student`;
207SET @saved_cs_client     = @@character_set_client;
208SET character_set_client = utf8;
209CREATE TABLE `student` (
210  `ssn` int(9) NOT NULL,
211  `semester` int(1) default NULL,
212  `dept_name` varchar(15) default NULL,
213  `course_name` varchar(20) default NULL,
214  PRIMARY KEY  (`ssn`),
215  KEY `dept_name` (`dept_name`),
216  KEY `course_name` (`course_name`),
217  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`ssn`) REFERENCES `person` (`ssn`),
218  CONSTRAINT `student_ibfk_2` FOREIGN KEY (`dept_name`) REFERENCES `department` (`name`),
219  CONSTRAINT `student_ibfk_3` FOREIGN KEY (`course_name`) REFERENCES `course` (`course_name`)
220) ENGINE=InnoDB DEFAULT CHARSET=latin1;
221SET character_set_client = @saved_cs_client;
222
223--
224-- Dumping data for table `student`
225--
226
227LOCK TABLES `student` WRITE;
228/*!40000 ALTER TABLE `student` DISABLE KEYS */;
229/*!40000 ALTER TABLE `student` ENABLE KEYS */;
230UNLOCK TABLES;
231
232--
233-- Table structure for table `teachingstaff`
234--
235
236DROP TABLE IF EXISTS `teachingstaff`;
237SET @saved_cs_client     = @@character_set_client;
238SET character_set_client = utf8;
239CREATE TABLE `teachingstaff` (
240  `essn` int(9) NOT NULL,
241  `qualification` varchar(10) default NULL,
242  `specialization` varchar(10) default NULL,
243  `dept_name` varchar(15) default NULL,
244  `cname` varchar(20) default NULL,
245  PRIMARY KEY  (`essn`),
246  KEY `dept_name` (`dept_name`),
247  KEY `cname` (`cname`),
248  CONSTRAINT `teachingstaff_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`name`),
249  CONSTRAINT `teachingstaff_ibfk_2` FOREIGN KEY (`cname`) REFERENCES `course` (`course_name`)
250) ENGINE=InnoDB DEFAULT CHARSET=latin1;
251SET character_set_client = @saved_cs_client;
252
253--
254-- Dumping data for table `teachingstaff`
255--
256
257LOCK TABLES `teachingstaff` WRITE;
258/*!40000 ALTER TABLE `teachingstaff` DISABLE KEYS */;
259/*!40000 ALTER TABLE `teachingstaff` ENABLE KEYS */;
260UNLOCK TABLES;
261/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
262
263/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
264/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
265/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
266/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
267/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
268/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
269/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
270
271-- Dump completed on 2010-12-02 16:51:58