· 6 years ago · Dec 09, 2019, 12:40 PM
1CREATE DATABASE IF NOT EXISTS `police_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
2USE `police_db`;
3-- MySQL dump 10.13 Distrib 8.0.18, for Win64 (x86_64)
4--
5-- Host: localhost Database: police_db
6-- ------------------------------------------------------
7-- Server version 8.0.18
8
9/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
10/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
11/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
12/*!50503 SET NAMES utf8 */;
13/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
14/*!40103 SET TIME_ZONE='+00:00' */;
15/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
16/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
17/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
18/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19
20--
21-- Table structure for table `offence`
22--
23
24DROP TABLE IF EXISTS `offence`;
25/*!40101 SET @saved_cs_client = @@character_set_client */;
26/*!50503 SET character_set_client = utf8mb4 */;
27CREATE TABLE `offence` (
28 `offID` int(11) NOT NULL AUTO_INCREMENT,
29 `offMaxFn` int(11) DEFAULT NULL,
30 `offDesc` varchar(5000) DEFAULT NULL,
31 PRIMARY KEY (`offID`)
32) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
33/*!40101 SET character_set_client = @saved_cs_client */;
34
35--
36-- Dumping data for table `offence`
37--
38
39LOCK TABLES `offence` WRITE;
40/*!40000 ALTER TABLE `offence` DISABLE KEYS */;
41INSERT INTO `offence` VALUES (1,5000,'No Insurance'),(2,30,'Broken Headlight'),(3,70,'Speeding');
42/*!40000 ALTER TABLE `offence` ENABLE KEYS */;
43UNLOCK TABLES;
44
45--
46-- Table structure for table `person`
47--
48
49DROP TABLE IF EXISTS `person`;
50/*!40101 SET @saved_cs_client = @@character_set_client */;
51/*!50503 SET character_set_client = utf8mb4 */;
52CREATE TABLE `person` (
53 `peID` int(11) NOT NULL AUTO_INCREMENT,
54 `peName` varchar(80) NOT NULL,
55 `peDOB` date NOT NULL,
56 `peAdrs` varchar(120) NOT NULL,
57 PRIMARY KEY (`peID`)
58) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
59/*!40101 SET character_set_client = @saved_cs_client */;
60
61--
62-- Dumping data for table `person`
63--
64
65LOCK TABLES `person` WRITE;
66/*!40000 ALTER TABLE `person` DISABLE KEYS */;
67INSERT INTO `person` VALUES (1,'John Miller','1975-07-21','31 Rodly road NG7ASX'),(2,'Jack Johnson','1978-09-01','87 Rodly road NG7ASX'),(3,'Jon Snow','1991-02-07','128 Rodly road NG7ASX'),(4,'Joseph Alistair','1961-02-25','128 Rodly road NG7ASX');
68/*!40000 ALTER TABLE `person` ENABLE KEYS */;
69UNLOCK TABLES;
70
71--
72-- Table structure for table `report`
73--
74
75DROP TABLE IF EXISTS `report`;
76/*!40101 SET @saved_cs_client = @@character_set_client */;
77/*!50503 SET character_set_client = utf8mb4 */;
78CREATE TABLE `report` (
79 `reID` int(11) NOT NULL AUTO_INCREMENT,
80 `reOfficerID` int(11) NOT NULL,
81 `reFineAmnt` int(11) DEFAULT NULL,
82 `rePntsAwrded` int(11) DEFAULT NULL,
83 `reStmnt` varchar(5000) NOT NULL,
84 `reTime` datetime NOT NULL,
85 `rePersonID` int(11) NOT NULL,
86 `reVehReg` varchar(7) NOT NULL,
87 `reOffID` int(11) DEFAULT NULL,
88 PRIMARY KEY (`reID`),
89 KEY `fk_rePrsnID` (`rePersonID`),
90 KEY `fk_reVehReg` (`reVehReg`),
91 KEY `fk_offenceID` (`reOffID`),
92 CONSTRAINT `fk_offenceID` FOREIGN KEY (`reOffID`) REFERENCES `offence` (`offID`),
93 CONSTRAINT `fk_rePrsnID` FOREIGN KEY (`rePersonID`) REFERENCES `person` (`peID`),
94 CONSTRAINT `fk_reVehReg` FOREIGN KEY (`reVehReg`) REFERENCES `vehicle` (`veReg`)
95) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
96/*!40101 SET character_set_client = @saved_cs_client */;
97
98--
99-- Dumping data for table `report`
100--
101
102LOCK TABLES `report` WRITE;
103/*!40000 ALTER TABLE `report` DISABLE KEYS */;
104INSERT INTO `report` VALUES (1,1,60,NULL,'Fine issued for speeding','2019-12-18 13:17:17',2,'YG12XAB',3),(2,3,30,NULL,'Fined for broken headlight','2019-12-22 21:56:13',2,'YG12XAB',2),(5,12,5000,NULL,'Fined for no insurance','2017-07-22 21:56:13',1,'NT85ZBA',1),(6,20,NULL,NULL,'Warned for broken headlight','2016-01-02 21:56:13',2,'YG08XAB',NULL),(8,12,5000,3,'No insurance and given points','2017-05-08 06:21:03',3,'NT10XAA',1),(9,12,30,NULL,'Broken headlight','2018-09-08 06:21:03',1,'NT85ZBA',2),(10,1,60,3,'Speeding','2019-10-06 07:52:00',2,'YG08XAB',3);
105/*!40000 ALTER TABLE `report` ENABLE KEYS */;
106UNLOCK TABLES;
107
108--
109-- Table structure for table `vehicle`
110--
111
112DROP TABLE IF EXISTS `vehicle`;
113/*!40101 SET @saved_cs_client = @@character_set_client */;
114/*!50503 SET character_set_client = utf8mb4 */;
115CREATE TABLE `vehicle` (
116 `veReg` varchar(7) NOT NULL,
117 `veMake` varchar(30) NOT NULL,
118 `veModel` varchar(30) NOT NULL,
119 `veCol` varchar(30) NOT NULL,
120 `veOwnrID` int(11) DEFAULT NULL,
121 PRIMARY KEY (`veReg`),
122 KEY `fk_ve` (`veOwnrID`),
123 CONSTRAINT `fk_ve` FOREIGN KEY (`veOwnrID`) REFERENCES `person` (`peID`)
124) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
125/*!40101 SET character_set_client = @saved_cs_client */;
126
127--
128-- Dumping data for table `vehicle`
129--
130
131LOCK TABLES `vehicle` WRITE;
132/*!40000 ALTER TABLE `vehicle` DISABLE KEYS */;
133INSERT INTO `vehicle` VALUES ('NT10XAA','Audi','A1','Black',3),('NT85ZBA','Mini','Cooper','Red',NULL),('YG08XAB','Ford','Mustang','Red',2),('YG12XAB','Ford','Fiesta','Dark Blue',2);
134/*!40000 ALTER TABLE `vehicle` ENABLE KEYS */;
135UNLOCK TABLES;
136/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
137
138/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
139/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
140/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
141/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
142/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
143/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
144/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
145
146-- Dump completed on 2019-12-09 12:22:56