/[cvs.andrew.net.au]/uni/COMP2400/createa3.sql
ViewVC logotype

Contents of /uni/COMP2400/createa3.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.26 - (show annotations)
Tue Oct 19 02:27:58 2004 UTC (17 years, 9 months ago) by apollock
Branch: MAIN
CVS Tags: HEAD
Changes since 1.25: +50 -4 lines
Added lots of pretty comments

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;

  ViewVC Help
Powered by ViewVC 1.1.22