59667 – Assignment 1: ERD and SQLOverviewThe purpose of this

SOLUTION AT Australian Expert Writers

1: ERD and of this task is to develop student’s skills in designing an ER diagram (ERD) and implementing this in a relational database for a given case study.Timelines and ExpectationsPercentage Value of Task: 25%Due: Week 7Minimum time expectation: Preparation for this task will take approximately 20 hoursLearning Outcomes AssessedThe following learning outcomes are assessed by completing this :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).S1. Interpret entity-relationship diagrams to implement a relational database.S2. Demonstrate skills in designing and building a database application using a commercially available database management system development tool.S3. Use a query language for data manipulation.A1. Design and implement a relational database using a database management system.A2. Utilise a query language tools and techniques to obtain data and information from a database. DetailsBackgroundToys 4 U is a company that specialises in toy hire for parties, kindergartens and play groups. They also offer equipment for infants and babies for hire. Toys 4 U can own several copies (TOYCOPY) of each toy (TOY). For example, a store may have 10 copies of the toy Rocking Horse. In the database, Rocking Horse would be one TOY, and each copy would be a TOYCOPY. A hire transaction (HIRE) involves one or more copies being hired to a member (MEMBERSHIP). A copy can be hired many times over its lifetime; therefore, there is an M:N relationship between HIRE and TOYCOPY. DETAILHIRE is the bridge table to resolve this relationship.Requirements1. 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 your entities, to resolve any many to many relationships.• Your attribute names, primary and foreign keys should 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 of normalisation including the normal form that each entity is in and why that is optimal. Also, a 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 structures from your developed ERD. The structures should contain the attributes specified in your ERD. 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 by your ERD.4. Write the SQL code to insert your data from (2) into the SQL table structures.Documentation requirements1. Prepare a business style report which contains the following:a). ER diagram based on defined scenariob). Example datac). A copy of your 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 suggested structure for the above report is provided. You can use it as a guideline for presenting your business report. For assistance in report techniques, see: https://federation.edu.au/__data/assets/pdf_file/0018/190044/General-Guide-to--and-Study-Skills.pdf , page 36.Report file name: ITECH2004_Assignment1_Report_yourname_studentIDTitle pageExecutive summaryTable of contents1.0 Introduction2.0 ERD for: [insert company name]3.0 Example data3.0 SQL Statements3.1 Create tables3.2 Insert data4.0 Conclusion5.0 2. A copy of the SQL statements, file name: ITECH2004_Assignment1_SQL_yourname_studentID.txtAcademic PresentationAssignment should be presented in 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 and Study Skills: http://federation.edu.au/__data/assets/pdf_file/0018/190044/General-Guide-to--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.pdfSubmissionThe assignment is to be submitted via the Assignment 1 submission box in Moodle. This can be found in the Assessments of the course Moodle shell.1. The report file described above EITHER as a MS word file or a PDF. If you are using MacOS, please submit as a PDF.2. The database file, as described above.Please note that the SQL files must be either .txt or .sql so 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 and will cost marks.Marking Criteria/RubricAssessment Criteria Marking ScalePoor Excellent1 5Presentation and Referencing• Overall presentation of the report.0ER 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 0Normalisation 0• 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. 0Example data• Example data provided for each entity/table 0SQL Statements 0• Correct translation of the ER diagram submitted with appropriate tables• Types and restrictions on attributes given 0• Primary keys correctly implemented 0• Foreign keys correctly implemented 0• Insert statements correctly implemented 0Total Mark [70 marks] 0.0Total Worth [25%] 0.0FeedbackFeedback and marks will be provided in Moodle. Marks will also be available in FDL Marks.PlagiarismPlagiarism 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/plagiarismPlease refer to the Course 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/feduni-legislation

Order from Australian Expert Writers
Best Australian Academic Writers