Unit ITICT107A Introduction to Databases
Assessment Type Database Design
Assessment Number 4
Assessment Name
Weighting Assignment 1 – Database Design
15%
Due Date/Time Week 8
Assignment 1 is on the database modelling of a database of your own choice / prescribed mini-case study with ER diagrams, keys and functional dependencies. Upload by Sunday 20th September 2020 via Moodle Turnitin 23:55 (AEST)
Assessment
Description You are required to do the modelling for a database of given the following design requirements.
Aim: to give you practical experience in using entity-relationship and relational modelling techniques.
Assignment 1 – Database Modelling & Development of an ER Diagram and Schema Course Objectives:
This assessment task focuses on the following course objective:
• Use entity-relationship diagrams to design a relational database. Due Date:
Submit a soft copy by 23:55 on the Sunday of Week 8. (Refer to unit outline on Moodle site for further dates and times).
Project Specification
You have been commissioned by Ronda from Express Media to design a database to assist them with managing their magazine advertising orders. Express Media is a publishing company that sells advertising space to clients in magazines that are published every two months. You are only required to provide the design of the database at this time.
The Advertisers database will assist sales representative staff to maintain a record of advertisement order details and bookings that flow through to invoicing. The system will also allow managers to produce reports on sales revenue and sales history.
The main purpose of the database is to keep track of orders for advertising space. For advertising orders they would like to store the order date, purchase order number, initials of the sales representative handling the order, special instructions, and copy notes. For each order Express Media would like to store the order ID, invoice date, magazine issue description, cost price, page size, shape, colour, position, and production notes.
Express Media would also like to identify magazine issues in terms of publication name, issue year, and issue month.
Express Media would like to keep a record of payments for orders.
Payments can be made in full or a number of payments may be made against an order. Payment information should include the following: payment amount, payment date, cheque number, credit card details (which include credit card type (for example Visa, American Express, & Diners Club), credit card number, credit card name, and credit card expiry month and year), payment method (where payment method may be: cash, cheque, direct debit, CC Debit, or Direct Credit, and Other). Express Media would like to maintain a list of payment types to produce a report that summarises payments for each type.
Express Media would like to store details related to each advertiser client Advertiser client details should include a company name, website address, business phone number, fax number, advertising contact details (including first name, last name, telephone, & fax number), and address of premises/offices (including street name, city, state, and post code). Express Media would also like to keep a record of the following for each advertiser:
• Enquiry / Editorial Address (including street address, city, state, and postcode)
• Invoicing Address (including street address, city, state, and postcode)
• Design agency (company name, contact name, phone, & email address)
• Advertising agency name
Express Media would like to record the following details for the advertising agencies that take care of advertising needs for some of the advertisers. The agencies are companies or consultants that are hired by the advertisers to design and place advertisements on the magazines that Express Media publishes. For each advertising agency they would like to store their business name, contact name, address details (including their street address, city, state, and postcode), and phone numbers (mobile, business, fax, and other). Express Media would like to be able record the percentage commission that is paid to the advertising agency for those advertisers that are handled by an agency.
Express Media would also like to maintain a list of suppliers which provide services such as print design and production. Supplier details should include the following: company name, contact name & title, address (including street address, city, state, & postcode), phone number, web address, email address, and a comment.
In addition Express Media would also like to store information about their staff in the database. Staff may be employed full-time, part-time or on a casual basis. Express Media needs to store contact information for the staff (address and phone), along with their Tax File Number (TFN). Some staff members are supervisors of other staff members and this also needs to be identified. Express Media would also like to store up to three email addresses for each staff member. The database should be designed so that only HR staff have access to confidential details such as the TFN & salary.
Express Media staff who work in HR and sales have access to this database and they will be uses of the database. Express Media would like to store information regarding their database users. For users they would like to store the user first name & last name, login name & password, email address, and user level (which specifies which level of access privileges the user should have).
Express Media understands that they may not have provided you with sufficient information. If you need to make assumptions about their organisation please ensure that you record these.
Assignment Submission
You should produce a single .pdf file, created using Word, containing all of the requirements as specified within this document. Your lecturer will provide details of how the file is to be submitted within lectures.
The design document should contain:
1. A completed copy of the TAFE Higher Education Assignment Coversheet.
2. An appropriate title page that includes an acknowledgement of all students you have spoken to about the assignment.
3. A table of contents and automatically generated page numbers.
4. An entity relation diagram that models the solution which includes:
a. all entities, relationships (including names) and attributes;
b. primary (underlined) and foreign (italic) keys identified;
c. cardinality and participation (optional / mandatory) symbols; and
d. assumptions you have made, e.g. how you arrived at the cardinality / participation for those not mentioned or clear in the business description, etc.
The E-R should be completed using the standards of this course (crow’s feet or UML).
5. Relational data structures (shown in Standard Notation Format) that translates your E-R diagram which includes:
a. relation names;
b. attribute names;
c. primary and foreign keys identified; and
d. for each relation the level of Normalisation achieved, and for any not to Third Normal Form, explain why.
e. The data structures should be shown using the standards of this course (crow’s feet or UML).
6. A relational database schema that translates your relational data structures which includes:
a. table names,
b. column names and data types (for MySQL or Oracle)
c. primary and foreign keys identified
7. Write a page that describes your experience designing the database. You can discuss any challenges / difficulties that you experienced or solutions that you found. Comment on any limitations and / or strengths of your database design. Comment on whether your database meets all the system requirements as specified in the project specification. Include an acknowledgement of all students you have spoken to about the assignment.
8. A bibliography, in Harvard format, containing all resources used to complete the assignment. If no resources have been used please indicate this appropriately.
Example of submission
Here is an example to show content to be submitted.
Example: A student can borrow many books from library and a book can be borrowed by many students.
a. An entity relation diagram that models the problem which includes:
STUDENT(StudentID, FirstName, Surname, StreetName, Suburb, Postcode)
BOOK(BookID, Title, Author, Publisher, Edition, ISBN, Price, YearPublished)
BOOKLOAN(StudentID, BookID, LoanDate, Due)
Assumptions:
• A student can borrow many books but some students may not borrow any book.
• A book can be borrowed by many readers but some books may never be borrowed.
NOTE: Only state assumptions if aspects of your design require support, explanation, or justification.
c. A relational database schema
Table Name Field Type Description
STUDENT StudentID integer primary key
FirstName varchar(30)
Surname varchar(30)
BOOK BookID integer primary key
Title varchar(5)
Author varchar(30)
Publisher varchar(30)
Edition integer
ISBN varchar(20)
Price double(6,2)
YearPublished integer
BOOKLOAN StudentID integer primary key; foreign key reference to
Student.StudentID
BookID
Book.BookID integer primary key; foreign key reference to
LoanDate date primary key; Format: dd/mm/yyy
Due date
Note that appropriate data types should be selected for MySQL or Oracle.
Detailed
Submission
Requirements Your report for your modelling assignment is to be presented as a soft copy. All assessment activities that are handed in must be presented to a professional standard and must include:
1. A soft copy to be submitted to the designated lecturer or nominated alternative by the due time and date. It must have a title page which includes the student’s name and ID number, title of assignment and a signed statement that the work is exclusively the student’s own work and that there has been no plagiarism or collusion. Reports without a coversheet will not be accepted.
2. A footer to be shown on every page that includes student’s name, ID number and page #.
3. A soft copy of your report to be submitted on Moodle. The same assignment deadlines apply to this submission format. E-mailing of your assignment will be accepted only after consultation with the tutor. If emailing, through arrangement with the tutor, send the document as an e-mail attachment in MS Word or PDF format. The subject line in the e-mail must include student name. When sending e-mails please request an automated receipt. Where there is doubt as to whether the assessment activity was submitted on time, no claims for timely submission will be accepted if there is no receipt.
Submissions not clearly identified with the author’s details as outlined in the points above will not be marked and may result in a 0 grade for that activity.
Misconduct PLAGIARISM AND COLLUSION Any submitted work that contains plagiarism or collusion will be reported to the appropriate authorities according to the TAFE policy and will receive an automatic zero ‘0’ mark for the assignment.
• The assessment will be submitted through Turnitin via your unit page on Moodle.
• Turnitin is plagiarism software, which will identify if you have copied information and included it in your assessment.
• Copying information from others (i.e. websites, partner company information, or other students etc.) without the acknowledging the author is classified as misconduct.
• Engaging someone else to write any part of your assessment for you outside of the group work arrangement is classified as misconduct.
• To avoid being charged with Misconduct, students need to submit their own work and apply APA or Harvard Style Referencing (ask your lecturer (https://courses.highered.tafensw.edu.au/mod/page/view.php?id=48) if you do not know what this means, or you need assistance applying it).
• The TAFE misconduct policy and procedure can be read on the TAFE website ((https://www.tafensw.edu.au/about/policies-procedures/higher-education).
• Use the TAFE referencing guide accessible on Moodle.
Late Submission Deadlines: There will be NO extensions except where Calendar rules apply. Deadlines must be met, unless an acceptable medical certificate covers 25% or more of the lead-time between the assignment and the deadline. Other absences must be accounted for by substantiating documentation, for example a police accident report or statutory declaration. Any assessment activities missed for medical (or other acceptable substantiated) reasons must be completed by a new deadline that will be assigned in negotiation with the lecturer.
The penalty for Late Submission of assignments is 5% of the total marks for each day (or part thereof, including weekends) the assignment is late. For assignments received more than 7 days after the due date, the assignment will be assessed, but a zero mark will be awarded.
Electronic failure causing delay: In most work submissions you have a final deadline but it is recommended that you submit well before this time. Timely submission is your responsibility – last minute technical disaster will not be accepted as a reason for late submission. You are expected to have discipline in time management, to make back-ups, and always have the back-ups available for submission.
• Any assessment submitted past the specific due date and time will be classified as Late.
• Any Late submission will be subject to a reduction of the mark allocated for the assessment item by 5% per day (or part thereof) of the total marks available for the assessment item. A ‘day’ for this purpose is defined as any day of the week including weekends. Assignments submitted later than one (1) week after the due date will not be accepted, unless special consideration is approved as per the formal process.
Special consideration Students whose ability to submit or attend an assessment item is affected by sickness, misadventure or other circumstances beyond their control, may be eligible for special consideration. No consideration is given when the condition or event is unrelated to the student's performance in a component of the assessment, or when it is considered not to be serious.
Students applying for special consideration must submit the form within 3 days of the due date of the assessment item or exam. The form can be obtained from the TAFE website
(https://courses.highered.tafensw.edu.au/mod/page/view.php?id=48) or on-campus P.4.32.
The request form must be submitted to Admin Office. Supporting evidence should
be attached. For further information please refer to the Higher Education
Assessment Policy and associated Procedure available on
(https://www.tafensw.edu.au/about/policies-procedures/higher-education).
Assessment Criteria 1. How clear and well organised your report is presented.
2. E-R diagram. Adherence to the unit standard, assumptions made,
consistent naming standards, inclusion of correct primary and foreign keys, appropriate entities, relationships, and attributes.
3. Please refer to the “Marking Guide” which is provided at the end of this specification to see the distribution of marks.
4. Normalisation. Appropriate interpretation of each normal form, arguments for leaving the schema in the normal form you consider optimal.
5. Conversion of E-R diagram to relational schema. Schema is a correct translation of the E-R submitted with appropriate tables, columns, and primary keys.
Submissions not clearly identified with the author’s details as outlined in the points above will not be marked and may result in a 0 grade for that activity.
PLAGIARISM AND COLLUSION Any submitted work that contains plagiarism or collusion will be reported to the appropriate authorities according to the TAFE policy and will receive an automatic zero ‘0’ mark for the assignment.
MARKING CRITERIA AND STANDARDS: ASSESSMENT 4
Criteria Unsatisfactory Pass Credit Distinction High Distinction
database modelling, using an ER diagram
The provided ER diagram does not attempt to model a database needed to solve the given problem. Includes some essential entities with some attributes to model the solution database with somewhat meaningful relationships. Primary keys, foreign key and constraints
identified. The model presented in a clear way that is easier to comprehend. Includes most of the essential entities with sufficient attributes to model the solution database with somewhat meaningful relationships. Primary keys, foreign key and constraints
identified. The model presented in a clear way that is easier to comprehend. Includes most of the essential entities with required attributes in ER diagram to model the solution database with meaningful relationships. Primary keys, foreign key and constraints identified. The well designed model presented in a clear way that is easier to comprehend. Includes all the essential entities with required
attributes in ER diagram to model the solution database with meaningful relationships. Primary keys, foreign key and constraints identified. The well designed model presented in a clear way that is easier to comprehend.
Relational structures Did not attempt to create a relevant relational data structure in 3NF based on the model using correct notation. Create a relational data based on the ER model using correct notation. Some assumptions are provided. Create a relational data based on the ER model using correct notation. Some assumptions are provided. Create a relational data structure in 3NF based on the ER model using correct notation. All the assumptions are well documented. Create a relational data structure in 3NF based on the ER model using correct notation. All the assumptions are well documented.
Relational
Schema Did not include all the required tables and attributes. Include some of the tables and
formatted
fields, appropriate data types, primary keys and foreign keys. Presented using appropriate notation. Include most of the tables and
formatted
fields, appropriate data types, primary keys and foreign keys. Presented using appropriate notation. Include most of the tables and
formatted
fields, appropriate data types, primary keys and foreign keys. Presented using appropriate notation. Include all the tables and formatted
fields, appropriate data types, primary keys and foreign keys. Presented using appropriate notation.
8
ITICT107A Introduction to Databases
Marking Guide for Individual Assignment
Student Name: Student
Number:
Requirements Allocated Mark Given Mark Total
ER Diagram
Entities
• Includes essential entities
Attributes
• Includes essential attributes
Relationships
• Relationships make sense Keys
• Primary and foreign keys identified Constraints
• Includes names, cardinalities and participation
• Correct and consistently used Design
• Simplicity & elegance
Presentation & Formatting
• Clear and easy to read
5
2
2
2
1
1
1
1
/15
Relational Structure
Translation from ER to Relational Data Structure
• All entities and relationships transferred
• Use of correct notation
• Table names and attributes sensible Normalization
• All entities in 3NF; if not then reason provided and is reasonable
Assumptions
• Reasonable assumptions provided
1
1
1
1
1
/5
Relational Schema
• Includes all tables and attributes
• Appropriate data types
• Primary keys and foreign keys identified (including the table and key that the key related to) and clearly presented
• Formatting of fields included e.g. dates
• Appropriate notation used
1
1
1
1
1
/5
Personal Report
• Clear description of strengths & weaknesses of design
2
/2
Documentation
• Completed according to instructions
• Includes assignment coversheet, title page including list of acknowledgements, table of contents, page numbers
• Bibliography included in Harvard format
1
1
1
/3
Total /30
Weight /15%
Comments / Feedback:
9