Schema Script File

The following script file creates all tables, including PRIMARY KEY, UNIQUE, and NOT NULL constraints, and also assigns FOREIGN KEY constraints.

/*------------------------------------------------------------------------------
-- Drop Section 
-- Necessary to be able to run the script multiple times
------------------------------------------------------------------------------*/

/* "DROP" Foreign Key Constraints --------------------------------------------*/

ALTER   TABLE Borrow_T
DROP    CONSTRAINT fk_borrow_condition;

ALTER   TABLE Borrow_T
DROP    CONSTRAINT fk_borrow_item;

ALTER   TABLE Borrow_T
DROP    CONSTRAINT fk_borrow_swapper;

ALTER   TABLE Item_T
DROP    CONSTRAINT fk_item_category;

ALTER   TABLE Item_T
DROP    CONSTRAINT fk_item_swapper;

ALTER   TABLE Group_Membership_T
DROP    CONSTRAINT fk_group_membership_swapper;

ALTER   TABLE Group_Membership_T
DROP    CONSTRAINT fk_group_membership_swapper_group;

/* DROP Tables ---------------------------------------------------------------*/

DROP TABLE Borrow_T;
DROP TABLE Category_T;
DROP TABLE Condition_T;
DROP TABLE Group_Membership_T;
DROP TABLE Item_T;
DROP TABLE Swapper_T;
DROP TABLE Swapper_Group_T;

/*------------------------------------------------------------------------------
-- Create Section 
------------------------------------------------------------------------------*/

/* CREATE Tables -------------------------------------------------------------*/

CREATE  TABLE Borrow_T (
        ItemID INTEGER NOT NULL,
        BorrowerID INTEGER NOT NULL,
        RequestDate DATE NOT NULL,
        LoanDate DATE NULL,
        ReturnDate DATE NULL,
        ReturnCondition VARCHAR(100) NULL,
        
        CONSTRAINT pk_borrow PRIMARY KEY (ItemID, BorrowerID, RequestDate)
        );

CREATE  TABLE Category_T (
        MediaCategory VARCHAR(100) NOT NULL,
        
        CONSTRAINT pk_category PRIMARY KEY (MediaCategory)
        );

CREATE  TABLE Condition_T (
        RetCondition VARCHAR(100) NOT NULL,
        Points INTEGER NOT NULL,
        
        CONSTRAINT pk_condition PRIMARY KEY (RetCondition)
        );

CREATE  TABLE Group_Membership_T (
        GroupName VARCHAR(100) NOT NULL,
        SwapperID INTEGER NOT NULL,
        JoinDate DATE NOT NULL,
        
        CONSTRAINT pk_group_membership PRIMARY KEY (GroupName, SwapperID)
        );

CREATE  TABLE Item_T (
        ID INTEGER NOT NULL,
        Title VARCHAR(100) NOT NULL,
        Description VARCHAR(1000) NOT NULL,
        MediaID VARCHAR(50) NULL,
        MustReturn BOOLEAN NOT NULL,
        AvailableDate DATE NOT NULL,
        UnavailableDate DATE NULL,
        OwnerID INTEGER NOT NULL,
        Category VARCHAR(100) NOT NULL,
        
        CONSTRAINT pk_item PRIMARY KEY (ID)
        );
        
CREATE  TABLE Swapper_T (
        ID INTEGER NOT NULL,
	Email VARCHAR(100) NOT NULL,
        Name VARCHAR(100) NOT NULL,
        Password VARCHAR(100) NOT NULL,
        
        CONSTRAINT uk_swapper UNIQUE (email),
        CONSTRAINT pk_swapper PRIMARY KEY (ID)
        );
        
CREATE  TABLE Swapper_Group_T (
        Name VARCHAR(100) NOT NULL,
        IsInvitationOnly BOOLEAN NOT NULL,
        
        CONSTRAINT pk_swapper_group PRIMARY KEY (Name)
        );

/* "CREATE" Foreign Key Constraints ------------------------------------------*/

ALTER   TABLE Borrow_T
ADD     CONSTRAINT fk_borrow_condition
        FOREIGN KEY (ReturnCondition)
        REFERENCES Condition_T (RetCondition);

ALTER   TABLE Borrow_T
ADD     CONSTRAINT fk_borrow_item
        FOREIGN KEY (ItemID)
        REFERENCES Item_T(ID);

ALTER   TABLE Borrow_T
ADD     CONSTRAINT fk_borrow_swapper
        FOREIGN KEY (BorrowerID)
        REFERENCES Swapper_T (ID);

ALTER   TABLE Item_T
ADD     CONSTRAINT fk_item_category
        FOREIGN KEY (Category)
        REFERENCES Category_T (MediaCategory);

ALTER   TABLE Item_T
ADD     CONSTRAINT fk_item_swapper
        FOREIGN KEY (OwnerID)
        REFERENCES Swapper_T(ID);

ALTER   TABLE Group_Membership_T
ADD     CONSTRAINT fk_group_membership_swapper_group
        FOREIGN KEY (GroupName)
        REFERENCES Swapper_Group_T (Name);

ALTER   TABLE Group_Membership_T
ADD     CONSTRAINT fk_group_membership_swapper
        FOREIGN KEY (SwapperID)
        REFERENCES Swapper_T(ID);

Changes from the Previous Deliverable

No changes were made to either of the Entity-Relationship Model or Proposal documents.