In this practical assignment, we will run through a complete database application development cycle, starting with the schema design, from an initial data import to database application programming and transactions.
The Scenario: A Museum Information System
The context of this assignment is a museum information system which helps the museum staff (curators, registrars, and exhibition designers) to manage the catalogue of all objects owned by the museum. This includes the capturing of a variety of information about the origin and significance of any object, as well as keeping track of where an object currently is (for example on display in some room as part of an exhibition, or at some location in the museum's storage).
Figure 1: Screenshot of a commercial Museum catalogue application.
Above's screenshot1 is an example of a professional museum information system as used by many Museums worldwide. This catalogue information is central to the museum's functioning and hence stored in a database. The information you see is retrieved from the back-end database system and can be edited in this application. While its main usage is for the internal management of the museums catalogue, part of the data is typically used to be also published on the museum's website or in exhibition catalogues.
A good example for such a setting is the Powerhouse Museum in Ultimo, which we will use as scenario for this assignment. On its public website, there is a section where one can search the museum's collection: https://www.powerhousemuseum.com/collection/database/
The data set that is accessible here is an excerpt from the museum's information system that is presented in a web-friendly way. If you search there, for example, for virtual reality game console, you will get to the same information than is displayed in a more structured form on the screenshot above.
We will use a subset of this data set as a starting point to create an own museum catalogue database and to implement the core functionality of the internal information system.
Question 1: Database Application Programming
Your general task in this practical assignment is to build a basic museum information system. Note that this does not include a museum website, but rather the database and the database application to maintain a museum catalogue internally.
The assignment consists of several components. Your team's final grade depends on how sophisticated and correct your solution is. For example, in order to just pass this assignment, it is sufficient to model the core catalogue database, import the provided data set and write a simple application that allows to browse and edit single objects. If you strive for a higher grade, you would on top of this have to implement more functionality, respectively extend the core data model with some further specialities as discussed below.
A.
1. a conceptual database schema in the E-R notation, that correctly captures the core entity types and relationship types of the powerhouse museum catalogue as described on page 5 under 'Core Database
2. a relational database instance that correctly implements your conceptual database model including suitable choices of keys, domain types and integrity constraints
3. an application that provides a simple user interface to perform the following core processes on your database
4. your application makes correctly use of the database queries; for example any possible filter condition is expressed in SQL queries rather than just scanning a database table and searching for the right entry outside the database.
5. your application is coding the 'Add New Object' functionality as a correct SQL transaction.
You are free to use any programming language, e.g. Java/JDBC, as long as it supports explicit SQL and transaction commands. A set of dynamic web pages using, e.g., PHP is fine too, but note that we can only give support for Java/JDBC!
The user interface can be textual or graphical (feel free to implement a GUI, but there will be no marks for GUI design etc). The actual grade achieved depends on the quality of your schema design, your database and the correctness of your queries and transactions.
B.
1. Your conceptual data model also captures the 'Additional Information'
2. Your database schema is in BCNF.
3. All update processes are coded (correctly) as SQL transactions.
4. Your Appropriate user-level error messages are given in case of a database error.
5. Your application correctly protects against SQL injection attacks.
6. At least one of the transactions is implemented as stored procedure.
Tip: If you implement the new-object functionality as stored procedure, you should be able to re-use it for both data import and the 'Add New Object' screen.)
C
1. You have created appropriate indexes to support your most common queries.
D.
1. Your conceptual data model also captures the 'Hierarchical Data'
2. Your database application implements at least two of the additional task packages
3. There are no flaws on the transaction semantics or the correct usage of SQL queries
4. You have created appropriate indexes to support your most common queries.
5. Your application code makes a clear separation of the data access layer from the application logic and the presentation layer.
Data Description
Core Database
Your museum information system shall be able to maintain the following core information about the museum's objects and to which collection they belong:
A museum owns a large number of different objects that are of some historic significance. Your system has to keep several pieces of information about each museum object: its title, a short description, an optional description of the specific marks on an objects (e.g. some obvious scratches or signatures), when it was produced, when it was obtained by the museum, if possible its dimensions and weight, an url, and its current location in the museum. For example with the Powerhouse Museum, an object can be in one of (currently)
three locations of the Powerhouse Museum: the main museum in Ultimo, the Sydney observatory, or the storage in Castle Hill. Each of those locations has several buildings with many rooms, in which the object can be on one specific shelf or display area.
All objects are further associated with one or more categories. Every object has been obtained by the museum at some point in time as part of a collection from a donor (or seller), and it keeps that information in its catalogue too. For example, the Nancy-Bird Walton Collection is the set of objects that were donated by Nancy-Bird Walton, Australia's most renowned woman aviator, to the Powerhouse Museum in the year 2000.
Finally, the information system also has to keep track of its users. We need the name, and the login information for everyone who is allowed to access the system. Each user shall also have a field-of-expertise associated with him.
Data Import
Write a Data Importer that can load the relevant part of that data set into your database. Please use the data set that we prepared on out INFO2120 assignments page. Note: You can take all IDs literary and assume 'Powerhouse Museum / Ultimo' as the location for all data with no associated location.
This example data set describes a set of exhibition objects with a number of attributes:
Record ID an internal, technical ID that uniquely identifies each object in the whole catalogue
Registration Number an unique registration number that is 'human-readable' and by which curators internally identify each object; over time, several different variants of this registration number evolved...An object's collection is encoded in its registration number: YEAR / COLLECTION OF YEAR / ITEMNO
Object Title title for an object
Description free-text description of the object
Marks optional free-text description of all specific marks on the object (such as scratches or some text)
Production Date when this type of object has been produced; can be a year, a year range, or a 'circa' text
Categories a serialised form of all the categories that an object belongs to (separated by 'j')
Persistent Link an URL to the object home page on the PHM website
Height, Width, Depth, Diameter optional dimensions of an object
Weight optional weight of an object
Provenance (Production) only for Credit level; for Pass level you can ignore this column
Provenance (History) only for Credit level; for Pass level you can ignore this column
Additional Information:
In addition to above's core data model, you need to also model the following information for a Credit-Level database schema:
Firstly, an object can further be organised into other types of collections, called 'narrative' and 'exhibitions': These are set of museum objects that share some thematic background, for example about objects designed in the 80's (cf. the museum's current narrative about "The 80s Are Back"). Finally, the museum
is organising changing exhibitions of objects, for which again a set of objects is put together. For each narrative and exhibition, we need to know the title and a short description, and of course which objects are associated with it.
Secondly, a museum keeps several forms of provenance data2 per each of its objects:
Production provenance data describes who (as a person or organisation) was the DESIGNER, MAKER or PHOTOGRAPHER of a certain object. Note that we typically want to keep several origins here, e.g. who was the original designer and who the manufacturer(s).
Historic provenance data describes who (as a person or organisation) played some role for the specific object in the catalogue during a certain period of time, such as being USER or the OWNER. Again these could be several people and organisations during the lifetime of an object.
In each case, the museum wants to keep track of the role of the person or organisation for the corresponding provenance entry (such as designer, maker, owner, user etc), when this took place (either as a point in time or a range of years), and the place of where it happened. We leave it up to you whether you wanna model a controlled vocabulary for the places and the persons/organisations, or whether you take it as free-text.
The corresponding two entries in the data export from the Powerhouse museum are:
Provenance (Production) list of production entries (serialised into a string), separated by 'j';
entry format: ROLE: WHO; WHERE (optional); WHEN j
Provenance (History) list of history entries (serialised into a string), separated by 'j';
entry format: ROLE: WHO; WHERE (optional); WHEN j
Hierarchical Data:
In addition to above's credit-level data model, you need to also model the following information for a Distinction-Level database schema:
An object can consist of several sub-parts, each of which can be handled quite separately by the Museum. For example, the main hall of the Powerhouse Museum displays the Bell 206B Jetranger III helicopter that was flown by Dick Smith on his solo circumnavigation of the world in 1982/83. Actually, it only shows the helicopter's fuselage. The helicopter's engine and all the removable content which came with it (such as its
flight manual or, curiously, an empty 150gr jar of Vegemite) are displayed somewhere else - or could also be still kept in storage. The example data set shows the part-of relationship in the registration numbers, whose most common format is as follows:
COLLECTIONNUMBER / ITEMNUMBER [ - SUBPART (optional) [ / SUBSUBPARTS (optional)] ]
Furthermore, also the location information of each object shall be organised hierarchically, and in addition allow for objects to be borrowed out to an external organisation (for example being part of an Australian exhibition that tours the world for some months).
Processes
The following defines several processes that should be implemented in your database application. The 'Core Application Functionality' must be implemented by every team.
Core Application Functionality
a) The system shall start with a LOGIN screen where museum staff members can log in with their username and password. Those two input arguments must be matched against your Museum database. When a valid user/password combination was entered, users shall be directed to the CATALOGUE MENU screen. If username or password are incorrect, an appropriate error message shall be given.
b) The CATALOGUE MENU page shall list the staff's collections by title for which s/he is responsible, plus the following options: List Collection, Add New Object, and Logout. Logout shall log out the user and direct back to the login screen.
c) The COLLECTION LIST screen shall list all objects that belong to a specific collection. The screen shall show the title of the collection, its description (if it exists) plus a listing of all objects that are included (show registration number and the object title). This list shall ordered by their registration number. The user shall be able to select one of the listed objects to get to its 'Objects Detail' screen.
d) The OBJECT DETAILS screen shall show all details of a given object. From here, users shall then be able to navigate to the 'Edit Object' screen for this object.
e) The EDIT OBJECT screen shall allow a logged-in user to edit the details of a specific object.
f) The ADD NEW OBJECT screen shall allow a logged-in user to create a new museum object. Beside the core pieces of information for an object, this shall include the possibility to specify for an object which categories it is in and also to which collection it belongs to. If this is the first object of a new collection, it also should allow from here to go to the NEW COLLECTION screen to first create a new collection.
g) Add a Search functionality to the 'Catalogue Menu' screen that allows to search for objects in the catalogue by registration number patterns, by title (patterns) or by description text.
h) Implement an XML Export functionality that allows to export a specific collection from the database as an XML file. This functionality should be reachable from the 'Collection List' screen. Define a suitable XML format that allows you to capture the whole collection details plus all objects - and their details - that belong to the given collection.
i) Implement an Audit Trigger that keeps track of when location information has been changed and by whom. The trigger shall fire whenever a user updates the location of an object, and then store in an extra table who (the current user) moved the object to which new place and when (current date). The OBJECT DETAILS screen further shall show the full history of location changes for a given object.