---

---

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.

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

1. carry out a join between two tables and use the group by clause.
2. execute a sub-query.
3. execute a correlated-query.
4. 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.

---

关于分析师

在此对LE PHUONG对本文所作的贡献表示诚挚感谢，她在山东大学完成了计算机科学与技术专业的硕士学位，专注数据分析、数据可视化、数据采集等。擅长Python、SQL、C/C++、HTML、CSS、VSCode、Linux、Jupyter Notebook。