CMPT 355

Assignment # 3

Assignment # 3 is due 11:00 am Wednesday, Nov 2

Topic: evolve your museum database with a focus on temporal data

NOTE: You MUST successfully complete your Assignment #3. It will be used as a basis for your project. If you do not hand in on time a successful Assignment #3, you will be placed with other people who also have not completed their Assignment #3.

Methodology:

You are to do the following using text files of SQL commands, dbvisualizer, and the PostgresSQL database provided to you on the Department PostgresSql server. You may initially test out your work on any machine equipped with PostgresSQL that is available to you.

NOTE: It is expected that you fix any major problems with your database from A#2 via sql commands before starting this assignment. There are no marks assigned to fixing those problems and no formal reporting requirements for documenting the fixes. However, if you do not make required fixes, marks may be deducted from A#3 for not starting from a good database structure. Therefore, you should briefly explain the fixes you have done (or why you did not need to do any fixes) at the start of your report for A#3 to help the marker understand where you are starting A#3 from.

NOTE: Be sure to include a copy of your dbVisualizer e-r diagram for the structure of your database that you are starting to use for A#3 at the start of your documentation for part 1.

Routine Maintenance

1. expanding your current exhibitions  using a text file named C355A31.txt (10 marks for db work and 5 marks for report)

• It has been decided that in preparation for the gala opening of your new exhibition space, that as many works as possible should be put on display.
  • this means adding works to all current and currently planned upcoming exhibitions to bring the number of works in an exhibition up to the maximum recommended capacity of the locations where the exhibition is taking place
• You need to
  
  • produce a report of all current and future exhibitions including the name of the exhibiton, the dates it is happening (starting and ending dates), the maximum recommended capacity of the locations it is using, and the current number of works planned for it
    
  • manually decide upon works to add to the various exhibitions to bring each room up to its full recomended maximum recommended capacity.
  • process updates to add works (that you have manually selected) to expand the exhibitions
    
    • this is easier for exhibitions that have not yet started
    • for exhibitions that are currently under way, additional works should be added to those exhibitions on October 28.

2. evolving collection using a text file named C355A32.txt (5 marks for db work and 5 marks for report)

• Your museum has just purchased 15 additional new works on October 25
• (which you should claim from the real world museum that you based your assignment on) 
• (these are in additon to the 95 works that are already known in your database).
• It has also sold (de-collected) 3 works (that have not been exhibited since the start of the database) to other museums (on October 21).
• (If there are no works that have not been exhibitied since the start of the database, then de-collect the works that are currently in storage that have been there for the longest amount of time since they were last exhibitied.)
• You need to
  
  • revise your data definitions in order to ensure that you can access the different locations of a work for the period of time when your museum owned or borrowed it. 
  • process the purchases and sales of works
    

3. recording history using a text file named C355A33.txt (20 marks for db work and 5 marks for report)

• It is important that you can identify the history of
  • different locations and exhibitions of a work on a date & time basis [the location of works is very important]
  • It is important for insurance purposes that you know the exact time (minute by minute) when a work moved between locations. 
  • Given that the museum is small, the time that it arrives at a new location can also be used as the time it left its previous location.
  • You should have the day by day information available for previous moves and this is good enough for those moves, since adding anything more precise would be guessing at best and fraudulent at worst.
  • You should start to use minute by minute information for all moves in the future.
  • different locations of an exhibition on a day by day basis
  • NOTE: the hours that an exhibition was open to the public are not needed for purposes of this assignment
  • different exhibitions with the same name that occur at different points in time / space
• You need to do the following to your database:
  • You might need to revise your data definitions in order to ensure that you can handle these new requirements
  • You might need to revise your existing data to include suitable date / datetime data in various records
  • To ensure that you have sufficient data in your database to help verify your queries, you need to (plan and) record (via suitable updates) three (new to the database) one/room exhibitions that occurred prior to the ones you entered in question 2 of Assignment #1. They were each exhibitons that included suitable numbers of works for the locations in which they were held and lasted suitable lengths of time.
