DROP TABLE IF EXISTS `person`;
DROP TABLE IF EXISTS `address`;
DROP TABLE IF EXISTS `phone_nr`;
DROP TABLE IF EXISTS `email`;
DROP TABLE IF EXISTS `category`;

CREATE TABLE `person` (
	`id` INTEGER NOT NULL AUTO_INCREMENT DEFAULT NULL,
	`fname` VARCHAR(100) NOT NULL,
	`lname` VARCHAR(100) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `category` (
	`id` INTEGER NOT NULL AUTO_INCREMENT DEFAULT NULL,
	`category` VARCHAR(200) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `address` (
	`pid` INTEGER NOT NULL,
	`category` INTEGER NOT NULL,
	`street` VARCHAR(200) NOT NULL,
	`street_nr` INTEGER(10) NOT NULL,
	`apartment_nr` VARCHAR(10) NULL,
	`postal_code` INTEGER(20) NOT NULL,
	`city` VARCHAR(100) NOT NULL,
	PRIMARY KEY (`pid`, `category`),
	FOREIGN KEY (`pid`) REFERENCES `person` (`id`) RESTRICT,
	FOREIGN KEY (`category`) REFERENCES `category` (`id`) RESTRICT
) ENGINE=InnoDB;

CREATE TABLE `phone_nr` (
	`pid` INTEGER NOT NULL,
	`category` INTEGER NOT NULL,
	`number` INTEGER NOT NULL,
	PRIMARY KEY (`pid`, `category`),
	FOREIGN KEY (`pid`) REFERENCES `person` (`id`) RESTRICT,
	FOREIGN KEY (`category`) REFERENCES `category` (`id`) RESTRICT
) ENGINE=InnoDB;

CREATE TABLE `email` (
	`pid` INTEGER NOT NULL,
	`category` INTEGER NOT NULL,
	`email` VARCHAR(200) NOT NULL,
	PRIMARY KEY (`pid`, `category`),
	FOREIGN KEY (`pid`) REFERENCES `person` (`id`) RESTRICT,
	FOREIGN KEY (`category`) REFERENCES `category` (`id`) RESTRICT
) ENGINE=InnoDB;