This assessment item relates to the course learning outcomes numbers 1, 2, 4 and 5 as stated in page 3 of the course profile. Assessment task Important points • You must work individually on this assignment. • The model solution to this assignment will be released approximately TWO (2) weeks after the due date. • Assignments that are submitted after the release of the model solution will receive zero. • Please read the case study in the document one more time as there are additional bold texts that serves to clarify points for the purpose of Assignment 2. • You must state in your assignment (at the end under the heading Acknowledgements is a good place) all materials and sources you used. This may be as simple as 'This assignment was completed using only the COIS 20026 course materials' if that was all you used. • Extension requests (by filling in an online form) should be submitted before the assignment due date. A penalty of 5% of the total mark per calendar day will apply for late submissions without an extension, or zero if submitted after the release of the model solution. • Before submitting your assignment, review the marking criteria as shown on the last page of this document. Ensure that all the criteria are addressed in your submission. Relational database implementation You must implement the entity relationship model provided in the model solution to Assignment 1. The model solution will be released on the course website approximately TWO (2) weeks after the Assignment 1 due date. The recommended tool for this assignment is Microsoft Access, version 2003 or later. One reason for recommending Microsoft Access is that your course lecturer will be able to provide support in the use of this tool. If you choose to use a different DBMS, then there is no guarantee that your lecturer will be able to provide support. Note: If you choose not to use Microsoft Access, you must contact your lead lecturer as soon as possible before you start work on the assignment. The remainder of this document assumes you are using Access. In implementing the ERD provided, you must complete the following tasks:• Map the ERD into a set of relations in at least Third Normal Form (3NF). You must ensure that your relations meet 3NF. There is no need to show your working. • Select any two (2) of your relations from the previous step and for each of them: • List all the functional dependencies present in the relation • Demonstrate that the relation meets Third Normal Form (3NF), for example: STUDENT (StudentID, Name, Address, DateOfBirth) The primary key is StudentID; this is the only candidate key since Name is not guaranteed to be unique (two different students may have the same name). Address is also not guaranteed to be unique. There will be one student name per Student ID, since only one name is recorded for each student. There will be one address per Student ID; even though a student may have multiple address (e.g. a home address and a work address), the case study states that there is only a need to record the home address. There will be only one date of birth per Student ID. This means that Student ID functionally determines every other attribute in the table; therefore there are no repeating groups. There is a single simple candidate key, therefore no partial dependencies are possible. Name cannot be used to functionally determine any other attribute in the table since two different students may have the same name; likewise for address and date of birth, therefore there are no transitive dependencies in the table. Therefore it meets the requirements of first, second and third normal form. • Create all the relations in a Microsoft Access database. Consider each attribute in every table and make appropriate choices regarding data types and sizes, indexes, required/not required, and validation rules. Your choices should be appropriate for each attribute and should support data integrity. (Note: see the Data Integrity section below for specific data integrity requirements.) • Create relationships as appropriate. Enforce referential integrity for all appropriate relationships in the database. Explore the possibility of cascade update and/or delete options. • Review the default indexes created by Access for each table. You should ensure that the indexes follow the guidelines given in the set textbook. • Populate the database with sample data. You must include sufficient sample data to test your queries and report(s). You should aim for at least 5 or 6 rows in every table. Please note that the expected result of query questions may depend on the actual sample data populated. Data integrity You are required to implement integrity constraints within the database to enforce the following requirements: For the table Coach: • All fields must be entered. • BlueCard number must be an 6-digit number. • Contact phone can't be null. For the table PlayedIn: • All fields must be entered. • The only valid values to indicate the match outcome must be "won", "lost", or "draw". • Score can't be null.For the table Earning: • All fields must be entered. • Prize must be not less than $100. Information requests Create queries to answer the following information requests. Note: Do not use the Access query builder (QBE) to create your queries – you should type the queries manually using SQL view. Although it is optional to justify the effectiveness of your SQL statement for the request, the marking process of MS Access queries for information requests may also consider your justification of the effectiveness for your queries in the implementation report if you provide them. (Expected results using sample data for queries depend on your actual sample data populated in the database. The following results of queries are only examples. It is important that your implementation must reflect the correct business logic.) 1. How many matches did each tournament have? Show the tournament's title and start date, as well as the number of matches of each tournament