• You now need to produce a number of queires to test that your database is properly working and that your data is good:
• Produce a query that lists the different locations that a given work was/is/will be in between two dates
  
  • the list should contain one line for each location including the date and time the work arrived at that location and the the date and time it left that location.
    
  • manually choose one or more works (currently in an exhibition) to use to demonstrate the query for the period between Nov 1, 2015 and Nov. 1, 2016 (NOTE: all works should have been in storage from when they were acquired by your museum until the first exhibits defined in Assignment 1 and in the new exhibitions that you defined above) 
    
• Produce a query that lists all the works found in an exhibition between two dates
  • the list should include the names of each work and the starting and ending date when the work was in the exhibition (sorted in order of starting dates and alphabetically within the same starting date)
  • demonstrate the query for the period between Nov 1, 2015 and Nov 1, 2016 for the exhibition that one of the above manually chosen works is currently part of.
• Produce a query that lists all the exhibitions that make use of a location between two dates
  • the list should contain one line for each separate use of the location including its starting and ending date sorted in order of starting dates
    • uses include exhibitions and periods when the location is closed due to changing exhibitions (which should be named as "closed for changing exhibitions")
      
    • if a particular exhibition returns to a location after some period of absence, it should be listed multiple times in this listing
  • demonstrate the query for the period between Nov 1, 2015 and Nov 1, 2016  for the location where one the above manually chosen works is currently being exhibited.

Expand your database to handle outgoing loans and traveling exhibitions  in the following order:

4. outgoing loans using a text file named C355A34.txt (20 marks for db work and 5 marks for report)

• Your museum is so popular, that it is now receiving requests to loan works out to other museums. It has been decided to handle loans by;
  
  • setting the location of a work that is loaned to another institution to "on loan" or some similar single value that fits within your current database schema
    
  • creating a new table to keep track of the name, address, phone number, and e-mail of the insititution to which a work has been loaned, along with the start and ending dates of the loan period.
    
  • NOTE: loans can be planned separately from recording when works are lent-out or returned
    
• You need to create the SQL
  
  • to revise your data defintion
  • to plan the loans of 6 different works (which you will manually select from a list of works that will be available during the time period of the loan)
    
    • loan periods vary in length from 2 - 8 months
    • the starting times of loans vary from some day in November 2016 to some time in April 2017
      
    • loans are to be made to a variety of (at least three) different institutions (that actually might be interested in the works).
    • two of the works to be loaned must have been exhibited in your first set of exhibitions
  • to process the loans that should have already started
  • to process the return of works that should have already been returned
    

5. traveling exhibitions using a text file named C355A35.txt (20 marks for db work 5 marks for report)

• Some of your exhibitions are proving so popular that there has been pressure to send them on the road
  • traveling exhibitions are different from loans since they are still controlled by your museum, they just exist at a temporary location or a series of temporary locations (and use one week for packing-up, transportation, and set-up when going between locations)
    
  • It is important to have a variety of information on each temporary location, including: name of sponsor of traveling exhibition, address of the temporary location, name of individual in charge of security, amount of insurance provided for exhibition (which must be at least 10% higher than the total value of current insurance value of the individual works in the exhibition)
• You need to create the SQL
  
  • to revise your data defintion
  • to plan a traveling exhibition
    
  • you should plan this exhibition based on (your manulally selecting from the results of an appropriate query) the earliest ending exhibition from Assignment #1
    
  • this traveling exhibition should travel to five Saskatchewan towns or cities that will each have the exhibition available to the public for three weeks.
    
    • NOTES:
      
      • Your traveling exhibition should start as soon as possible to help publicise the new expansion of  your museum.
      • If any works from the exhibition (that this exhibition is based on) are already committed to another in musuem exhibition or to be loaned out, then they cannot be part of the traveling exhibition and should be replaced in it by some other works that are available.
        

Create a report in a Word document named C355A3r.doc that explains what you did for each step (including e-r diagrams for each step)

Be sure to ask any questions you need to clarify this assignment, well before it is due.

Hand In:

