DDL

-- MySQL Administrator dump 1.4
 
--
 
-- ------------------------------------------------------
 
-- Server version   5.0.16-nt
 
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
 
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 
/*!40101 SET NAMES utf8 */;
 
 
 
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
 
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 
 
 
 
 
--
 
-- Create schema `my_address_book`
 
--
 
 
 
DROP DATABASE IF EXISTS `my_address_book`; 
 
CREATE DATABASE `my_address_book` /*!40100 DEFAULT CHARACTER SET latin1 */;
 
USE `my_address_book`;
 
 
 
--
 
-- Table structure for table `my_address_book`.`contacts`
 
--
 
 
 
DROP TABLE IF EXISTS `my_address_book`.`contacts`;
 
CREATE TABLE  `my_address_book`.`contacts` (
 
  `CONTACT_ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 
  `FIRST_NAME` varchar(45) NOT NULL,
 
  `MIDDLE_NAME` varchar(45) DEFAULT NULL,
 
  `LAST_NAME` varchar(45) NOT NULL,
 
  `EMAIL` varchar(45) DEFAULT NULL,
 
  PRIMARY KEY  (`CONTACT_ID`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='store contacts personal details';
 
 
 
--
 
-- Table structure for table `my_address_book`.`event_type`
 
--
 
 
 
DROP TABLE IF EXISTS `my_address_book`.`event_type`;
 
CREATE TABLE  `my_address_book`.`event_type` (
 
  `EVENT_TYPE_ID` int(2) UNSIGNED NOT NULL AUTO_INCREMENT,
 
  `EVENT_NAME` varchar(50) NOT NULL,
 
  PRIMARY KEY  (`EVENT_TYPE_ID`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='stores type of events';
 
 
 
--
 
-- Table structure for table `my_address_book`.`event_detail`
 
--
 
 
 
DROP TABLE IF EXISTS `my_address_book`.`event_detail`;
 
CREATE TABLE  `my_address_book`.`event_detail` (
 
  `EVENT_DETAIL_ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 
  `EVENT_TYPE_ID` int(2) UNSIGNED NOT NULL,
 
  `CONTACT_ID` int(10) UNSIGNED NOT NULL,
 
  `DATE` date NOT NULL,
 
  PRIMARY KEY  (`EVENT_DETAIL_ID`),
 
  KEY `FK_event_detail_1` (`EVENT_TYPE_ID`),
 
  KEY `FK_event_detail_2` (`CONTACT_ID`),
 
  CONSTRAINT `FK_event_detail_1` FOREIGN KEY (`EVENT_TYPE_ID`) REFERENCES `event_type` (`EVENT_TYPE_ID`),
 
  CONSTRAINT `FK_event_detail_2` FOREIGN KEY (`CONTACT_ID`) REFERENCES `contacts` (`CONTACT_ID`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='stores event details of each contact';
 
 
 
 
 
--
 
-- Table structure for table `my_address_book`.`address_type`
 
--
 
 
 
DROP TABLE IF EXISTS `my_address_book`.`address_type`;
 
CREATE TABLE  `my_address_book`.`address_type` (
 
  `ADDRESS_TYPE_ID` int(2) UNSIGNED NOT NULL AUTO_INCREMENT,
 
  `ADDRESS_CATEGORY` varchar(45) NOT NULL,
 
  PRIMARY KEY  (`ADDRESS_TYPE_ID`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='stores types of addesses';
 
 
 
--
 
-- Table structure for table `my_address_book`.`address_detail`
 
--
 
 
 
DROP TABLE IF EXISTS `my_address_book`.`address_detail`;
 
CREATE TABLE  `my_address_book`.`address_detail` (
 
  `ADDRESS_DETAIL_ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 
  `ADDRESS_TYPE_ID` int(2) UNSIGNED NOT NULL,
 
  `CONTACT_ID` int(10) UNSIGNED NOT NULL,
 
  `COUNTRY` varchar(50) NOT NULL,
 
  `CITY` varchar(50) NOT NULL,
 
  `STREET` varchar(100) NOT NULL,
 
  `EXTRA_DETAIL` varchar(100) DEFAULT NULL,
 
  PRIMARY KEY  (`ADDRESS_DETAIL_ID`),
 
  KEY `FK_address_detail_1` (`ADDRESS_TYPE_ID`),
 
  KEY `FK_address_detail_2` (`CONTACT_ID`),
 
  CONSTRAINT `FK_address_detail_1` FOREIGN KEY (`ADDRESS_TYPE_ID`) REFERENCES `address_type` (`ADDRESS_TYPE_ID`),
 
  CONSTRAINT `FK_address_detail_2` FOREIGN KEY (`CONTACT_ID`) REFERENCES `contacts` (`CONTACT_ID`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='stores address details of each contact';
 
 
 
--
 
-- Table structure for table `my_address_book`.`phone_type`
 
--
 
 
 
DROP TABLE IF EXISTS `my_address_book`.`phone_type`;
 
CREATE TABLE  `my_address_book`.`phone_type` (
 
  `PHONE_TYPE_ID` int(2) UNSIGNED NOT NULL AUTO_INCREMENT,
 
  `PHONE_CATEGORY` varchar(45) NOT NULL,
 
  PRIMARY KEY  (`PHONE_TYPE_ID`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='stores categories of phone contacts';
 
 
 
--
 
-- Table structure for table `my_address_book`.`phone_detail`
 
--
 
 
 
DROP TABLE IF EXISTS `my_address_book`.`phone_detail`;
 
CREATE TABLE  `my_address_book`.`phone_detail` (
 
  `PHONE_DETAIL_ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 
  `PHONE_TYPE_ID` int(2) UNSIGNED NOT NULL,
 
  `CONTACT_ID` int(10) UNSIGNED NOT NULL,
 
  `PHONE_NUMBER` int(9) NOT NULL,
 
  PRIMARY KEY  (`PHONE_DETAIL_ID`),
 
  KEY `FK_phone_detail_1` (`PHONE_TYPE_ID`),
 
  KEY `FK_phone_detail_2` (`CONTACT_ID`),
 
  CONSTRAINT `FK_phone_detail_1` FOREIGN KEY (`PHONE_TYPE_ID`) REFERENCES `phone_type` (`PHONE_TYPE_ID`),
 
  CONSTRAINT `FK_phone_detail_2` FOREIGN KEY (`CONTACT_ID`) REFERENCES `contacts` (`CONTACT_ID`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='stores phone details of each contact';
 
 
 
--
 
-- Create users and privilages
 
--
 
 
 
CREATE USER `alex`@`localhost` IDENTIFIED BY `mbalc28`;
 
 
 
GRANT ALL PRIVILEGES ON my_address_book.contacts TO `alex`@`localhost`IDENTIFIED BY `mbalc28`;
 
 
 
CREATE USER `anthony`@`localhost` IDENTIFIED BY `mban28`;
 
 
 
GRANT SELECT,INSERT,UPDATE,DELETE ON my_address_book.* TO `anthony`@`localhost`IDENTIFIED BY `mban28`;
 
 
 
CREATE USER `tom`@`localhost` IDENTIFIED BY `mwto28`;
 
 
 
GRANT SELECT,INSERT,UPDATE,DELETE ON my_address_book.* TO `tom`@`localhost`IDENTIFIED BY `mwto28`;
 
 
 
CREATE USER `nick`@`localhost` IDENTIFIED BY `nzni28`;
 
 
 
GRANT SELECT,INSERT,UPDATE,DELETE ON my_address_book.* TO `nick`@`localhost`IDENTIFIED BY `nzni28`;