ITECH 1006 - Database Management Systems
Assignment 1
Overview
You are to create an ER diagram, and a relational schema for a small business. You need to provide a discussion of normalisation 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.
Timelines and Expectations
Percentage Value of Task: 20%
Due: 5pm Thursday the 5th January, 2017 (week 7)
Minimum time expectation: 12 hours
Learning Outcomes Assessed
The following course learning outcomes are assessed by completing this assessment:
• K4. design a relational database for a provided scenario utilising tools and techniques including ER diagrams, relation models and normalisation
• K5. describe relational algebra and its relationship to Structured Query Language (SQL);
• A1. design and implement a relational database using a database management system;
Project Specification
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 normalisation 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.
Submission
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 normalisation including the normal form that each entity is in and why that is optimal. Also a discussion of how normalisation 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.
The assignment is to be submitted via the Assignment 1 submission box in Moodle. This is to be found in the Assessments section of the course Moodle shell.
Marking Criteria/Rubric
Assessment Criteria and Marking Overview Tasks Marks
1. Presentation
• Cover page indicating student name and number and tutor name.
• Page numbers included in report
• Index giving page numbers of various sections
• Overall presentation of the report
• Full APA referencing of all materials used and full disclosure of assistance fro sources including tutors and other students. 2
2
2
2 m all
2 10
2. E-R diagram
• Completeness of diagram
• Correct notation and convention used
• All assumptions clearly noted
• Primary and foreign keys
• Resolution of many to many relationships 12
8 8
10
12 50
3. Normalization
• All entities and relationship in appropriate normal form
• Discussion of normalisation for all entities and relationships
• Appropriate interpretation of each normal form, arguments for leaving the s in the normal form you consider optimal. 10
5
chema
5 20
4. Conversion of E-R diagram to relational schema
• Correct standards, conventions and notation used
• Primary keys used
• Foreign keys correctly identified including parent entity
• Schema is a correct translation of the E-R diagram submitted with appropriate tables, columns, primary keys, and foreign keys.
• Types and restrictions on attributes given 2 2
6
6 4 20
Total 100
Feedback
Feedback will be provided via the marked rubric with comments handed out in the tutorial 2 weeks after the submission date. The marks will also be available on FDL Marks at this time.
Plagiarism:
Plagiarism is the presentation of the expressed thought or work of another person as though it is one's own without properly acknowledging that person. You must not allow other students to copy your work and must take care to safeguard against this happening. More information about the plagiarism policy and procedure for the university can be found at http://federation.edu.au/students/learning-andstudy/online-help-with/plagiarism.