Museum database processing with ORACLE: updates, views, security, concurrency control

MIS 380_Fall 2020

Database Project   #4

 

Museum  database processing  with ORACLE:  updates, views, security, concurrency control

DO NOT FORGET TO RECORDYOUR WORK (either directly into a Word document, or into a spool file opened before  you start your oracle work).  Presentation: as project 3.  Special presentation for questions 9 and 10

Due December 18,  8PM (xtra credit +1), uploaded to blackboard as a Word document.  Accepted until Dec 20, 10PM.  Not accepted later. 

 

Students working as a team for the full project: have both names atop first page of the project.    Show both usernames. Both accounts must show the work.

 

 

Teams optional.

 

Questions 1 to 8: use same ORACLE username as in Project 3.  Questions 9 and 10: you need a second oracle account.  If you are not part of a team, or do not have another student to work with, please email me to get a second Oracle account.

 

GUIDELINES.  Complete each question below in the shown.  For each question, print the question number, show ALL the SQL commands used for the question (unless told otherwise).  Note that you need not complete each question to work on the following ones.

 

 

1. (2) The estimated value of paintings is updated as follows.

Paintings before 1800: increase by 50%

1800-1899 paintings: increase by 40%

1900 and after paintings: decrease by 20%

Show the update commands and the resulting Painting table

ROLLBACK – DO NOT COMMIT

2. (extra-credit, 1) perform the same changes of the estimated value of paintings as in question 1, using the update command with “case when” clause. (Look up the” update … case … when” on the Internet). Show the update commands and the resulting Painting table.   Do not COMMIT, perform a ROLLBACK when done

 

3. (2 ) List each “RM” customer (Custid, Custname) whose expertise does not include Chagall nor Da Vinci.

 

4. (2pts). Delete from the table PAINTING all paintings loaned to  museums located in France or Spain.  Show the delete command and list the resulting PAINTING table.  Do not COMMIT.  ROLLBACK when done. (Delete with nested select)

 

5. (3pts). Create a table IR_PAINTING consisting of the 4 attributes PaintName, EstValue, Year, Painter, using the usual CREATE TABLE command.  With an insert command similar to the one p. 116, populate the table IR_PAINTING with the paintings that have Painters whose Country is Netherlands or Italy. Show the create and insert commands and display the new table.  COMMIT.

 

6. (2 + extra-credit 2). The date for gala nights between jan 2020 and April 2020 are in error: you need to replace 2020 by 2021. You need to modify data in both GALA_NIGHT and INVITE tables, but you may run into referential integrity constraint errors (INVITE has a foreigh key referencing GALA_NIGHT). Explain how you proceed to avoid those errors.  Show all correct commands involved.  Display fully the new GALA_NIGHT and INVITE tables.  Commit;.

 

7. (extra-credit 2) VIP customers who have not been invited to the 30-apr-2021 Navet gala_night now get invited to it  (use 30-apr-2020 if you were not able to perform the previous question).  Show the insert command and the resultingINVITE table.

Note: similar to, but more complex than the insert p.116.  It is permitted to mix in the SELECT statement columns that consist of data values and attribute names and use DISTINCT

 

8. View processing

 

1. (4) Create a view PaintingView that shows all paintings loaned to Prado or Tate with their painters and the following attributes

From PAINTING: Painting_name, estvalue, Painter_Name, MuseumName

From Painter:  ABdate, Acountry

Show the Create View command.  Show the data in the view, by Painting_name

 

1. (2 extra credit) explain the type of association (1-1 or 1-M) between PaintingView and ARTIST, between PaintingView and PAINTING. (see p. 121)

 

c(2). “Waterlilies” is loaned to Tate.  Show the command(s) on the table(s) and display the resulting view. Explain. rollback

 

1. (2) “Village” is returned by Prado. Show the command(s) on the table(s) and display the resulting view.  Explain.  Rollback.

 

