Question

Design the database using the ER approach. Then using Java and SQL, implement the following functionality:

Implement a button called “Initialize Database”. When a user clicks it, all necessary tables will be created (or recreated) automatically, with each table be populated with at least 10 tuples so that each query below will return some results. All students should use the database name “sampledb”, username “john”, and password “pass1234”.

Implement a user registration and login interface so that only a registered user can login into the system.

Create the functionality to assign three reviewers to a paper.

Answer 1

CODE:

Create table paper
(
paperid integer,
title VARCHAR(50),
abstract VARCHAR(250),
pdf VARCHAR(100),
primary key (paperid)
)

Create table author
(
email VARCHAR(100),
name VARCHAR(50),
affiliation VARCHAR(100),
primary key(email)
)

CREATE table write
(
paperid integer,
email varchar(50),
order integer,
Primary key(paperid, email),
foreign key paperid references paper,
foreign key email references autor
)

create table pcmember
(
email VARCHAR(50),
name VARCHAR(20),
primary key (email)
)

create table review
(
reportid integer,
sdate DATE,
comment VARCHAR(250),
recommendation CHAR(1),
paperid integer,
email VARCHAR(100),
unique(paperid, email),
foreign key paperid references paper,
foreign key email references pcmember)

CREATE Assertion 3pc

CHECK NOT EXISTS
(
SELECT * FROM Papers P
WHERE 3 <>
(
SELECT COUNT(*)
FROM Review R
WHERE R.paperid = P.paperid
)
)

CREATE ASSERTION atmostfivepapers

CHECK NOT NOT EXISTS
(
SELECT * FROM pcmember P
WHERE 5 <
(
SELECT *
FROM review R
WHERE R.email = P.email
)
)