Introduction
实现一个美术馆的网站，需要实现的是数据库部分，即用MySQL实现查询逻辑，最终需要前端页面展示。
Arm Of The Assignment
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.
Task
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.
Database specification
Art Gallery
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.
Process
Enhanced entity relationship model
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.
Logical design
Table structures should be written down in the following format:
TABLE_NAME(Primary-key-attribute,Non-key-attribute1, Non-key-attribute2…..).
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 Design
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.
Creating and loading the database
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.
Querying the database
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:
Web Page Front End
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.
