Database opdracht 1

Flamefever · updated April 02, 2021 · SQL
-- create a table
CREATE TABLE Author (
a_id INTEGER PRIMARY KEY,
a_name TEXT,
a_age INTEGER
);

CREATE TABLE Document (
d_id INTEGER PRIMARY KEY,
DOI TEXT
);

CREATE TABLE owns (
d_id INTEGER,
a_id INTEGER,
PRIMARY KEY (d_id, a_id),
FOREIGN KEY (a_id) references Author,
FOREIGN KEY (d_id) references Document
);
  
CREATE TABLE Patent (
d_id INTEGER PRIMARY KEY,
type TEXT,
FOREIGN KEY (d_id) references Document ON DELETE CASCADE
);

CREATE TABLE Scientific_Article (
nr_references TEXT,
published_year INTEGER,
d_id INTEGER,
j_id INTEGER,
PRIMARY KEY (j_id, d_id)
FOREIGN KEY (j_id) references Journal,
FOREIGN KEY (d_id) references Document ON DELETE CASCADE
);

CREATE TABLE Journal (
j_id INTEGER PRIMARY KEY,
j_name TEXT,
field_of_study TEXT
);  

CREATE TABLE Peer_review (
p_name TEXT,
p_job TEXT, 
j_id INTEGER,
PRIMARY KEY (j_id, p_name),
FOREIGN KEY (j_id) references Journal ON DELETE CASCADE
);

Comments

Please sign up or log in to contribute to the discussion.