uni/COMP2400/createa3.sql

-- CREATE ROLES

--
-- ROLE: researcher
--

CREATE ROLE u4137129_researcher;

--
-- ROLE: administrator
--

CREATE ROLE u4137129_administrator;

--
-- ROLE: editor
--

CREATE ROLE u4137129_editor;

GRANT u4137129_researcher TO Anderson;
GRANT u4137129_researcher TO Bellson;
GRANT u4137129_researcher TO Carlson;

GRANT u4137129_administrator TO Antar01;
GRANT u4137129_administrator TO Timbu01;

GRANT u4137129_editor TO AvianEd;
GRANT u4137129_editor TO BirdsEd;

-- CREATE VIEWS

-- Views for the public

CREATE VIEW Books (ISBN, Title, AuthorName, Publisher, Year, Edition)
AS SELECT b.ISBN, p.Title, a.RName, b.Publisher, p.Year, b.Edition
FROM b_publication p JOIN b_book b ON p.pid = b.pid JOIN b_authorship a ON
p.pid = a.pid;

CREATE VIEW JournalArticles (Title, AuthorName, JournalName, Year, Volume,
Page)
AS SELECT p.Title, au.RName, a.JournalName, p.Year, a.Volume, a.PageNumber
FROM b_publication p JOIN b_article a ON p.pid = a.pid JOIN b_authorship au
ON p.pid = au.pid;

-- Views for researchers
-- "Researchers write journal articles, books and monographs and are
-- interested in locating relevant publications"

-- Researchers aren't granted anything more than read access for these two views

CREATE VIEW publications_by_author
AS SELECT p.pid, title, year, kind, rname
FROM b_publication p JOIN b_authorship a ON p.pid = a.pid;

CREATE VIEW articles_by_keyword
AS SELECT a.pid, journalname, volume, pagenumber, submissiondate, keyword
FROM b_article a JOIN b_articlekeywords k ON a.pid = k.pid;

-- Views for administrators

-- This view should only show researchers from the administrator's
-- institution, and with the check option, only allow modifications that meet
-- this view's description

-- I initially had the WITH CHECK OPTION on this view, however it presented
-- chicken-and-egg scenario, where an administrator could not add a new
-- publication, as it failed the check option, and it wasn't possible to
-- satisfy the check option without violating a foreign key constraint. 
-- As it happens, the foreign key constraints pretty much ensure that a
-- rogue administrator from another institution cannot go deleting another
-- institution's publications (not that they can see them anyway)

CREATE VIEW publication_by_institution (PId, Title, Year, Kind)
AS SELECT PId, Title, Year, Kind
FROM b_Publication
WHERE PId IN (SELECT p.PId 
                FROM b_Publication p JOIN b_authorship a ON p.PId =
                a.PId JOIN b_researcher r ON a.Rname = r.Rname 
                WHERE UPPER(IName) LIKE UPPER(SUBSTR(USER, 1, 5)) || '%');

-- This view is for allowing administrators to add monographs (for their own
-- institution)

CREATE VIEW monograph_by_institution (PId, IName, CatalogNo)
AS SELECT PId, IName, CatalogNo
FROM b_monograph
WHERE UPPER(IName) LIKE UPPER(SUBSTR(USER, 1, 5)) || '%'
WITH CHECK OPTION;

-- This view is for allowing administrators to add researchers (for their
-- own institution)

CREATE VIEW researcher_by_institution
AS SELECT RName, Email, IName
FROM b_researcher
WHERE UPPER(IName) LIKE UPPER(SUBSTR(USER, 1, 5)) || '%'
WITH CHECK OPTION;

-- This view is for allowing administrators to add authors for publications
-- (only for publications authored by a researcher from their institution)

CREATE VIEW author_by_institution (PId, RName)
AS SELECT PId, RName
FROM b_authorship
WHERE RName IN (SELECT RName
                FROM b_researcher
                WHERE UPPER(IName) LIKE UPPER(SUBSTR(USER, 1, 5)) || '%')
WITH CHECK OPTION;

-- This is a read-only (in more ways than one) view for showing some summary
-- information

CREATE VIEW institution_publications (Institution, Publications)
AS SELECT Iname, COUNT(Iname)
FROM b_publication p, b_researcher r, b_authorship a
WHERE p.pid = a.pid AND a.rname = r.rname
GROUP BY Iname
ORDER BY 2 DESC;

