CMPT 355

Assignment # 1

This is now the formal version of this assignment.

Assignment # 1 is due 11:00 am Weds. Oct. 5

NOTE: It is best that you start obtaining the data you require for this assignment as soon as possible. It must be obtained from a museum Web site that has been approved by the instructor for your individual use. You may find that the museum Site you have chosen does not provide all the attributes of data that you need to populate your database.

Topic: Create, populate, and query an initial museum database.

Data Profiles:

The following requirements are important for (more requirements will be made known in future assignments):

Works

• See Assignment #0
  

Locations

Currently your museum is rather small. It consists of an entrance lobby, three galleries and a storage facility. Each location has:

• a unique name
• physical dimensions
• a capacity in terms of suggested number of works
  • NOTE: suggested values are useful for reports that identify numbers over or under the suggested value, but they should not be used as constraints on the actual number of items in the database
  • a suggested minimum amount of works
  • a suggested maximum number of works

  • location	suggested min	suggested max
    storage	0	600
    lobby	2	4
    gallery A	7	16
    gallery B	7	16
    gallery C	14	24

• one or more doors connecting to other locations
  • the lobby connects to the storage facility and to each of two small galleries
  • each of the two small galleries connects to the lobby, the other small gallery, and the large gallery
  • the large gallery only connects to the two small galleries

Exhibitions

• a good catchy unique name
• a description of the exhibition that explains the reason / purpose / rationale for the exhibition including some background to this reason / purpose / rationale and why the individual works belong together in the exhibition. This should be a sentence or two in length.
• the (start and end) dates of the exhibition. 
• the location where the exhibition is being presented (for this assignment this will be a particular gallery as defined below in the discussion of how to populate the 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.

1. Create a database using a text file named C355A11.txt

• Create a database with the domains, tables, attributes, views required to store data that fits the above data profiles.
  • Follow the standards presented in the tutorials and the lectures.
  • You must use domains to define all attributes
  • You must name your attributes with 
  • a prefix of 2 or 3 characters that is unique to the table
  • the domain name being used to define the data type of the attribute
  • an optional suffix to distinguish between different instances of the same type of attributes within a table
  • You must use views for all database accesses (both for inputs/modifications and for outputs)
  • Choose meaningful names for each domain, table, attribute, and view.
    
  • Choose reasonable data types for each domain (and thus attribute) based on whatever information you have from the above data profile, from observing the data at your museum Web site or the Web site of other museums, and from common sense.
  • Choose meaningful constraints, where appropriate

2. Populate the database using a text file named C355A12.txt (and any other text files that you wish to use with it that should be named C355A12-1.txt to C355A12-n.txt) in the following order:

• Add data on the the locations (including three galleries, lobby, storage and connecting doors) as discussed above.
• Add at least 65 works to the collection of your museum database, from the text file you created for Assignment #0. Create missing data where necessary and allowable.
  
• Be sure to do this in the manner suggested in your tutorial - so that you do not expend a lot of unnecessary effort in getting this accomplished.
  
• Place all items initially in your museum's storage facility.  
• Create three exhibitions that are currently taking place
• One exhibition should contain 9 works, one 10 works, and one 16 works.
• One exhibition should have started during July 2016, one during August 2016, and one during September 2016. On average exhibitions should last between 2 and 7 months  and should not end before the start of Octover 2016. (you should pick the exact dates to use within these basic requirements).
• Place the selected items in these exhibitions
• Assign two special works to be displayed in the lobby.
• Assign the exhibitions to locations using SQL for the following logic (which you should decide yourself rather than trying to get your program to make this decision):
  • the exhibit with the least number of works should be assigned to the smallest gallery
  • the exhibit with the most number of works should be assigned to the largest gallery
  • the unassigned exhibit should be assigned to the unassigned gallery
• Update the location of all works that are now in exhibitions

3. Query the database using a text file named C355A13.txt in the following order:

• Produce for the head of the museum a listing of all works (including their identifier, name, and insurance value} that are currently in storage.
• Produce for the public a listing of all exhibitions (including name, description, location, and number of works)
• Produce for the public a listing of all publicly available data on all works in each of the exhibitions sorted by exhibition and by the name of the work
• Produce for a curator a listing of works sorted by when they are available for use in a new exhibition and by classification and by name of the work
• Produce for the curator a listing of the amount of additional works that could be added to each exhibit based on the unused capacity of the galleries that they are currently in. This listing should just have the amount of additional works that could be added and the name of the exhibit

4. Create a report in a Word document named C355A1r.doc that

• includes
• the entity-relationship diagram produced by db-visualizer as an introduction to your assignment
  
• and explains
• the specific reasons you used in choosing names for each table, attribute, and view.
• the specific reasons you used in choosing the data types and sub types for each attribute.
• the way in which you obtained or created the values for each attribute.

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 the files you used along with your report
• e.g. {C355A11.txt, C355A12.txt, C355A13.txt, C355A1r.doc and any additional data files}
• your database on the Department PostgresSQL server must be equivalent to the results of applying {C355A11.txt, C355A12.txt, C355A13.txt, etc} 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:

The marks for this assignment are distributed as follows:

	% of assignment mark
1. Create Database	30
2. Populate Database	30
3. Query Database	20
4. Report	20

.