Teddington Tennis Club - Essay Example

1. ERD Diagram The ERD Diagram for the database is shown below The ERD diagram is self-explanatory as the tables and their cardinality relationships are well mentioned in the diagram itself. 2. a Customizing data The data was customised to add my roll number (14559981) as man1 of Team A. 2. b Adjust doubles score The double score was adjusted to 5 for the match of man1 (my roll number) and man2 of Team A versus man1 and man2 of Team B 2. c Adjusting Database name The student name and author name were adjusted in the database properties 2. d Saving the database The database file was save with my roll number 3. Create tables with appropriate fields and data and display the data in each table. Identify and implement validation rules in all tables. Name the tables and fields using the Access 2007 textbook naming conventions The following tables are created to score details of the application Members (mID, pID, pType, pCurrent)-> To store details of member data whwere mID- member Id pID – Player Id pType – Membership type (Adult, Child, Family) pCurrent – Whether current year member or not Players(pID, pName, pEmail, pPhno, pAddress, pSex) -> To store player details pId- player ID pName- Player Name rest of the fields are self-explanatory Team(tID, tName, pID) -> To store team details tID – team ID tName – Team Name pID – Player ID Schedule(mID, mForTeam, mAgainstTeam, mSchedule, mYear) -> To store match schedule details mID- match ID mForTeam – For team mAgainstTeam – Against team mSchedule – Week name mYear – Year Name Results(mID, wonby, ForScore, AgainstScore, MatchScore) -> To store results of each match mID- match ID mWonby – Team Name mForScore – won score mAgainstScore – opponent Score mMatchScore– matchtype (mixed, mens, womens) mWonDifference – mforScore- mAgainstScore Player_Match(mID, pID) – To store player details for each match mID – Match ID pID- Player ID The tables and their details can be seen again with the ERD diagram itself: 5. Create a form for a particular night of competition that allows the results to be entered online Since the basic design of how the form should look is given in the excel sheet, there were minimal problems in designing it. But, to decide on the exact methodology for getting the user input was the tedious process. The inputs can be obtained from the user in n number of ways, but the method chosen should be easily understandable to the user as well as give various interpretations of data. Once the method was chosen, the design became apparent. The form basically offers two options for the user. One, to view previous results and two, to enter results online. Initially, the user has to choose on the year of competition, then the week and finally the teams to play for that week. From here the option varies. The user can either view the results, if the year selected is in the past or else can update the results for present and future games. This is done by virtually hiding all the controls present in the form until the user clicks on the appropriate buttons which are ‘View’ and ‘Update’ (the names are self-explanatory). This is done by marking a tag to all these controls and setting their visible property to ‘false’. A sample piece of code is shown below. All these codes are activated using the event procedures that are written for each command buttons. A sample of the click even procedure for ‘View’ Command button is shown below: Once these are decided, the user can select the ‘GO’ button, to view the result input boxes filled with players’ names adjacent to them. Once the user fills and clicks on ‘Save’, th e data gets stored in appropriate tables. The query used to update these values is given below: A screenshot of the design for viewing the results as well as for entering results is shown below: 6. Create a report of all players who are not current financial members and all current financial members who are not players. The creation of the financial report detailing the players who are members for current fiscal and those who are not basically depends on the data available in the two tables namely, ‘Members’ and ‘Players’. These tables contain details regarding the players, their member ids and their membership details. The report is created using the Report Wizard of MS Access which lists out the fields that need to be presented in the report in addition to the variables based on which the report has to be ordered. The report is generated by analysing the value of the ‘Members’ table field column, pCurrent which mentions whether the player is member or not. If the value for a member id is ‘Y’, the player is a member and if ‘N’, the player has to renew is membership. Based on the member ID and Player Id relationship, the player details are obtained from the player table. An example of the report that was generated for the current year is shown below. 7. Create a parameter query to find all members of Team A. The parameter query to find all members of Team A is created using the ‘Create Query’ option of Access. Once it is selected, a dialog box opens, enabling us to select the table for which the details are required. The table selected here is ‘Teams’ table and the field Team Name is selected for which the parameter has to be passed. Once the field names are selected, the datasheet with results is shown. Here the parameter option in Query present in the design tab of Access is chosen to select the name and data type.. Once down the query is stored and the results are displayed. The query created by following the above mentioned method is as follows: And the result is shown is the below given screenshot. 10. Features that enhance the usefulness (functionality) of the application Viewing of Results Another additional feature added to the result form is a feature allowing the user to view previous year results. The user can select the year and the particular week for which results are required and once the ‘View’ button is clicked, the user can view the results similar to how it was expressed in Excel sheet. A visual description of this feature is shown below: Year Wise Team Results Summary Table To increase the dynamic functionality of the application, the team result summary table is added with the feature of providing result summary and performance analysis for previous years too. For this particular reason, the creation of the summary table was done in a form design instead of report generation. This would allow the user to select the particular year for which the results are required and based on the selection a query is run to fetch and display the results. This is actually an enhanced functionality of the challenge that was proposed in question no. 9. The screen shots are given below as an evidence and explanation 11. Features that enhance the ease of use (usability) of the application including presentation and interface design. Since the requirements of the database include generation of reports and on-screen data entry form, it becomes inherently important to provide the interface with additional features. Front End introductory screen A basic front end screen is designed for the database in order to give the use a comprehensive feel about the application. The screen lists out the features that are available for the user to access. The features are added to the interface using an Option Group control of MS-Access. This allows the user to select one option which would take him/her to the pertinent screen. A screenshot of the design that has been explained is shown below: Financial Report of Members The report detailing the players with and without membership for current financial year is given through different modes: One is through report which is useful for printing and the other is through form generation which allows the user to analyse each and every member’s record individually. A screenshot of both forms is given below: 12. Lesson Learnt The application development was massively informative and helped in learning lot of features about the MS-Access Software and more importantly able to get a stronghold of database concepts such as Normalisation, primary key , foreign keys etc. The major problem in developing the database was to visualise the number of tables required to store data and what level of normalisation is required. Since the data is dynamically updated through a front end, the table design must give access to all representations of data and should not be redundant at the same time. The team performances as well as doubles team performance are also required to identify the best team and best pair in each category. This gave a lot of complexity while designing tables. This is where the concept of grinding data to its basic form was used. By this way, firstly, all the information available was separated to their atomic level. This gave a lot of scope to a hierarchical form of data analysing. Since data has to be stored for future performances also, the data was ordered in the following hierarchical manner: Year Week Schedule Team Players Members This way of separating the data gave a lot of scope for designing the database and assigning relationships. Once the table structures were determined using the concepts of relational database, the next step was to identify the primary and foreign key for each table. Based on the concepts discussed in the studies, the primary key for each table are identified by using the rules: It should not be a duplicate Each record has unique value and cannot be null Once the primary keys were established, the foreign keys are identified thereby determining the cardinality of relationships for each table. It gave a lot of scope to understand the usefulness of cardinalities in a database. With the help of cardinalities, the values that could be passed to a particular field in a table were determined. For example, the team names given in the results table has to be from the teams table ensuring a 1: n cardinality relationship. This enabled in validating the values inherently in the database. Once the database was designed, the features available in MS Access like Form Wizard, Query Wizard helped in creating forms and queries through visualization and the detailed explanation given in the study materials enabled in easily managing the design concepts of the form and queries. Features such as, changing controls properties, creating parameter queries using parameter wizard were used in the application and thus increasing the understanding of those features. Another major advantage of developing the application was the use of queries to identify and retrieve stored results. The complexity of the application enabled in the usage of several query language features to retrieved data efficiently. Some of the optional features that were used were JOIN statements, Order by, Sort by, Distinct etc. Hence a strong understanding of these concepts were achieved with practical application Read More