Assessment regulations
Refer to section 4 of the How you study guide for undergraduate students for a clarification
of how you are assessed, penalties and late submissions, what constitutes plagiarism etc.
Penalty for Late Submission
If you submit your coursework late but within 24 hours or one working day of the specified
deadline, 10 marks will be deducted from the final mark, as a penalty for late submission,
except for work which obtains a mark in the range 40-49%, in which case the mark will be
capped at the pass mark (40%). If you submit your coursework more than 24 hours or more
than one working day after the specified deadline you will be given a mark of zero for the work
in question unless a claim of Mitigating Circumstances has been submitted and accepted as
valid.
It is recognised that on occasion, illness or a personal crisis can mean that you fail to submit
a piece of work on time. In such cases you must inform the Campus Office in writing on a
mitigating circumstances form, giving the reason for your late or non-submission. You must
provide relevant documentary evidence with the form. This information will be reported to the
relevant Assessment Board that will decide whether the mark of zero shall stand. For more
detailed information, please refer to the University Assessment Regulations.
Coursework Case Study - The Art Gallery
The Art Gallery boasts an impressive collection of paintings, each with its own distinct
characteristics. Every painting is carefully catalogued with a unique identification, whenever
possible, the artist's attribution, the creation year (if known), a descriptive title, and a detailed
description. Additionally, comprehensive details include the paint type (such as oil or
watercolours), the underlying material (ranging from paper and canvas to wood), and the artistic
style (spanning Modern, Abstract, Impressionism, Expressionism, and Cubism).
Paintings in the Gallery's collection are further classified into two categories: those that belong
to the permanent collection, owned outright by the museum, with associated information
encompassing the date of acquisition, current display status (whether they are exhibited or in
storage), and their acquisition cost; and those that are temporarily on loan from external
museums. For the latter, relevant details include the lending museum's identity, the date the
painting was borrowed, and the anticipated return date.
The Gallery meticulously maintains artists' records, capturing essential information including
their name, date of birth, date of death (if applicable), country of birth, the artistic epoch they
belong to, their primary artistic style, and a descriptive overview. It is assumed that each artist's
name is unique.
Moreover, the museum orchestrates various exhibitions throughout the year. Each of the
exhibitions is defined by a distinctive name, a commencement date, and an end date. These
exhibitions are intrinsically linked to the paintings that are on display during their respective
timeframes, forming a dynamic and ever-evolving showcase of artistic expression.
You have recently joined the Art Gallery as a junior database developer. Your line manager has
asked you to design and develop a database system for the problem.
ggosonoot
4
Task details:
1. You are required to produce an ERD (Entity Relationship Diagram) for the case study
given using UML notation, any other notation will not be graded.
2. The ERD should represent aspects related to The Art Gallery only
3. For each of the entities in your model, you should clearly identify appropriate attributes
and relevant keys.
4. Relationships between entities should be clearly identified and labelled with any
assumptions fully documented.
5. You are also required to implement your design as a relational database using MySQL
database by creating appropriate SQL scripts and executing them.
6. You should populate your tables with sufficient data to demonstrate the execution of
ten relevant and useful queries.
Submission Requirements
You must submit a single pdf document with the following elements:
1. ER diagram for The Art Gallery system – a sample diagram is given in the Appendix.
You are advised to use the entity table shown after the sample ERM to ensure that
for each and every entity, a primary key is identified and a foreign key where
necessary.
2. SQL table creation scripts.
3. SQL scripts showing the sample data you have inserted into your database - a sample
script file is shown on the last page (see Appendix).
4. Ten SQL query scripts that demonstrate some useful functionality of the system, with an
explanation of their rationale/use. The SQL should be provided, together with a screen
print of the output it produces. Use the table on the last page to document yourqueries.
Note: DO NOT write queries like: SELECT * FROM clients;
5
Appendix
Sample ERM (not the actual domain):
Entity Primary Key (PK) Foreign Key (FK) Rationale for PK
Client clientID N/A Client name as
alternative
possibility but may
not scale up and
clientID would be
unique
Staff staffNO N/A StaffNO is unique to
each staff
Project projCode N/A projCode aunique
identifier for each
project
Staff_Proj staffNO, projCode staffNO, projCode Staff_Proj is a link
entity and a
minimum PK is a
composite key made
up of parent PKs as
well as being FKs.
6
Sample SQL script file:
DROP TABLE IF EXISTS dept CASCADE;
CREATE TABLE dept
(DEPTNO INT(2),
DNAME VARCHAR(14),
LOC VARCHAR(13),
CONSTRAINT pk_dept_deptno PRIMARY KEY (DEPTNO)
) ;
Insert into dept (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
Insert into dept (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
Insert into dept (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
Insert into dept (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');
DROP TABLE IF EXISTS emp CASCADE;
CREATE TABLE emp
(EMPNO INT(4),
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INT(4),
HIREDATE DATE,
SAL FLOAT(7,2),
COMM FLOAT(7,2),
DEPTNO INT(2),
CONSTRAINT pk_emp_empno PRIMARY KEY (EMPNO),
CONSTRAINT fk_emp_deptno FOREIGN KEY (DEPTNO) REFERENCES dept (DEPTNO)
);
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values
(7369,'SMITH','CLERK',7902,STR_TO_DATE('17-12-80','%d-%m-%Y'),800,null,20);
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values
(7499,'ALLEN','SALESMAN',7698,STR_TO_DATE('20-02-81','%d-%m-%Y'),1600,300,30);
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values
(7521,'WARD','SALESMAN',7698,STR_TO_DATE('22-02-81','%d-%m-%Y'),1250,500,30);
Query
No
Purpose Tables involved SQL Correct Execution
1 Show how many projects each
member of staff is working on
1 table – Staff_proj Select count(*) -------
-
Yes/No
2 To reward long term staff –who
have been with the company over
10 years
1 table - Staff Date operations –
current date (System
Date), Date Staff
joined, etc, ordered
by
Yes/No
3 Staff who have worked on
projects sponsored by a specific
client
All 4 tables 3 joins, etc Yes/No
4