1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
create table categories(
id integer primary key asc,
title varchar(16) unique not null,
description varchar(255) not null,
created_at datetime default current_timestamp,
updated_at datetime default current_timestamp
);
create table customers(
id integer primary key asc,
first_name varchar(255) not null,
last_name varchar(255) not null,
address text,
created_at datetime default current_timestamp,
updated_at datetime default current_timestamp
);
create table media(
id integer primary key asc,
title varchar(255) unique not null,
m_type varchar(255) check(m_type in ('blu-ray', 'dvd', 'game')) not null default('dvd'),
category_id integer not null,
created_at datetime default current_timestamp,
updated_at datetime default current_timestamp,
foreign key(category_id) references categories(id)
);
create table rentals(
id integer primary key asc,
rented_at datetime not null,
due_at datetime not null,
customer_id integer not null,
media_id integer not null,
created_at datetime default current_timestamp,
updated_at datetime default current_timestamp,
foreign key(media_id) references media(id),
foreign key(customer_id) references customers(id)
);
create index index_media_on_category_id on media(category_id);
create index index_rentals_on_customer_id_and_media_id on rentals(customer_id, media_id);
.schema
|
