You are to create an ER diagram, and a relational schema for a small business. You need to provide a discussion of normalization including the normal form that each entity is in, a list of relationships with all table names, primary and foreign keys, and attributes indicating their type and purpose.
You have been commissioned to create a database for ArtScapes, a new company that runs exhibitions in different locations in Australia. Their goal is to sell tickets to these art exhibitions to the public, promote featured artists and help them sell works of art during the exhibitions. All ArtScape exhibitions are organised and run from the company’s head office in Sydney, but exhibitions can be in any city or town. It is company policy to only run one exhibition at any one time, and to supply their own staff. Each exhibition has a name and a theme. Artists and their works are chosen for each theme and invited to participate. Artists are not paid for participating, but they receive the proceeds (if any) from sales of their artwork. Artscape receive a 10% commission from these art sales and the admission fees from exhibition visitors. They pride themselves for showing outstanding pieces of art from known artists and promising new ones. The following business rules and information gathered about the current business activities will allow you to derive entities. Your submission is not expected to have many to many relationships left
unresolved. You may add entities or attributes as you see fit. Assumptions can be made to include further entities and their relationships, but referential integrity and normalization processes must be adhered to. Reasons should be given for any relation that is not maintained in 3NF.
Exhibitions (run by ArtScape) are referenced by a unique exhibition number, they have a name, a theme, a location, a website address, and start and end dates for the exhibition.
ArtScape employs many staff members, keeping track of their unique IDs, names and contact details, as well as tax number, job title and daily rate of pay. Most staff members are assigned to a supervisor, who is a member of staff as well.
Several staff members are assigned to every exhibition. ArtScape ensures that at least some experienced staff are assigned to every exhibition, so it must keep a record of staff assignments with the role that the staff member had during the assignment. Staff may be assigned to an exhibition for all or part of its duration, so the assignment start and end dates need to be recorded for payroll purposes.
To assist in staff training, every staff role has a name and detailed task description.
When visiting an exhibition, visitors must supply first name, last name, email, phone number and address. ArtScape records these details for each visitor, together with a unique number, to allow notification of future exhibitions.
Each visitor can belong to one of several visitor types, such as: Junior, Standard, Concession Card etc. Each unique visitor type has a name, description and the entry fee.
ArtScape has a registry of artists. For every artist, there is a unique number, first name, last name, date of birth, address, phone number and email address, as well as a biography for the artist and the artist’s website address. Artists have the option of supplying an artist name.
Every exhibition has many artworks. Every artwork in this inventory has a unique number. Every piece is produced by only one artist, and has a title and description. There are three types of artworks: photographs, sculptures and paintings.
For all exhibited sculptures, height, width and length is recorded, as well as the weight.
For paintings, the width, height and artist medium is recorded.
For photographs, there must be a record of the camera used and the location where the photo was taken.
Some artworks are for sale, so inventory items have a forsale attribute. If an item is for sale, this must be marked with “Y” and a sale price is supplied by the artist
Visitors can purchase works marked for sale during the exhibition. ArtScape records sales invoices. Each invoice can have one or more purchases. Most artworks are unique (for example most paintings), but some photographic prints have multiple numbers, so the quantity as well as the inventory number must be recorded.
Your submission should include:
An ER Diagram with all entity names, attribute names, primary and foreign keys, relationships, cardinality and participation indicated. All many to many relationships should be resolved.
A discussion of normalization including the normal form that each entity is in and why that is optimal. Also a discussion of how normalization was achieved for that entity.
A list of relationships with all table names, attributes, primary and foreign keys indicated as per the conventions given in the lecture slides (ie entity/table names in capitals, attributes as proper nouns, primary key underlined and foreign keys in italics.)
A database schema indicating the type and purpose of all attributes.