-- 図書館システムデータベースの定義

-- ユーザーの定義
CREATE USER student PASSWORD 'student'

-- データベースの定義
CREATE DATABASE library;
\c library

-- 蔵書表の定義
CREATE TABLE book (
    no SERIAL PRIMARY KEY, 
    name TEXT NOT NULL, 
    author TEXT NOT NULL, 
    status INT NOT NULL DEFAULT 0 
              CHECK(status IN (0, 1, 2))
);

-- 利用者表の定義
CREATE TABLE member (
    no SERIAL PRIMARY KEY, 
    name TEXT NOT NULL
);

-- 貸出表の定義
CREATE TABLE bookrecord (
    memberno INT NOT NULL REFERENCES member, 
    bookno INT NOT NULL REFERENCES book, 
    issuedate DATE NOT NULL, 
    duedate DATE NOT NULL, 
    returndate DATE, 
    PRIMARY KEY(memberno, bookno, issuedate)
);