An art gallery wants to create a database to manage its collection and exhibitions. The gallery needs to store: Details of artworks (title, artist, medium, ...
Question 1 Report
An art gallery wants to create a database to manage its collection and exhibitions.
The gallery needs to store:
- Details of artworks (title, artist, medium, year created, estimated value)
- Details of exhibitions (exhibition name, start date, end date, gallery room)
- Which artworks are displayed in which exhibitions
(a) Design three tables: ARTWORK, EXHIBITION, and DISPLAY. For each table, list field names with data types and identify the primary key. [4]
Answer Details
(a) This question tests your ability to design a relational database with appropriate tables, data types, and keys for a many-to-many relationship. [4]
ARTWORK table:
| Field Name | Data Type | Key |
|---|---|---|
| ArtworkID | INTEGER | Primary Key |
| Title | VARCHAR | |
| Artist | VARCHAR | |
| Medium | VARCHAR | |
| YearCreated | INTEGER | |
| EstimatedValue | REAL / DECIMAL |
[1] for sensible fields with types, [1] for primary key. EstimatedValue uses REAL or DECIMAL because monetary values require decimal precision.
EXHIBITION table:
| Field Name | Data Type | Key |
|---|---|---|
| ExhibitionID | INTEGER | Primary Key |
| ExhibitionName | VARCHAR | |
| StartDate | DATE | |
| EndDate | DATE | |
| GalleryRoom | VARCHAR |
[1] for sensible fields with types, [1] for primary key.
DISPLAY table (junction table):
| Field Name | Data Type | Key |
|---|---|---|
| DisplayID | INTEGER | Primary Key |
| ArtworkID | INTEGER | Foreign Key |
| ExhibitionID | INTEGER | Foreign Key |
[1] for both FK fields present, [1] for primary key.
The DISPLAY table resolves the many-to-many relationship: one artwork can appear in multiple exhibitions, and one exhibition can display multiple artworks. Each record in DISPLAY represents one specific artwork being shown in one specific exhibition. The foreign keys link back to ARTWORK and EXHIBITION respectively.
Download The App On Google Playstore
Everything you need to excel in your exams
