Added library.sql

1 files changed, 29 insertions, 0 deletions

diff --git a/database/postgresql/02-library/library.sql b/database/postgresql/02-library/library.sql
new file mode 100644
index 0000000..b104fbe
--- /dev/null
+++ b/database/postgresql/02-library/library.sql
@@ -0,0 +1,29 @@
+drop table if exists borrowed_book;
+drop table if exists book;
+drop table if exists user_account;
+
+create table user_account (
+  uid serial primary key,
+  username text unique not null,
+  name text,
+  password text
+);
+
+insert into user_account (username, name) values ('kamal', 'Kamal Wickramanayake');
+insert into user_account (username, name) values ('tharindu', 'Tharindu Fernando');
+
+create table book (
+  bookid serial primary key,
+  name text,
+  author text
+);
+
+insert into book (name, author) values ('Tasty Cooking', 'Bhagya Ratnayake');
+insert into book (name, author) values ('Smiling Kitty', 'Seema Farwin');
+
+
+create table borrowed_book (
+  uid integer references user_account(uid) on delete restrict,
+  bookid integer references book(bookid) on delete restrict,
+  borrowed_time timestamp
+);