kSe5BX43

1create database if not exists pet_db;
2
3use pet_db;
4
5drop table if exists pets;
6drop table if exists pet_types;
7drop table if exists breeds;
8drop table if exists costumes;
9
10create table costumes (
11    costume_id int(11) not null auto_increment,
12    costume_name varchar(30),
13    costume_desc varchar(120),
14    primary key (costume_id)
15);
16
17create table pets (
18    pet_id int(11) not null auto_increment,
19    pet_type_id int(11),
20    pet_breed_id int(11),
21    pet_costume_id int(11),
22    pet_name varchar(30),
23    pet_gender char(1),
24    pet_birthday date,
25    primary key (pet_id),
26    foreign key (pet_costume_id) references costumes(costume_id)
27);
28
29create table pet_types (
30    pet_type_id int(11) not null auto_increment,
31    pet_breed_id int(11),
32    pet_type_name varchar(30),
33    primary key (pet_type_id)
34);
35
36create table breeds (
37    pet_breed_id int(11) not null auto_increment,
38    pet_type_id int(11),
39    pet_breed_name varchar(30),
40    pet_min_lifespan int(11),
41    pet_max_lifespan int(11),
42    primary key (pet_breed_id),
43    foreign key (pet_type_id) references pet_types(pet_type_id)
44);