--
-- Host: localhost    Database: demo
-- ------------------------------------------------------


--
-- Table structure for table `addrbook`
--

CREATE TABLE `addrbook` (
  `lname` varchar(20) NOT NULL,
  `fname` varchar(20) NOT NULL,
  `street` varchar(50) NOT NULL,
  `city` varchar(20) NOT NULL,
  `state` char(2) NOT NULL,
  `zip` char(5) NOT NULL,
  `phone` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `addrbook`
--

INSERT INTO `addrbook` VALUES ('Anile','Joseph','35 Forest Avenue','Glen Cove','NY','12345','(516)937-4021'),('Brown','Robert','96 Landing Road','Brooklyn','NY','12345','(718)921-7505'),('Colin','Albert','64 Lisa Drive','Plainview','NY','12345','(516)671-2895'),('Davies','Todd','129 Georgia Street','Forest Hills','NY','12345','(718)496-3717'),('Eisner','Jack','6 Sunset Drive','East Norwich','CT','12345','(215)759-1976'),('Field','Diane','254 West 79 Street','New York','NY','12345','(212)647-9372'),('Gambi','Teresa','12 Cypress Avenue','Syosset','NY','12345','(516)299-5478'),('Hart','Richard','31 Cherry Lane','Hicksville','NY','12345','(516)679-7920'),('Izzo','Anthony','342 Coleridge Drive','Locus Grove','NY','12345','(516)367-3066'),('Johnson','Carl','8 Bayview Drive','Bayville','NY','12345','(516)299-7643'),('Sullivan','John','22 Oakdale Avenue','Huntington','NY','12345','(516)453-3239'),('Sultan','Sam','123 Main Street','New York','NY','12345','(212)123-1234'),('Sultan','Carol','123 Main Street','New York','NY','12345','(212)123-1234'),('Tillman','Christopher','70 Oyster Bay Road','Oyster Bay','NY','12345','(516)922-4941'),('Urich','Robert','123 Hollywood Lane','Century City','CA','12345','(415)498-5301'),('Valante','Vincent','67 Redbank Road','Holmdel','NJ','12345','(212)123-1234'),('Williams','Leonard','1267 2nd Ave','New York','NY','12345','(212)765-3476'),('Xu','Wei','16 Jericho Turnpike','Jericho','NY','12345','(516)628-8309'),('Yen','Jerry','123 Johnson Circle','Bethpage','NY','12345','(631)327-8567');

--
-- Table structure for table `address`
--

CREATE TABLE `address` (
  `address_id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_id` int(11) DEFAULT NULL,
  `inst_id` int(11) DEFAULT NULL,
  `addr_type` char(1) NOT NULL,
  `street1` varchar(50) NOT NULL,
  `street2` varchar(50) DEFAULT NULL,
  `city` varchar(25) NOT NULL,
  `state` char(2) DEFAULT NULL,
  `country` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`address_id`),
  KEY `addr_stu_idx` (`stu_id`),
  KEY `addr_inst_idx` (`inst_id`),
  CONSTRAINT `inst_id_fk` FOREIGN KEY (`inst_id`) REFERENCES `instructor` (`instructor_id`),
  CONSTRAINT `stu_id_fk` FOREIGN KEY (`stu_id`) REFERENCES `student` (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `address`
--

INSERT INTO `address` VALUES (1,NULL,1,'H','123 Main Street','','Bronx','NY',''),(2,NULL,1,'W','7 East 12 Street','','New York','NY',''),(3,15,NULL,'H','828 Eight Avenue',NULL,'New York','NY',''),(4,3,NULL,'H','222 Second Avenue',NULL,'Brooklyn','NY',''),(5,3,NULL,'W','126 Madison Avenue',NULL,'New York','NY',''),(6,NULL,3,'H','5 West 4th Street','Apt 44','New York','NY',''),(7,5,NULL,'W','323 Third Avenue',NULL,'Bronx','NY',''),(8,7,NULL,'H','424 Forth Avenue',NULL,'New York','NY',''),(9,9,NULL,'W','525 Fifth Avenue',NULL,'Yonkers','NY',''),(10,1,NULL,'H','121 First Avenue',NULL,'New York','NY',''),(11,11,NULL,'W','626 Sixth Avenue',NULL,'Hoboken','NJ',''),(12,13,NULL,'H','727 Seventh Avenue',NULL,'New York','NY',''),(13,17,NULL,'W','929 Ninth Avenue',NULL,'Brooklyn','NY',''),(14,19,NULL,'H','101 Tenth Avenue',NULL,'Newark','NJ',''),(15,NULL,5,'H','121 Eleventh Avenue',NULL,'New York','NY',''),(16,NULL,5,'W','48 East 42 Street',NULL,'New York','NY',''),(17,21,NULL,'H','1 Ontario Drive',NULL,'Toronto',NULL,'Canada'),(18,21,NULL,'W','1155 Browdway',NULL,'New York','NY','');

--
-- Table structure for table `course`
--

CREATE TABLE `course` (
  `course_id` char(8) NOT NULL,
  `description` varchar(40) NOT NULL,
  `price` int(11) NOT NULL,
  PRIMARY KEY (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `course`
--

INSERT INTO `course` VALUES ('X52-9238','Introduction to Java',1095),('X52-9267','Object Oriented Analysis and Design',995),('X52-9272','SQL Programming Language',540),('X52-9562','Java web Services',1095),('X52-9740','Web Page Development with HTML',1095),('X52-9742','Intensive Web Development',3995),('X52-9755','JavaScript',1095),('X52-9759','XML for Web Development',1095);

--
-- Table structure for table `hierarchy`
--

CREATE TABLE `hierarchy` (
  `employee_id` int(11) NOT NULL AUTO_INCREMENT,
  `lname` varchar(20) NOT NULL,
  `fname` varchar(20) NOT NULL,
  `manager_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  KEY `hier_name_idx` (`lname`,`fname`),
  KEY `hier_mgr_fk` (`manager_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `hierarchy`
--

INSERT INTO `hierarchy` VALUES (1,'Burns','Barbara',NULL),(2,'Tobias','Wayne',1),(3,'Stack','Patrick',1),(4,'Milgrom','Anya',2),(5,'Vasquez','Lilian',4),(6,'Davidson','Duncan',4);

--
-- Table structure for table `instructor_info`
--

CREATE TABLE `instructor_info` (
  `instructor_id` int(11) NOT NULL,
  `active_status` char(1) DEFAULT 'A',
  `start_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `specialty` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`instructor_id`),
  CONSTRAINT `inst_info_fk` FOREIGN KEY (`instructor_id`) REFERENCES `instructor` (`instructor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `instructor_info`
--

INSERT INTO `instructor_info` VALUES (1,'A','1999-09-01 04:00:00','Web technology, Java, XML, SQL'),(2,'A','2000-02-15 05:00:00','ASP, .NET, HTML'),(3,'A','1998-07-01 04:00:00',NULL),(4,'A','1995-01-01 05:00:00','Oracle, DB2'),(5,'I','2003-03-25 05:00:00',NULL),(6,'A','2001-05-15 04:00:00','JavaScript, C, Flash');

--
-- Table structure for table `output`
--

CREATE TABLE `output` (
  `line` varchar(4000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `output`
--


--
-- Table structure for table `payment`
--

CREATE TABLE `payment` (
  `payment_num` int(11) NOT NULL AUTO_INCREMENT,
  `vendor` varchar(40) NOT NULL,
  `amount` decimal(6,2) DEFAULT '0.00',
  `description` varchar(40) NOT NULL,
  `pay_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_num`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `payment`
--

INSERT INTO `payment` VALUES (1,'Con Edison','125.15','Electric','2012-02-01 05:00:00'),(2,'Verizon','54.79','Home Phone','2012-03-01 05:00:00'),(3,'Allstate','1240.50','Home Insurance','2012-03-02 05:00:00'),(4,'Verizon','49.95','Cell Phone','2012-03-03 05:00:00'),(5,'Verizon','39.95','DSL Line','2012-03-04 05:00:00'),(6,'Con Edison','59.63','Electric','2012-03-05 05:00:00'),(7,'Department of Water','155.43','Water and Sewers','2012-03-06 05:00:00'),(8,'Allstate','1550.25','Car Insurance','2012-03-07 05:00:00');

--
-- Table structure for table `relationship`
--

CREATE TABLE `relationship` (
  `row_pk` int(11) NOT NULL AUTO_INCREMENT,
  `from_id` int(11) NOT NULL,
  `to_id` int(11) NOT NULL,
  PRIMARY KEY (`row_pk`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `relationship`
--

INSERT INTO `relationship` VALUES (1,1,3),(2,2,3),(3,3,4),(4,6,4),(5,3,5),(6,6,5),(7,4,7),(8,4,8),(9,4,9);

--
-- Table structure for table `student_email`
--

CREATE TABLE `student_email` (
  `email_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`email_id`),
  KEY `stu_email_idx` (`student_id`),
  CONSTRAINT `stu_email_fk` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `student_email`
--

INSERT INTO `student_email` VALUES (1,1,'barbara.burns@nyu.edu'),(2,2,'vcambria@yahoo.com'),(3,3,'duncan.davidson@nyu.edu'),(4,3,'davidson123@aol.com'),(5,5,'ethomas@xyz.com'),(6,8,'rick.myers@nyu.edu'),(7,8,'rick.myers@abc.com'),(8,8,'myers101@aol.com'),(9,12,'none'),(10,15,'soleyj@nyu.edu');

--
-- Temporary table structure for view `v_key_column`
--

/*!50001 DROP VIEW IF EXISTS `v_key_column`*/;
/*!50001 CREATE TABLE `v_key_column` (
  `table_name` varchar(64),
  `column_name` varchar(64),
  `constraint_type` varchar(73),
  `constraint_name` varchar(64),
  `ordinal_position` bigint(10),
  `referenced_table_name` varchar(64),
  `referenced_column_name` varchar(64)
) ENGINE=MyISAM */;

--
-- Final view structure for view `v_key_column`
--

/*!50001 DROP TABLE IF EXISTS `v_key_column`*/;
/*!50001 DROP VIEW IF EXISTS `v_key_column`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`demo`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_key_column` AS select `k`.`TABLE_NAME` AS `table_name`,`k`.`COLUMN_NAME` AS `column_name`,replace(`t`.`CONSTRAINT_TYPE`,'PRIMARY',' PRIMARY') AS `constraint_type`,`k`.`CONSTRAINT_NAME` AS `constraint_name`,`k`.`ORDINAL_POSITION` AS `ordinal_position`,`k`.`REFERENCED_TABLE_NAME` AS `referenced_table_name`,`k`.`REFERENCED_COLUMN_NAME` AS `referenced_column_name` from (`information_schema`.`table_constraints` `t` join `information_schema`.`key_column_usage` `k`) where ((`t`.`TABLE_SCHEMA` = `k`.`TABLE_SCHEMA`) and (`t`.`TABLE_NAME` = `k`.`TABLE_NAME`) and (`t`.`CONSTRAINT_NAME` = `k`.`CONSTRAINT_NAME`)) order by 1,3,2,4,5 */;


