: to give you practical experience in using entity-relationship

ISY1002 (ISY103) Database Management for Business

2018.1 Assignment 1 (Individual Assignment): Entity-Relationship Modelling

Total Marks: 30

Assessment Weighting: 20%

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 hard copy within the first 15 minutes of your tutorial in Week 8 and soft copy by 5 pm on the Friday 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 AIH 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:
   1. all entities, relationships (including names) and attributes;
   2. primary (underlined) and foreign (italic) keys identified;
   3. cardinality and participation (optional / mandatory) symbols; and
   4. 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:
   1. relation names;
   2. attribute names;
   3. primary and foreign keys identified; and
   4. for each relation the level of Normalisation achieved, and for any not to Third Normal Form, explain why.
   5. 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:
   1. table names,
   2. column names and data types (for MySQL or Oracle)
   3. 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:

b. Relational data structures that translate your ER diagram:

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 integer primary key; foreign key reference to Book.BookID

LoanDate date primary key; Format: dd/mm/yyy

Due date

Note that appropriate data types should be selected for MySQL or Oracle.

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 Schema” 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 College policy and will receive an automatic zero ‘0’ mark for the assignment.

ISY1002 (ISY103) Database Management for Business

Marking Schema 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 or APA format			1 1 1			/3
Total						/30
Weight						/15%
Comments / Feedback:

End of the document

• • Assignment status: Resolved by our Writing Team
  • Source@PrimeWritersBay.com

GET THIS PAPER COMPLETED FOR YOU FROM THE WRITING EXPERTS  CLICK HERE TO ORDER 100% ORIGINAL PAPERS AT PrimeWritersBay.com

• • • • NO PLAGIARISM