• use the Moodle system to handin all four files {C355A31.txt, C355A32.txt, C355A33.txt, C355A34.txt, C355A35.txt, C355A3r.doc}
• your database on the Department PostgresSQL server must be equivalent to the results of applying {C355A31.txt, C355A32.txt, C355A33.txt, C355A34.txt, C355A35.txt} to it.
• NOTE: Your assignment is due at 11:00 am. Late assignments will not be accepted by the Moodle system. Extensions will only be granted for severe medical or family emergencies. No marks will be given for late assignments that do not qualify for an extension.

Marks:

5% of the year's grade, distributed as follows:

	% of assignment mark
1. Expand Current Exhibit	10 for db, 5 for report
2. Evolving the Collection	5 for db, 5 for report
3. Record History	20 for db, 5 for report
4. Outgoing Loans	20 for db, 5 for report
5. Traveling Exhibitions	20 for db, 5 for report

end of Assignment #3

Extra optional work:

[Note: you do not have to do the following. If you succeed with it, you can earn marks above the 100% allocated to the above assignment. doing Doing all of this optional extra work successfully could add up to 2.5% to your year's grade. To get marks for any section, you must have also done the preceding sections. I had to set Moodle up to accept marks up to 150, but be assured that I will use these marks out of 100 for grading purposes so that you do not have to do this extra optional work to get full marks on A#3.]

X. Develop mechanisms to perform automatic scheduling of new exhibitions

• Museum curators want to know when the optimal time will be to schedule future exhibitions.
• This involves considering tradeoffs in various disruption costs.
• Rather than deal with this abstractly, you should continue to work on your database.
  
• From time to time your museum is offered the opportunity to have a special guest exhibition of works from another museum, that is traveling around the world.
  
• You will want to be able to hold a special guest exhibition, when it becomes available, even if that means disrupting current or planned exhibitions.
• The special guest exhibition should be scheduled with as low a disruption cost to start on a date as near as possible after the date on which it would become available to you.
  
• To schedule this special guest exhibition, you can
  
• preempt an exhibition that is already scheduled
  
• end an exhibition early
  
• extend an exhibition
• you should consider the following disruption costs:
• delaying opening the special guest exhibition costs 5 disruption points per day
• leaving a gallery empty costs 4 disruption points per day (galeries being emptied of old exhibitions or being loaded with new exhibitions are not considered as empty during the days when these activities need to take place)
  
• ending an exhibition early costs 3 disruption points per day
• extending an exhibition costs 2 disruption points per day
• preempting an exhibition costs 1 disruption point per day
• This optional extra work may be done with or without using triggers, as long as it is clearly explained.

X.a. Design for calculating disruption costs (20 marks for a report - this is available in any case - in other words, you could get these marks for successful planning of your strategy)

• Create a report in a Word document named C355A4a.doc that explains your design for calculating disruption costs
• This should be a well resasoned explanation of your design (including what you you need to do in terms of database queries and why you need to do it).
• It should also explain how you will use the results of your query to decide on when and where to schedule the special guest exhibition.
  
• It should also explain how these queries can be generalized to be used in for future scheduling.
  

X.b. Calculating disruption costs (10 marks for db work according to report from X.a, prerequisite X.a)

• Use a text file named C355A4b.txt for the SQL needed to query disruption cost information.
  
• This  query needs to generate disruption cost information to assist you in assessing what scheduling changes to make.
• Then make your decision as to when and where the best date and location for holding the special guest exhibition is. Be sure to record this decision in your design report (C355A4a.doc).

X.c. Creating the guest exhibition (10 marks for db work according to report fromX.a, prerequisite X.b)

• Use a text file named C355A4c.txt for the SQL needed to accomplish the planning of this exhibition.
  
• You need to plan a special guest exhibition of 18 works (that are not currently in your database) that will come available on Feb 13, 2013.
• This exhibition can be placed either in the two small galleries or in one of the larger galleries.
• Explain what you have done in your design report (C355A4a.doc)

X.d. Making the necessary changes (10 marks for db work according to report from X.a, prerequisite X.c)

• Use a text file named C355A4d.txt for the SQL needed to accomplish the necessary updates.
• Make the appropriate updates to the database to schedule the special guest exhibition and make any necessary changes to the other scheduled exhibitions in order to minimize the resulting disruption costs.
• Explain what you have done in your design report (C355A4a.doc)