$3\mathrm{.}3$ Sample of Data

Design, implement and document:

$1.$ a database system which satisfies the requirements listed in Section $3\mathrm{.}23\mathrm{.}2$ and which

contains the sample of data listed in Section $3\mathrm{.}33\mathrm{.}3.$

$2.$ SQL for the queries listed in Section

A database system is required to store data on a chain of art galleries called The Tate.

Each gallery is identified by a unique three character code called gallery id$.$ The

name, location, city and postcode of a gallery may be stored in the database. The

database checks that no two galleries have the same name. Each gallery displays

many paintings. Not every painting is on display because some paintings are kept in

storage.

Obviously, at any moment in time, a painting cannot be on display at more than one

gallery. This database represents a snapshot in time and so does not record data on

loans of paintings from one gallery to another.

A gallery may hold one or more exhibitions. An exhibition is where some paintings

which fit into a theme are displayed next to each other in a particular gallery. A painting

cannot feature in more than one exhibition.

The Tate only has originals; it does not have any copies of paintings. Paintings have

a unique three character code called painting id$.$ Where they are known, data on

the title, height and width of paintings are stored. The medium on which the paint was

applied may also be stored. Each painting is painted by one artist.

Each artist is identified by a unique three character code called artist id and must

have a name. Other data on artists which may be stored in the database includes their

nationality, the year in which they were born and the year in which they died. Some

artists paint in one or more recognised styles. Each style has a one word description

and a detailed textual description. The design of the database assumes that every style

is followed by at least one artist in the database.

An E$-$R Diagram drawn using the Crow$$s foot notation.

$(20$ marks$)$ MAXIMUM $1$

$2$ SIDE OF A$4$

$$ A listing of your logical $($relational$)$ model.

$(10$ marks$)$ MAXIMUM $1$

$2$ SIDE OF A$4$

Database Systems CRN $32741,$UMC UMC G$40010045$ Assessment Information$/$Brief

$$ An explanation of why your logical model is in first, second and third normal form.

$(10$ marks$)$ MAXIMUM $1$

$2$ SIDE OF A$4$

$$ SQL statements that create the relations

$(30$ marks$)$ MAXIMUM $1$ SIDE OF A$4$

$$ SQL statements that populate the database.

$(10$ marks$)$ MAXIMUM $2$ SIDES OF A$4$

$$ SQL statements that query the database.

$(20$ marks$)$