Παιδιά σε μία προσπάθεια για το 3ο θέμα του 18 έκανα αυτά:
CREATE TABLE Author( authorID INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY, authorName VARCHAR2(40) NOT NULL, nationality VARCHAR2(30) NOT NULL, dateOfBirth DATE NOT NULL, CONSTRAINT pk_author PRIMARY KEY(authorID), UNIQUE (authorName, dateOfBirth) );
CREATE TABLE Article ( articleID INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY, articleTitle VARCHAR2(40) NOT NULL, submissionDate DATE NOT NULL, scientificField VARCHAR(30) NOT NULL, CONSTRAINT pk_article PRIMARY KEY(articleID), UNIQUE (articleTitle, submissionDate) );
CREATE TABLE Reviewer ( reviewerID INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY, reviewerName VARCHAR2(40) NOT NULL, nationality VARCHAR2(30) NOT NULL, dateOfBirth DATE NOT NULL, emailAdress VARCHAR2(30) NOT NULL, CONSTRAINT pk_reviewer PRIMARY KEY(reviewerID), UNIQUE (emailAdress ) );
CREATE TABLE AuthorArticle ( author INTEGER NOT NULL, article INTEGER NOT NULL, accepted VARCHAR2(6) NOT NULL CONSTRAINT values_accepted CHECK(accepted = 'TRUE' or accepted = 'FALSE'), CONSTRAINT fk_AuthorArticle_article FOREIGN KEY(article) REFERENCES Article(articleID) ON DELETE CASCADE, CONSTRAINT fk_AuthorArticle_author FOREIGN KEY(author) REFERENCES Author(authorID) ON DELETE CASCADE, CONSTRAINT pk_authorArticle PRIMARY KEY (author, article) );
CREATE TABLE Review( reviewID INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY, reviewer INTEGER NOT NULL, article INTEGER NOT NULL, dateOfReview DATE NOT NULL , rating NUMBER NOT NULL CONSTRAINT rating_values CHECK(rating>0 and rating<=10), reviewText VARCHAR2(100) NOT NULL, CONSTRAINT fk_Review_article FOREIGN KEY(article) REFERENCES Article(articleID) ON DELETE CASCADE, CONSTRAINT fk_Review_reviewer FOREIGN KEY (reviewer) REFERENCES Reviewer(reviewerID) ON DELETE CASCADE, CONSTRAINT pk_review PRIMARY KEY(reviewID) );
CREATE ROLE authorsManager; CREATE ROLE reviewersManager;
CREATE VIEW view1 AS SELECT Author.authorName AS nameOfAuthor , Article.articleTitle AS TitleOfArticle FROM Author INNER JOIN AuthorArticle ON AuthorArticle.author = Author.authorID INNER JOIN Article ON AuthorArticle.article = Article.articleID ;
CREATE VIEW view2 AS SELECT Reviewer.reviewerName AS nameOfReviewer , Article.articleTitle AS TitleOfArticle, Review.reviewtext AS review FROM Reviewer INNER JOIN Review ON Review.reviewer = Reviewer.reviewerID INNER JOIN Article ON Review.article = Article.articleID ;
GRANT SELECT, UPDATE, INSERT, DELETE ON view1 TO authorsManager; /* ORACLE does not support ONLY the on update statement */ GRANT SELECT, UPDATE, INSERT, DELETE ON Author TO authorsManager; GRANT SELECT, UPDATE, INSERT, DELETE ON Article TO authorsManager; GRANT SELECT, UPDATE, INSERT, DELETE ON AuthorArticle TO authorsManager;
GRANT SELECT, UPDATE, INSERT, DELETE ON view2 TO reviewersManager; /* ORACLE does not support ONLY the on update statement */ GRANT SELECT, UPDATE, INSERT, DELETE ON Reviewer TO reviewersManager; GRANT SELECT, UPDATE, INSERT, DELETE ON Article TO reviewersManager; GRANT SELECT, UPDATE, INSERT, DELETE ON Review TO reviewersManager;
CREATE USER username1 IDENTIFIED BY secret_password1; GRANT authorsManager TO username1
CREATE USER username2 IDENTIFIED BY secret_password2; GRANT reviewersManager TO username2
Βλέπετε λάθη;
|