1. (3) The following painting: Tahiti, 1885, estvalue = 1000000, Painter Gauguin, 7 June 1848 – 8 May 1903, France (not shown in the current PAINTINGS table) is loaned to Tate. Perform all updates and inserts necessary on the relevant table(s) (show them).  List the resulting view.  Explain.  Rollback.

 

1. (2 extra credit) . The “Church” painting is returned by Tate.  What command would you use on the view? ((We still want to  have”Church”  in the Paitings table).  Explain.  Show the command, the view, and the involved row in the  table(s)

 

1. (1 extra credit) Try to change Picasso ABdate to 26-Oct-1881 directly on the view (show the SQL). Why does it fail?

 

9 .   GRANT command

Questions 9  and 10 require you to work with two ORACLE accounts.  I call them account1 and account2 here.   If you are not part of a team, or do not have another student to work with, please email me to get a second Oracle account..

Open two concurrent ORACLE sessions as follows:

Logon to edoras with account1.  SQLPLUS to account1.  Logon to edoras with account2. without closing the first session. SQLPLUS to account2.

You have now two ORACLE accounts in session.

 

(Students working together for questions 9 and 10 only: indicate both names aand usernames  here.  Both project reports must showthe work)

 

 

Presentation for questions 9 and 10.  Insert a table with 3 columns into your word document (careful: those are not Oracle tables, they are Word tables)_.  Column 1 is for account1, column 2 is for account2, column 3 is for your explanations.  Show content of  columns 1 and 2 in chronological (by shifting a column down if necessary)and explanations in the  row of what is taking place in Oracle accounts. 

 

 

1. (1) In account1 create a table Xpainting consisting of attributes Pname and Year (same data types as Painting_Name, Year_Painted  of  table  PAINTING)  and  the following 4 rows :

Penguin  1930

Kids    1910

Blue 1907

Chicken  1920

Execute COMMIT:

1. (1) Before executing a Grant command, try to access table Xpainting from account2.  (please review Koster, page 128 at the top, for the SQL syntax to access  a table that is in a different account).  Show the select command and explain.
2.  (4) Grant permission to account2 from account1 on the table Xpainting:

grant select, update(Pname), insert on Xpainting to account2; show the Grant command.

Show and  explain the following (permitted or not permitted) actions performed by account2 on Xpainting: SELECT –INSERT  –DELETE —  UPDATE Pname – UPDATE  Year

When done, Execute Rollback; in account2

 

 

 

 

 

10.. (2 +3 extra-credit)  Concurrent processing of tables.  .  .  No matter how much you show,, no credit will be given if you do not give  proper explanations as requested, and do not follow the proper guidelines for presenting the results of this question.  Please show all SQL commands you execute

. In account 1, execute  “GRANT all on XpaintingTo account2; (this gives permission to account2 to execute any legal SQLcommand on table Xpainting)

1. Illustrate row-level locking caused by an update (row-level locking is shown by being able to update an unlocked row (or insert one) and not being able to update a row that has been locked). Explain the various events occurring before and after COMMIT; executed in account1 in terms of transactions and their ACID properties.  When done, rollback in account2
2. show a deadlock. Indicate how ORACLE resolves it
3. show and explain row-level locking initiated by the command :SELECT … FOR UPDATE.. rollback; in both accounts
4. show and explain table-level locking (show an insert before and after the lock command).

Rollback; in both accounts

 

ere lock executed Concurrent processing of tables.   (2 + 2 extra-credit). To properly demonstrate things, sql that fails as well as sql that succeeds must be shown.

 

 

1. In account 1, execute “GRANT all on IR_PAINTING To account2;”.

Illustrate row-level locking caused by an update  (row-level locking is shown by being able to update an unlocked row (or insert one) and not being able to update a row that has been locked).  Explain the various events occurring before and after COMMIT; in terms of transactions and their ACID properties

1. show a deadlock. Indicate how ORACLE resolves it
2. show and explain row-level locking initiated by the command :SELECT … FOR UPDATE..
3. show and explain table-level locking. Explain the pros and cons of row-level locking vs table-level locking