-- Views for journal editors

-- This view allows a journal editor to add or modify information (for the
-- journal they edit)

CREATE VIEW editor_journal (JournalName, Frequency, AnnualPrice, Editor)
AS SELECT JournalName, Frequency, AnnualPrice, Editor
FROM b_journal
WHERE UPPER(JournalName) LIKE UPPER(SUBSTR(USER, 1, 5)) || '%'
WITH CHECK OPTION;

-- This view allows a journal editor to add or modify subscription records
-- for their journal

CREATE VIEW editor_journalsubscription (RName, JournalName, TerminationDate)
AS SELECT Rname, JournalName, TerminationDate
FROM b_journalsubscription
WHERE UPPER(JournalName) LIKE UPPER(SUBSTR(USER, 1, 5)) || '%'
WITH CHECK OPTION;

-- This view allows a journal editor to add or modify articles for their
-- journal

CREATE VIEW editor_article (PId, JournalName, Volume, PageNumber, SubmissionDate)
AS SELECT PId, JournalName, Volume, PageNumber, SubmissionDate
FROM b_article
WHERE UPPER(JournalName) LIKE UPPER(SUBSTR(USER, 1, 5)) || '%'
WITH CHECK OPTION;

-- As articles reference the publications table, journal editors also need
-- to be able to add records to this table
-- ASSUMPTION: The publication title will equal the name of the journal

CREATE VIEW editor_publication (PId, Title, Year, Kind)
AS SELECT PId, Title, Year, Kind
FROM b_publication
WHERE Title = (SELECT JournalName
                FROM b_journal
                WHERE UPPER(JournalName) LIKE UPPER(SUBSTR(USER, 1, 5)) || '%')
WITH CHECK OPTION;

-- As publications have authors, the journal editor will need to be able to
-- add and modify records to this table

CREATE VIEW editor_authorship (PId, RName)
AS SELECT PId, RName
FROM b_authorship
WHERE PId IN (SELECT PId
                FROM editor_publication)
WITH CHECK OPTION;

-- This view allows a journal editor to add or modify keywords for articles
-- of journals they edit

CREATE VIEW editor_articlekeywords (PId, Keyword)
AS SELECT PId, Keyword
FROM b_articlekeywords
WHERE PId IN (SELECT PId FROM editor_article)
WITH CHECK OPTION;

-- This read-only view produces a subscription mailing list for the journal
-- they edit

CREATE VIEW editor_mailinglist (RName, IName, IAddress)
AS SELECT r.RName, IName, IAddress
FROM b_JournalSubscription j JOIN b_Researcher r ON j.RName = r.RName JOIN
b_Institution i ON r.IName = i.IName
WHERE UPPER(JournalName) LIKE UPPER(SUBSTR(USER, 1, 5)) || '%';

-- This read-only view produces a summary of all journal subscriber numbers

CREATE VIEW editor_subscriptions (JournalName, Subscribers)
AS SELECT JournalName, COUNT(JournalName)
FROM b_JournalSubscription
GROUP BY JournalName
ORDER BY 2 DESC;

-- This read-only view produces a journal table of contents

CREATE VIEW article_toc (JournalName, Volume, Title, PageNumber)
AS SELECT JournalName, Volume, Title, PageNumber
FROM b_publication p JOIN b_article a ON p.PId = a.PId
WHERE UPPER(JournalName) LIKE UPPER(SUBSTR(USER, 1, 5)) || '%'
ORDER BY JournalName, Volume, PageNumber;

-- This read-only view produces article keywords

CREATE VIEW article_keywords (JournalName, Volume, Title, Keyword)
AS SELECT JournalName, Volume, Title, Keyword
FROM b_publication p JOIN b_article a ON p.PId = a.PId JOIN
b_articlekeywords ak ON a.PId = ak.PId
WHERE UPPER(JournalName) LIKE UPPER(SUBSTR(USER, 1, 5)) || '%'
ORDER BY JournalName, Volume, Keyword;

-- GRANT PERMISSIONS

--
-- Access for the general public;
--

GRANT SELECT ON Books TO PUBLIC;
GRANT SELECT ON JournalArticles TO PUBLIC;

--
-- Access for researchers
--

GRANT SELECT ON publications_by_author TO u4137129_researcher;
GRANT SELECT ON articles_by_keyword TO u4137129_researcher;

