Your task during the classwork is to design and construct a database and a set of Web pages that can be used to recover and display some of the data. This is an individual task and must be accomplished without collaboration or collusion.
Read the following description of a data model. From the specification, produce the following:
- An enhanced entity relationship model.
- A list of table structures produced by logical design based on the above enhanced entity relationship model.
- The SQL code to construct and populate only sufficient tables to carry out the query part of the exercise. The database can be built using either Oracle or MySQL.
- A set of web pages that execute the queries and display the results.
The art gallery holds paintings on behalf of their owners so that the paintings can either be permanently on display in the gallery or on loan to exhibitions around the country. Each owner may have introduced one or more other owners to the scheme.
The catalogue of paintings held by the gallery records the name of the artist, the title of the painting, its estimated value and current location within the gallery or the venue of the exhibition at which the painting is currently on show. In addition, the gallery must record a paintings acquisition date, insurance value and owner. The location for a painting is the room number of one of the buildings owned by the gallery. Each location is maintained at a specific temperature and humidity. For the artists, basic details of name, date of birth, date of death (if applicable) and nationality are held. In order to maintain a record of the history of a particular painting, the gallery keeps details of the current owner and all previous owners.
The gallery keeps a record of all exhibitions to which each painting has been loaned while it has been held at the gallery (loans when the painting was not held at the gallery are not recorded). Exhibitions to which the gallery loans paintings are all privately sponsored and the gallery wants to keep records of which sponsors have sponsored exhibitions where their paintings have been shown. Each exhibition has a venue, a unique title, a start and finish date and details of the exhibition sponsors.
The first step in this process is to construct an enhanced entity relationship diagram.
Read the above specification and write down a list of the entities and attributes. For each entity indicate the identifier and write a sentence to describe the significance of the entity. Indicate any supertype/subtype hierarchies. Make a note of necessary assumptions. Draw an enhanced entity relationship diagram.
Table structures should be written down in the following format:
Using the enhanced entity relationship model from Section 4.1, write down a table
structures for each entity taking care that:
- each attribute becomes a column.
- the unique identifier becomes the primary key and is indicated by underlining
- subtype/supertype entities are represented in one of three methods described in the lectures
Physical database design is generally specific to the database management system that is to be used and the performance requirements of the system. In both Oracle and MySQL the chief tool is the creation of indexes, etc. For the purposes of this exercise it is sufficient to create the necessary indices. It is not necessary to optimise the database structure by merging entities etc. Make a note of the attributes from
Section that will require indexes. You would normally use an index for attributes that are involved in joins or which are the subject of an SQL ‘where’ clause. Both Oracle and MySQL automatically create indexes for primary keys but you must identify these using appropriate constraints. You will also need to note foreign key attributes for index creation as indices.
Implement only the parts of the database that are necessary for carrying out the queries. Implement your design in Oracle or MySQL on the Departmental devweb server. Use appropriate integrity constraints. Populate each table with a limited set of data i.e. only enough to show that the queries work.
You now need to write some queries on your database. The queries must be useful queries and not artificially constructed simply to fulfil the criteria listed. All queries require a WHERE clause of the form ‘…WHERE ATTRIBUTE = Value…’ to limit the rows returned (Value can be a text, numeric or date value). Write SQL statements that will
- carry out a join between two tables and use the group by clause.
- execute a sub-query.
- execute a correlated-query.
- carry out a self join that uses primary key/foreign key attributes.
For MySQL, output can be saved to files using PHPmyadmin. The output of Oracle SQL queries can be captured in a file by typing:
Using PHP, HTML and CSS as appropriate design a website that has five main pages: one main page with links to four other pages to support your four queries from above. Each of these query pages should ask the user for required data and then when submit is hit, present the results in a nicely formatted table. There should be a common look and feel (i.e. consistent appearance and placement of content) across all pages and every page should include a suitable company logo.