代做program、Python，Java程序代写

日期：2023-11-19 12:51

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