CMPT 355

Assignment # 2

Assignment # 2 is due 11:00 am Wednesday. Oct. 19

Topic: maintain and evolve your museum database.

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.

Be sure that your work is done according to this structure and that portions of your report are clearly labeled.

1. Create new exhibits to replace your current set of exhibits using a text file named C355A21.txt

1.a ensure you have a good database to start with (5 marks for db work and 5 marks for report)

• Before you get started with making changes, you should make sure that your database is properly structured.
• analyze your database from Assignment 1 to ensure that it is properly normalized and contains no unnecessary redundancies
• ensure that the structure of exhibits is such that you will not loose data about past exhibits as new exhibits replace them
• do not make any other changes to the structure of your database in this part of the assignment.
• You have two choices of how to accomplish this requirement:
• you may rebuild the database
  
• you may make the necessary modification to the existing database
• NOTE: in any case you are expected to report what you have done, thus if your database from Assignment #1 did not need any changes you still need to briefly explain why it did not need any changes
  
• NOTE: you will not be given this choice for future parts - you will have to process modifications for them

• Provide a portion of your report discussing what you have done to Assignment 1 and why you did this. (To get the marks in situations where you did not need to make any changes, please explain in your report why changes were not needed.)

1.b new exhibitions (5 marks for db work and 5 marks for report)

• As each of your current exhibits ends it needs to be replaced by a new exhibit.
• on the day after the end of a current exhibit all works are returned to storage
• on the next day works are taken from storage to the location of the new exhibit
• on the fifth day after the end of the previous exhibit the new exhibit opens.

• You need to plan the following exhibits and update the database accordingly:
• an exhibit of 8 works, that will go into the first (small) gallery available where it fits between the suggested minimum and maximum number of works. This exhibit will be on display for 4 months
  
• an exhibit of 9 works that will go into the next (small) gallery available where it fits between the suggested minimum and maximum number of works. This exhibit will be on display for 5 months
• an exhibit of 18 works that will go into the first (large) gallery available where it fits between the suggested minimum and maximum number of works. This exhibit will be on display for 3 months
• NOTES:
  
• small and large are only descriptors that are used by people to talk about relative gallery sizes, they are not official designations and should not be used in the database, since there are many possible sizes that might be referred to as small or large.
  
• you must let the update decide (via a subquery) when new exhibits start and end and where they will be located or you will loose some marks (however, if you cannot figure out how to do this all in sql it is better to lose these marks and to properly do the rest of the assignment than to stop at this point)
• you must not loose the information on the old / existing exhibitions, so the database must still contain information on these exhibitions as well as the new ones.

• Provide a portion of your report discussing what you have done to accomplish these routine operations

2. Evolve the database in the following order:

2.a. expanded works (5 marks for database work and 10 marks for report) using a text file named C355A22a.txt

• Your museum is so popular that it is now able to borrow works from donors (on a long term basis) and from other museums (to use in particular exhibits).
  
• Revise your data definitions to include identification of whether works are: owned works or borrowed works, or works that might be potentially borrowed from some other owner (and any other information relating to this expanded base of works).
• Add 15 new works to your database and have them be long term borrowed works (use works from the museum you used in assignment 0 - but attribute them to being borrowed from various cartoon character donors).
• Add 15 new works belonging to other museums as potential works that could be borrowed (that might be used for an upcoming (not yet scheduled) special exhibition) and add data about them (as if you are able to borrow them) in your database. NOTE: these works will not actually arrive at the museum until sometime near to the start of the special exhibition for which they will be used and will be returned soon after the exhibition is finished.
• (This means that your database should have 95 works recorded in it, but your museum will only have 80 of these works.)

• Provide a portion of your report discussing what you have done to accomplish these expanded works

2.b expanded facilities (5 marks for database work and 10 marks for report) using a text file named C355A22b.txt (and any other text files that you wish to use with it that should be named C355A22b-1.txt to C355A22b-n.txt)

• Your museum is so popular that the board has authorized a speedy expansion of the facilities.
• This involves building a new large gallery with a suggested minimum of 15 works and suggested maximum of 25 works. The gallery will be ready for use in two months from today. This new gallery has only one door that will  connect to one of the small galleries (the one furthest from the storage facility - which is beside the other small gallery and the existing large gallery). 
• Once it is opened, the museum will start having exhibitions that use more than one gallery as long as all galleries used by an exhibition are connected by doors between one another.
• Revise your data structure (if necessary) to accommodate using data about connecting rooms in scheduling multi-gallery exhibitions.
• Add data about the new gallery to your database.

