Data Modelling | My Assignment Tutor

Assignment 1ITECH 2004 Data Modelling Name ERD and SQL Purpose The purpose 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 Expectations Percentage Value of Task: 25% (70 marks). Due: Week 7 – Friday May 7, 2021, 4:00 pm. Minimum time expectation: This task will take approximately 20 hours to complete. Learning Outcomes Assessed The following learning outcomes are assessed by completing this assessment: K4, K5, S1, S2, S3, A1 and A2. Assessment Details Background Kids e-Books is a company that specialises in loaning children’s e-books to the general public, schools, kindergartens and play groups. Kids e-Books can own several copies (BOOKCOPY) of each book (BOOK). For example, Kids e-Books may have 10 copies of the book: Where is the green sheep? In the database, the book: Where is the green sheep? would be one BOOK, and each copy would be a BOOKCOPY. A borrow transaction (BORROW) involves one or more copies being lent to a member (MEMBERSHIP). A copy can be loaned many times over its lifetime; therefore, there is an M:N relationship between BORROW and BORROWCOPY. DETAILBORROW is the bridge table to resolve this relationship. Requirements 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 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. For each entity, create some example data for that entity. Include 5 rows of data for each entity.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.Write the SQL code to insert your data from (2) into the SQL table structures Documentation Requirements Prepare a business style report which contains the following: ER diagram based on defined scenarioExample dataA copy of your SQL codeStatement 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. Report file name: ITECH2004_Assignment1_Report_yourname_studentID 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 writing techniques, see: A suggested case study report structure is as follows. Title page Executive summary Table of contents 1.0 Introduction 2.0 ERD for: [insert company name] 3.0 Example data 4.0 SQL statements 4.1 Create tables 4.2 Insert data 5.0 Conclusion 6.0 Bibliography A copy of the SQL statements, file name: ITECH2004_Assignment1_SQL_yourname_studentID.txt Academic Presentation Assessment should be presented in accordance with instructions in writing guides, layout, and appearance guidelines. Helpsheets: guides: and appearance: Referencing You many require help from textbooks, websites, YouTube videos etc., these should be presented in APA format: See FedCite – referencing tool: Submission The assignment is to be submitted via the Assignment 1 submission box in Moodle. This can be found in the Assessments section of the course Moodle shell. The report file described above EITHER as a MS word file or a PDF. If you are using MacOS, please submit as a PDF.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. Feedback This assessment will be marked by the course lecturer and/or tutors. Feedback and marks will be provided individually 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 at: Please 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: Marking Criteria/Rubric Assessment CriteriaMarking ScalePoor Excellent1 5Presentation and referencing0ER diagram – completeness of diagram0– correct notation and convention used0– all assumptions clearly noted0– primary and foreign keys0– resolution of many to many relationships0Normalisation – discussion of normalisation for all entities and relationships0– appropriate interpretation of each normal form, arguments for leaving the schema in the normal form you consider optimal0Example data – example data provided for each entity/table0SQL statements – correct translation of the ER diagram submitted with appropriate tables0– types and restriction on attributes given0– primary keys correctly implemented0– foreign keys correctly implemented0– insert statement correctly implemented0Total Mark [70 marks]** Expression is faulty **Total Worth [25%]** Expression is faulty **


Leave a Reply

Your email address will not be published. Required fields are marked *