Questions

1. The write ahead logging (WAL) protocol insists that, before writing a database object from memory to disk, its corresponding log records must be flushed to the disk first.
   1. Why is the order (log records first, database object next) so important?
   2. Should writing new log records to the log file (in memory presumably) be prohibited during the actions of flushing log records and flushing the database object? Justify your answer.
2. Suppose the DBA (database administrator) of the real estate company (used in Assignment 1 to 8) decided to create an Oracle account with the username projacc as a dedicated account to just handle the closing transaction activities described in Assignment 7. That is, the username projacc and its password will only be used to make connection to Oracle to execute the program implementing Assignment 7 specifications.
   Write SQL statements to grant minumum but sufficient privileges to the program's Oracle account projacc so that Assignment 7 program can be executed successfully.
3. Create a view called "NewlyListed" for the real estate company that shows the listingID, civic adress of the listed property, asking price and listing realtor's service name of each listing whose listing date is the latest.
4. A database table, Customers, has many columns. Two of these columns are customer_id and last_name. The following 25 tuples are inserted into this table:

   customer_id	last_name	other columns
   1023	Van	...
   1024	Morin	...
   1012	Campbell	...
   1013	Anderson	...
   1014	Leblanc	...
   1015	Lee	...
   1010	Taylor	...
   1011	Cote	...
   1020	Thompson	...
   1021	Gauthier	...
   1025	Bouchard	...
   1026	Wilson	...
   1001	Smith	...
   1002	Brown	...
   1003	Tremblay	...
   1022	Young	...
   1016	Jones	...
   1017	White	...
   1018	Williams	...
   1019	Miller	...
   1004	Martin	...
   1005	Roy	...
   1006	Zimmer	...
   1007	Macdonald	...
   1008	Gagnon	...
   1009	Johnson	...

   You are asked to construct two B+ tree indices on the table Customers using customer_id and last_name as the search key for the two indices respectively. Suppose that, no matter what data to be stored on each page, each leaf node can hold at most 4 data items and 2 page pointers, and each internal node can hold at most 4 search keys and 5 pointers.
   
   1. Suppose that there is no index built on the table Customers yet, can one of the indices you are asked to build be a primary index? If you answer is no, justify your answer. If your answer is yes, explain how to built such a primary index?
   2. Show one secondary index you are asked to build. You can choose either customer_id or last_name as the search key for this index.

How to Submit

You can submit your assignment solutions using one of the following two ways:

• Submit a hard copy before the due date/time.
• Login to your VIU Learn account, find the CSCI 370 course page, click on the "Assessment" drop-down menu, click on the "Assignments" item, then click on the folder named "AA". Then you can click on the "Add a File" button to browse and upload your solution file(s).