• Provide a portion of your report discussing what you have done to accomplish these expanded facilities. It is essential that you explain how that this works, even if your previous design already facilitated this working, in order to get the marks for this section. Also you should briefly explain the new data that you added to your database to accomplish this.

2.c more detailed information on items (10 marks for database work and 25 marks for report) using a text file named C355A22c.txt (and any other text files that you wish to use with it that should be named C355A22c-1.txt to C355A22c-n.txt)

• It is important to help museum patrons recognize the expertise that your museum specializes in. This can involve further information beyond just the types and sub-types of items you have. There might be many other potential characteristics of the items (that could be in their own attributes) that might be useful to consider when grouping items (for an exhibition or otherwise). These characteristics group information that is felt to be so important that it is now found in the text descriptions of items. Unfortunately searching text only finds matches to a single value of some characteristic and does not find all the potential vales of that characteristic. So you are being asked to make the database a little more helpful by identifying an important characteristic of your items and implementing this characteristic as a separate attribute (or set of attributes) in the database.
• Up to now the data about your items is relatively consistent with that for other museums (other than your type and sub-type classifications). It includes some common characteristics such as media and insurance value. However, there are likely to be a number of special characteristics of works that apply to many of  the  items in your museum but that are not contained in their own attributes in the database. Currently values of these characteristics are only to be found in the textual description attribute within your database. 
• The museum wants you to research the data found in this attribute and to suggest one or more additional attributes in the database that could be used to highlight the most important characteristic of the items which is not currently represented in its own database attribute(s).
• To do this you should:
• query the description attribute of all the records in your database to identify all the different words that are found in it. Your query should include: 
• the word 
• the total number of times it appears (anywhere in the description attributes) within database
• the number of different items that it appears in (once or more times in the description attribute of an item)
• produce for yourself two different copies of the query:
• query 1 sorted in order of number of different items that the word appears in (to identify the most important words)
• query 2 sorted in alphabetic order of the words (to identify similar words, i.e. variations on a single word)
• identify the "best" new attribute (or set of attributes) that you could add to the database to represent the most important characteristic of the works that is not currently represented by its own attribute(s)
• think about the important words and about what attribute might be useful 
• for the largest number if appearances of all important words that might apply to a single characteristic
• add the attribute(s) to your database structure
• update the records for each item to include appropriate value(s) for the attribute(s)
• NOTE: you can do this in any way you like as long as it uses sql commands to update the values (i.e. you can create your own table of items and values and use this to update the table or you can use programmatic methods to find certain values in the description fields and then to update the table)
• consider what further changes might be useful to make (but you don't need to make them - only to report them)

3. Verify the database (5 marks for database work and 10 marks for report) using a text file named C355A23.txt 

• It is essential that you ensure that what you do to a database works as planned. Create and use queries to verify that what you did for each of the sections above (1a, 1b, 2a, 2b, 2c) worked as intended.
• In your report be sure to explain what you did and what you found out. If you found problems that you did not have time to fix, also explain the problem and what you would need to do to fix the situation.

4. Create a report in a Word document named C355A2r.doc that explains:

• a well reasoned and detailed explanation of what you did and why you did it for each part (1a, 1b, 2a, 2b, 2c)
• you should include the applicable e-r diagrams from db-visualizer with each part of your report to assist the markers
• (special expectations and mark allocations for sections of the report are discussed along with each of the parts above that they apply to)
• be sure that you explain how you met the all the requirements clearly

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

Hand In:

• use the Moodle system to hand in all the files you used along with your report
• your database on the Department PostgresSQL server must be equivalent to the results of applying all your text files 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. Create New Exhibits      1a - database changes (if needed)      1a - report      1b - new exhibitions      1 b - report	5    5    5    5
2. Evolve the Database      2a - expanded works      2a - report      2b - expanded facilities      2b - report      2c - additional attribute(s)      2c - report	5 10   5 10 10 25
3. Queries to Validate      1a - database changes (if needed)      1a - report      1b - new exhibitions      1 b - report      2a - expanded works      2a - report      2b - expanded facilities      2b - report      2c - additional attribute(s)      2c - report	1 2 1 2 1 2 1 2 1 2