As per the specs
Guide to Project for Supervisors ITECH 1006 - Database Management Systems Assignment 1 Overview Your task is to create an ER diagram and a relational schema for a company. 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. Please refer to the marking criteria to ensure you address all requirements. Timelines and Expectations Percentage Value of Task: 20% Due: 5pm, Friday of week 7 of Semester 3, 2017 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 the Great Southern Research Institute (GSRI), who runs a research centre in Sydney. GSRI conducts medical research projects for many different clients in industry and government. GSRI staff members undertake projects for clients. Each project incurs expenses, and each project has income, which are payments that may come from clients, government or from donations. Each project has associated documents, which are of different types, for example research papers, project proposals, interim reports and others. 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. · GSRI employs many staff members, keeping track of their unique ID, first name, last name, phone number, address and email, as well as tax number, job title and weekly rate of pay. · GSRI’s clients have a unique ID, name, address, phone, email and contact name (this is needed if the client is a company), and belong to a client type. · Client types have a name and description. · Projects have a unique ID, name, description, start and end dates, progress (showing % completion) and a total project Value. Additionally, each project has a project team leader, whose name and email is recorded. The project lead may not be a GSRI staff member, so we do not need to record the staff ID. · Each project has one or more expenses, which are recorded with the amount, date, Payee and a description of the expense. · GSRI uses several different document types, which have a name and a description. · Each project has one or more documents. Each document belongs to a document type, has a unique ID, name (the document title) and the author’s name. Some documents reference another document (for example, a project final report might refer to a project proposal). · GSRI receives payments for projects, where each project can receive many different payments. For each payment, GSRI has to record the amount and date paid. Depending on the payment source, specific details are recorded, as specified below: · If the payment is from a client, then the client’s ID is recorded as well as the reference number (alphanumerical) supplied by the client. · If the payment is from a government grant or contract, the name of the government department and the government contract number are recorded. · If the payment is a donation, then the donor’s name, address and email are recorded. · Each project has one or more clients, and each client can be part of many projects. Sometimes, clients are added to an existing project, or cease to be involved in a project, so for each client participation in a project, the start and end dates are recorded. Notes can also be recorded. · One or more staff members are allocated to a project, and a staff member may work on one or more projects in different roles. A staff members may work on a project for the whole duration or part of it, therefore the start and end date for a staff member’s participation in a project is recorded, and notes. 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 list of assumptions underlying your ER diagram. · 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, and any restrictions or validation to apply. 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. 2 · Page numbers included in report2 · Index giving page numbers of various sections2 · Overall presentation of the report2 · Full APA referencing of all materials used and full disclosure of assistance from all sources including tutors and other students.2 10 2. E-R diagram · Completeness of diagram12 · Correct notation and convention used8 · All assumptions clearly noted8 · Primary and foreign keys10 · Resolution of many to many relationships12 50 3. Normalization · All entities and relationship in appropriate normal form10 · Discussion of normalisation for all entities and relationships5 · Appropriate interpretation of each normal form, arguments for leaving the schema in the normal form you consider optimal. 5 20 4. Conversion of E-R diagram to relational schema · Correct standards, conventions and notation used2 · Primary keys used2 · Foreign keys correctly identified including parent entity6 · Schema is a correct translation of the E-R diagram submitted with appropriate tables, columns, primary keys, and foreign keys. 6 · Types and restrictions on attributes given4 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-and-study/online-help-with/plagiarism. CRICOS Provider No. 00103D Assignment Template.docx Page 1 of 3 CRICOS Provider No. 00103D Assignment Template.docx Page 2 of 3