--
-- Access for administrators
--

GRANT SELECT, UPDATE, DELETE, INSERT ON publication_by_institution TO
u4137129_administrator;
GRANT SELECT, UPDATE, DELETE, INSERT ON monograph_by_institution TO
u4137129_administrator;
GRANT SELECT, UPDATE, DELETE, INSERT ON researcher_by_institution TO
u4137129_administrator;
GRANT SELECT, UPDATE, DELETE, INSERT ON author_by_institution TO
u4137129_administrator;
GRANT SELECT ON institution_publications TO u4137129_administrator;

--
-- Access for editors
--

GRANT SELECT, UPDATE, DELETE, INSERT ON editor_journal TO u4137129_editor;
GRANT SELECT, UPDATE, DELETE, INSERT ON editor_journalsubscription TO u4137129_editor;
GRANT SELECT, UPDATE, DELETE, INSERT ON editor_article TO u4137129_editor;
GRANT SELECT, UPDATE, DELETE, INSERT ON editor_articlekeywords TO u4137129_editor;
GRANT SELECT, UPDATE, DELETE, INSERT ON editor_publication TO u4137129_editor;
GRANT SELECT, UPDATE, DELETE, INSERT ON editor_authorship TO u4137129_editor;
GRANT SELECT ON editor_mailinglist TO u4137129_editor;
GRANT SELECT ON editor_subscriptions TO u4137129_editor;
GRANT SELECT ON article_toc TO u4137129_editor;
GRANT SELECT ON article_keywords TO u4137129_editor;
1	-- CREATE ROLES
2
3	--
4	-- ROLE: researcher
5	--
6
7	CREATE ROLE u4137129_researcher;
8
9	--
10	-- ROLE: administrator
11	--
12
13	CREATE ROLE u4137129_administrator;
14
15	--
16	-- ROLE: editor
17	--
18
19	CREATE ROLE u4137129_editor;
20
21	GRANT u4137129_researcher TO Anderson;
22	GRANT u4137129_researcher TO Bellson;
23	GRANT u4137129_researcher TO Carlson;
24
25	GRANT u4137129_administrator TO Antar01;
26	GRANT u4137129_administrator TO Timbu01;
27
28	GRANT u4137129_editor TO AvianEd;
29	GRANT u4137129_editor TO BirdsEd;
30
31	-- CREATE VIEWS
32
33	-- Views for the public
34
35	CREATE VIEW Books (ISBN, Title, AuthorName, Publisher, Year, Edition)
36	AS SELECT b.ISBN, p.Title, a.RName, b.Publisher, p.Year, b.Edition
37	FROM b_publication p JOIN b_book b ON p.pid = b.pid JOIN b_authorship a ON
38	p.pid = a.pid;
39
40	CREATE VIEW JournalArticles (Title, AuthorName, JournalName, Year, Volume,
41	Page)
42	AS SELECT p.Title, au.RName, a.JournalName, p.Year, a.Volume, a.PageNumber
43	FROM b_publication p JOIN b_article a ON p.pid = a.pid JOIN b_authorship au
44	ON p.pid = au.pid;
45
46	-- Views for researchers
47	-- "Researchers write journal articles, books and monographs and are
48	-- interested in locating relevant publications"
49
50	-- Researchers aren't granted anything more than read access for these two views
51
52	CREATE VIEW publications_by_author
53	AS SELECT p.pid, title, year, kind, rname
54	FROM b_publication p JOIN b_authorship a ON p.pid = a.pid;
55
56	CREATE VIEW articles_by_keyword
57	AS SELECT a.pid, journalname, volume, pagenumber, submissiondate, keyword
58	FROM b_article a JOIN b_articlekeywords k ON a.pid = k.pid;
59
60	-- Views for administrators
61
62	-- This view should only show researchers from the administrator's
63	-- institution, and with the check option, only allow modifications that meet
64	-- this view's description
65
66	-- I initially had the WITH CHECK OPTION on this view, however it presented
67	-- chicken-and-egg scenario, where an administrator could not add a new
68	-- publication, as it failed the check option, and it wasn't possible to
69	-- satisfy the check option without violating a foreign key constraint.
70	-- As it happens, the foreign key constraints pretty much ensure that a
71	-- rogue administrator from another institution cannot go deleting another
72	-- institution's publications (not that they can see them anyway)
73
74	CREATE VIEW publication_by_institution (PId, Title, Year, Kind)
75	AS SELECT PId, Title, Year, Kind
76	FROM b_Publication
77	WHERE PId IN (SELECT p.PId
78	FROM b_Publication p JOIN b_authorship a ON p.PId =
79	a.PId JOIN b_researcher r ON a.Rname = r.Rname
80	WHERE UPPER(IName) LIKE UPPER(SUBSTR(USER, 1, 5)) \|\| '%');
81
82	-- This view is for allowing administrators to add monographs (for their own
83	-- institution)
84
85	CREATE VIEW monograph_by_institution (PId, IName, CatalogNo)
86	AS SELECT PId, IName, CatalogNo
87	FROM b_monograph
88	WHERE UPPER(IName) LIKE UPPER(SUBSTR(USER, 1, 5)) \|\| '%'
89	WITH CHECK OPTION;
90
91	-- This view is for allowing administrators to add researchers (for their
92	-- own institution)
93
94	CREATE VIEW researcher_by_institution
95	AS SELECT RName, Email, IName
96	FROM b_researcher
97	WHERE UPPER(IName) LIKE UPPER(SUBSTR(USER, 1, 5)) \|\| '%'
98	WITH CHECK OPTION;
99
100	-- This view is for allowing administrators to add authors for publications
101	-- (only for publications authored by a researcher from their institution)
102
103	CREATE VIEW author_by_institution (PId, RName)
104	AS SELECT PId, RName
105	FROM b_authorship
106	WHERE RName IN (SELECT RName
107	FROM b_researcher
108	WHERE UPPER(IName) LIKE UPPER(SUBSTR(USER, 1, 5)) \|\| '%')
109	WITH CHECK OPTION;
110
111	-- This is a read-only (in more ways than one) view for showing some summary
112	-- information
113
114	CREATE VIEW institution_publications (Institution, Publications)
115	AS SELECT Iname, COUNT(Iname)
116	FROM b_publication p, b_researcher r, b_authorship a
117	WHERE p.pid = a.pid AND a.rname = r.rname
118	GROUP BY Iname
119	ORDER BY 2 DESC;
120
121	-- Views for journal editors
122
123	-- This view allows a journal editor to add or modify information (for the
124	-- journal they edit)
125
126	CREATE VIEW editor_journal (JournalName, Frequency, AnnualPrice, Editor)
127	AS SELECT JournalName, Frequency, AnnualPrice, Editor
128	FROM b_journal
129	WHERE UPPER(JournalName) LIKE UPPER(SUBSTR(USER, 1, 5)) \|\| '%'
130	WITH CHECK OPTION;
131
132	-- This view allows a journal editor to add or modify subscription records
133	-- for their journal
134
135	CREATE VIEW editor_journalsubscription (RName, JournalName, TerminationDate)
136	AS SELECT Rname, JournalName, TerminationDate
137	FROM b_journalsubscription
138	WHERE UPPER(JournalName) LIKE UPPER(SUBSTR(USER, 1, 5)) \|\| '%'
139	WITH CHECK OPTION;
140
141	-- This view allows a journal editor to add or modify articles for their
142	-- journal
143
144	CREATE VIEW editor_article (PId, JournalName, Volume, PageNumber, SubmissionDate)
145	AS SELECT PId, JournalName, Volume, PageNumber, SubmissionDate
146	FROM b_article
147	WHERE UPPER(JournalName) LIKE UPPER(SUBSTR(USER, 1, 5)) \|\| '%'
148	WITH CHECK OPTION;
149
150	-- As articles reference the publications table, journal editors also need
151	-- to be able to add records to this table
152	-- ASSUMPTION: The publication title will equal the name of the journal
153
154	CREATE VIEW editor_publication (PId, Title, Year, Kind)
155	AS SELECT PId, Title, Year, Kind
156	FROM b_publication
157	WHERE Title = (SELECT JournalName
158	FROM b_journal
159	WHERE UPPER(JournalName) LIKE UPPER(SUBSTR(USER, 1, 5)) \|\| '%')
160	WITH CHECK OPTION;
161
162	-- As publications have authors, the journal editor will need to be able to
163	-- add and modify records to this table
164
165	CREATE VIEW editor_authorship (PId, RName)
166	AS SELECT PId, RName
167	FROM b_authorship
168	WHERE PId IN (SELECT PId
169	FROM editor_publication)
170	WITH CHECK OPTION;
171
172	-- This view allows a journal editor to add or modify keywords for articles
173	-- of journals they edit
174
175	CREATE VIEW editor_articlekeywords (PId, Keyword)
176	AS SELECT PId, Keyword
177	FROM b_articlekeywords
178	WHERE PId IN (SELECT PId FROM editor_article)
179	WITH CHECK OPTION;
180
181	-- This read-only view produces a subscription mailing list for the journal
182	-- they edit
183
184	CREATE VIEW editor_mailinglist (RName, IName, IAddress)
185	AS SELECT r.RName, IName, IAddress
186	FROM b_JournalSubscription j JOIN b_Researcher r ON j.RName = r.RName JOIN
187	b_Institution i ON r.IName = i.IName
188	WHERE UPPER(JournalName) LIKE UPPER(SUBSTR(USER, 1, 5)) \|\| '%';
189
190	-- This read-only view produces a summary of all journal subscriber numbers
191
192	CREATE VIEW editor_subscriptions (JournalName, Subscribers)
193	AS SELECT JournalName, COUNT(JournalName)
194	FROM b_JournalSubscription
195	GROUP BY JournalName
196	ORDER BY 2 DESC;
197
198	-- This read-only view produces a journal table of contents
199
200	CREATE VIEW article_toc (JournalName, Volume, Title, PageNumber)
201	AS SELECT JournalName, Volume, Title, PageNumber
202	FROM b_publication p JOIN b_article a ON p.PId = a.PId
203	WHERE UPPER(JournalName) LIKE UPPER(SUBSTR(USER, 1, 5)) \|\| '%'
204	ORDER BY JournalName, Volume, PageNumber;
205
206	-- This read-only view produces article keywords
207
208	CREATE VIEW article_keywords (JournalName, Volume, Title, Keyword)
209	AS SELECT JournalName, Volume, Title, Keyword
210	FROM b_publication p JOIN b_article a ON p.PId = a.PId JOIN
211	b_articlekeywords ak ON a.PId = ak.PId
212	WHERE UPPER(JournalName) LIKE UPPER(SUBSTR(USER, 1, 5)) \|\| '%'
213	ORDER BY JournalName, Volume, Keyword;
214
215	-- GRANT PERMISSIONS
216
217	--
218	-- Access for the general public;
219	--
220
221	GRANT SELECT ON Books TO PUBLIC;
222	GRANT SELECT ON JournalArticles TO PUBLIC;
223
224	--
225	-- Access for researchers
226	--
227
228	GRANT SELECT ON publications_by_author TO u4137129_researcher;
229	GRANT SELECT ON articles_by_keyword TO u4137129_researcher;
230
231	--
232	-- Access for administrators
233	--
234
235	GRANT SELECT, UPDATE, DELETE, INSERT ON publication_by_institution TO
236	u4137129_administrator;
237	GRANT SELECT, UPDATE, DELETE, INSERT ON monograph_by_institution TO
238	u4137129_administrator;
239	GRANT SELECT, UPDATE, DELETE, INSERT ON researcher_by_institution TO
240	u4137129_administrator;
241	GRANT SELECT, UPDATE, DELETE, INSERT ON author_by_institution TO
242	u4137129_administrator;
243	GRANT SELECT ON institution_publications TO u4137129_administrator;
244
245	--
246	-- Access for editors
247	--
248
249	GRANT SELECT, UPDATE, DELETE, INSERT ON editor_journal TO u4137129_editor;
250	GRANT SELECT, UPDATE, DELETE, INSERT ON editor_journalsubscription TO u4137129_editor;
251	GRANT SELECT, UPDATE, DELETE, INSERT ON editor_article TO u4137129_editor;
252	GRANT SELECT, UPDATE, DELETE, INSERT ON editor_articlekeywords TO u4137129_editor;
253	GRANT SELECT, UPDATE, DELETE, INSERT ON editor_publication TO u4137129_editor;
254	GRANT SELECT, UPDATE, DELETE, INSERT ON editor_authorship TO u4137129_editor;
255	GRANT SELECT ON editor_mailinglist TO u4137129_editor;
256	GRANT SELECT ON editor_subscriptions TO u4137129_editor;
257	GRANT SELECT ON article_toc TO u4137129_editor;
258	GRANT SELECT ON article_keywords TO u4137129_editor;