-- MySQL dump 10.13  Distrib 8.0.30, for macos12 (x86_64)
--
-- Host: localhost    Database: building
-- ------------------------------------------------------
-- Server version	8.0.30

/*!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 */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `apartment`
--

DROP TABLE IF EXISTS `apartment`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `apartment` (
  `buildingid` char(3) NOT NULL,
  `aptno` char(5) NOT NULL,
  `anoofbedrooms` int NOT NULL,
  `ccid` char(4) DEFAULT NULL,
  PRIMARY KEY (`buildingid`,`aptno`),
  KEY `ccid` (`ccid`),
  CONSTRAINT `apartment_ibfk_1` FOREIGN KEY (`buildingid`) REFERENCES `building` (`buildingid`),
  CONSTRAINT `apartment_ibfk_2` FOREIGN KEY (`ccid`) REFERENCES `corpclient` (`ccid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `apartment`
--

LOCK TABLES `apartment` WRITE;
/*!40000 ALTER TABLE `apartment` DISABLE KEYS */;
/*!40000 ALTER TABLE `apartment` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `building`
--

DROP TABLE IF EXISTS `building`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `building` (
  `buildingid` char(3) NOT NULL,
  `bnooffloors` int NOT NULL,
  `bmanagerid` char(4) NOT NULL,
  PRIMARY KEY (`buildingid`),
  KEY `bmanagerid` (`bmanagerid`),
  CONSTRAINT `building_ibfk_1` FOREIGN KEY (`bmanagerid`) REFERENCES `manager` (`managerid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `building`
--

LOCK TABLES `building` WRITE;
/*!40000 ALTER TABLE `building` DISABLE KEYS */;
INSERT INTO `building` VALUES ('B1',5,'M12'),('B2',6,'M23'),('B3',4,'M23'),('B4',4,'M34');
/*!40000 ALTER TABLE `building` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `cleaning`
--

DROP TABLE IF EXISTS `cleaning`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `cleaning` (
  `buildingid` char(3) NOT NULL,
  `aptno` char(5) NOT NULL,
  `smemberid` char(4) NOT NULL,
  PRIMARY KEY (`buildingid`,`aptno`,`smemberid`),
  KEY `cleaningfk2` (`smemberid`),
  CONSTRAINT `cleaningfk1` FOREIGN KEY (`buildingid`, `aptno`) REFERENCES `apartment` (`buildingid`, `aptno`),
  CONSTRAINT `cleaningfk2` FOREIGN KEY (`smemberid`) REFERENCES `staffmember` (`smemberid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `cleaning`
--

LOCK TABLES `cleaning` WRITE;
/*!40000 ALTER TABLE `cleaning` DISABLE KEYS */;
/*!40000 ALTER TABLE `cleaning` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `corpclient`
--

DROP TABLE IF EXISTS `corpclient`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `corpclient` (
  `ccid` char(4) NOT NULL,
  `ccname` varchar(25) NOT NULL,
  `ccindustry` varchar(25) NOT NULL,
  `cclocation` varchar(25) NOT NULL,
  `ccidreferredby` char(4) DEFAULT NULL,
  PRIMARY KEY (`ccid`),
  UNIQUE KEY `ccname` (`ccname`),
  KEY `ccidreferredby` (`ccidreferredby`),
  CONSTRAINT `corpclient_ibfk_1` FOREIGN KEY (`ccidreferredby`) REFERENCES `corpclient` (`ccid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `corpclient`
--

LOCK TABLES `corpclient` WRITE;
/*!40000 ALTER TABLE `corpclient` DISABLE KEYS */;
/*!40000 ALTER TABLE `corpclient` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `inspecting`
--

DROP TABLE IF EXISTS `inspecting`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `inspecting` (
  `insid` char(3) NOT NULL,
  `buildingid` char(3) NOT NULL,
  `datelast` date NOT NULL,
  `datenext` date NOT NULL,
  PRIMARY KEY (`insid`,`buildingid`),
  KEY `buildingid` (`buildingid`),
  CONSTRAINT `inspecting_ibfk_1` FOREIGN KEY (`buildingid`) REFERENCES `building` (`buildingid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `inspecting`
--

LOCK TABLES `inspecting` WRITE;
/*!40000 ALTER TABLE `inspecting` DISABLE KEYS */;
/*!40000 ALTER TABLE `inspecting` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `inspector`
--

DROP TABLE IF EXISTS `inspector`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `inspector` (
  `insid` char(3) NOT NULL,
  `insname` varchar(15) NOT NULL,
  PRIMARY KEY (`insid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `inspector`
--

LOCK TABLES `inspector` WRITE;
/*!40000 ALTER TABLE `inspector` DISABLE KEYS */;
/*!40000 ALTER TABLE `inspector` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `manager`
--

DROP TABLE IF EXISTS `manager`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `manager` (
  `managerid` char(4) NOT NULL,
  `mfname` varchar(15) NOT NULL,
  `mlname` varchar(15) NOT NULL,
  `mbdate` date NOT NULL,
  `msalary` decimal(9,2) NOT NULL,
  `mbonus` decimal(9,2) DEFAULT NULL,
  `mresbuildingid` char(3) DEFAULT NULL,
  PRIMARY KEY (`managerid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `manager`
--

LOCK TABLES `manager` WRITE;
/*!40000 ALTER TABLE `manager` DISABLE KEYS */;
INSERT INTO `manager` VALUES ('M12','Boris','Grant','1980-06-20',60000.00,NULL,'B1'),('M23','Austin','Lee','1975-10-30',50000.00,5000.00,'B2'),('M34','George','Sherman','1976-01-11',52000.00,2000.00,'B4');
/*!40000 ALTER TABLE `manager` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `managerphone`
--

DROP TABLE IF EXISTS `managerphone`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `managerphone` (
  `managerid` char(4) NOT NULL,
  `mphone` char(11) NOT NULL,
  PRIMARY KEY (`managerid`,`mphone`),
  CONSTRAINT `managerphone_ibfk_1` FOREIGN KEY (`managerid`) REFERENCES `manager` (`managerid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `managerphone`
--

LOCK TABLES `managerphone` WRITE;
/*!40000 ALTER TABLE `managerphone` DISABLE KEYS */;
INSERT INTO `managerphone` VALUES ('M12','555-2222'),('M12','555-3232'),('M23','555-9988'),('M34','555-9999');
/*!40000 ALTER TABLE `managerphone` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `staffmember`
--

DROP TABLE IF EXISTS `staffmember`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `staffmember` (
  `smemberid` char(4) NOT NULL,
  `smembername` varchar(15) NOT NULL,
  PRIMARY KEY (`smemberid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `staffmember`
--

LOCK TABLES `staffmember` WRITE;
/*!40000 ALTER TABLE `staffmember` DISABLE KEYS */;
/*!40000 ALTER TABLE `staffmember` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2023-02-27 18:20:34
