Toys 4 Uis acompanythat specialises intoy hirefor parties, kindergartens andplay groups. Theyalso offer equipment for infants and babies forhire.Toys 4 Ucan own several copies (TOYCOPY) of eachtoy(TOY). For example, astore may have 10 copies of thetoyRocking Horse. In the database,Rocking Horse would be oneTOY, and each copy would be aTOYCOPY. Ahire transaction (HIRE) involves one or morecopiesbeinghired to a member (MEMBERSHIP). Acopycan behiredmany times over its lifetime; therefore, there is an M:N relationship betweenHIRE andTOYCOPY. DETAILHIRE is the bridge table to resolve this relationship.
Requirements
1.Design your ER Diagram with all entity names, attribute names, primary and foreign keys, relationships, cardinality and participation indicated. You may add entities or attributes as you see fit. You will need to normalise all of yourentities,to resolve anymany to many relationships.•Your attributenames, primary and foreign keysshould be indicated as per the conventions given in the lecture slides (i.e. entity/table names in capitals, attributes as proper nouns, primary key underlined and foreign keys in italics).•All many to many relationships should be resolved, and you should include 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.2.For each entity, create some example data for that entity. Include 5 rows of data for each entity.3.Write the SQL code to create the table structuresfrom your developed ERD. The structures should contain the attributes specified inyourERD. Use data types that are appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated byyour ERD.4.Write the SQL code toinsert your data from (2) into theSQLtable structures.
Documentation requirements
1.Prepare a business style report whichcontains the following:a).ER diagrambased on defined scenariob).Example datac).A copy ofyour SQL coded).Statement of any resources used. These includes full disclosure of assistance from all sources including tutors and other students.Full APA referencing of any resources used.
A suggestedstructure for the above report is provided.You can useit as a guideline forpresenting your businessreport.For assistance in report writing techniques, see:https://federation.edu.au/__data/assets/pdf_file/0018/190044/General-Guide-to-Writing-and-Study-Skills.pdf , page 36.
Report file name:ITECH2004_Assignment1_Report_yourname_studentID
|
Title page Executive summary Table of contents 1.0Introduction2.0ERD for: [insert company name]3.0Example data3.0?SQL Statements 3.1?Create tables 3.2?Insert data 4.0?Conclusion 5.0?Bibliography
|
2.A copy of theSQLstatements, file name:ITECH2004_Assignment1_SQL_yourname_studentID.txt
Academic Presentation
Assignment should be presentedin accordance with:
•General Guide to Referencing:https://federation.edu.au/__data/assets/pdf_file/0020/313328/FedUni-General-Guide-to-Referencing-2016ed.pdf•General Guide to Writing and Study Skills:http://federation.edu.au/__data/assets/pdf_file/0018/190044/General-Guide-to-Writing-and-Study-Skills.pdf•Guide to Layout and Appearance:https://federation.edu.au/__data/assets/pdf_file/0017/190043/General-Guide-to-Layout-and-Appearance.pdf
Submission
The assignment is to be submitted via the Assignment1 submission box in Moodle. This can be found in the Assessments section of the course Moodle shell.
1.Thereport file described above EITHER as a MS word file or a PDF. If you are usingMacOS, please submitas a PDF.2.Thedatabase file, as described above.
Please note that the SQL files must be either .txt or .sqlso they can be run If they are in a word or pdf file there will be extra characters that will prevent the files from running without errors andwill cost marks.
Marking Criteria/Rubric
| Assessment Criteria | Marking Scale |
| Poor?Excellent | |
| 1 ? 5 | |
| Presentation and Referencing
•Overall presentation of the report. |
0 |
| ER Diagram
•Completeness of diagram |
0 |
| •Correct notation and convention used | 0 |
| •All assumptions clearly noted | 0 |
| •Primary and foreign keys | 0 |
| •Resolution of many to many relationships | 0 |
| Normalisation | 0 |
| •Discussion of normalisation for all entities and relationships | |
| •Appropriate interpretation of each normal form, arguments for leaving the schema in the normal form you consider optimal. | 0 |
| Example data
•Example data provided for each entity/table |
0 |
| SQL Statements | 0 |
| •Correct translation of the ER diagram submitted with appropriate tables | |
| •Types and restrictions on attributes given | 0 |
| •Primary keyscorrectly implemented | 0 |
| •Foreignkeyscorrectly implemented | 0 |
| •Insert statements correctly implemented | 0 |
| Total Mark?[70 marks] | 0.0 |
| Total Worth?[25%] | 0.0 |
Feedback
Feedback and marks will be provided in Moodle. Marks will also be available in FDL Marks.
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 athttp://federation.edu.au/students/learning-and-study/online-help-with/plagiarism
Please refer to theCourse Description for information regarding late assignments, extensions, and special consideration. A reminder all academic regulations can be accessed via the university’s website, see:http://federation.edu.au/staff/governance/legal
