From 3b7cc65e3fe5e66dcf77bee4045d9f5c7ce0f846 Mon Sep 17 00:00:00 2001
From: mo khan <mo@mokhan.ca>
Date: Mon, 13 Jan 2025 22:47:09 -0700
Subject: Split sql files

---
 projects/3/ddl.sql | 43 +++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 43 insertions(+)
 create mode 100644 projects/3/ddl.sql

(limited to 'projects/3/ddl.sql')

diff --git a/projects/3/ddl.sql b/projects/3/ddl.sql
new file mode 100644
index 0000000..4a264cf
--- /dev/null
+++ b/projects/3/ddl.sql
@@ -0,0 +1,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
-- 
cgit v1.2.3