New Perspectives on Microsoft Office Access - Assignment Example

New Perspectives on Microsoft Office Access 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 saved 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: 4. What other tables, attributes and relationships would be useful to create a more complete design? Three additional tables could be used to enhance the features. First is to store details about the court availability which includes court schedule and court maintenance charges. (TABLE COURT) Second is to store the yearly and weekly details of when the event is happening. (TABLE TIMETABLE) Third additional table is used to choose the star player among the public where the public determine their option of best player or doubles team and they vote for them. (TABLE STARPLAYER) Business Constraints The business constraints would be to the management of data integrity as well gathering of information from different sources. The organisation has to refer to several other counterparts to get the required details The ERD diagram involving these three additional tables is shown below: 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 a tedious process. The inputs can be obtained from the user in n number of ways, but the method chosen should be easily understandable by the user. The interpretations of data should be understandable by the user. 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 the year of competition, then the week and finally the teams to play for that week. From here the option varies. If the year selected is in the past, the user can either view the results 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 event 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’, the 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 his 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. (Alexander 2007) 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 the query is stored, the corresponding results are displayed. The query created by following the above mentioned method is as follows: And the result is shown in the below given screenshot. 8. Create a query to find the best doubles combinations in each of the mens, ladies, and mixed. These are the following queries to identify the best pair in men’s doubles and ladies doubles and in mixed section This is a sample query to retrieve the women’s doubles best pair. The first query returns the team which has the maximum winning difference of scores against all other teams. The team which tops the list is taken and from which the second query, returns the names of the two women players. The parameters passed in the first query are the match type value which is Mxd – mixed double Md – men double Wd – women doubles. On passing this value to the first query, the best pair results are obtained. The screenshots are shown below 9. Challenge - Create a set of team results The team results are calculated and displayed for every year. The user is allowed to select the year and based on which the team result summary is displayed for that year. The design of the form is shown below On selection of year, the results are displayed This is done using the following two queries They return the sum of won scores and lost scores of a particular team against a single opponent and sum of these two values are added up and stored in the appropriate form text box. Finally the percentage of winning is calculated using the formulas described in the excel sheet. 10. Features that enhance the usefulness (functionality) of the application Viewing of Results Another additional feature added to the result form is the one that allows 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. (Kroenke and Auer 2008). Front End introductory screen A basic front end screen is designed for the database in order to give the user 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. (Kroenke and Auer 2008). 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. (Alexander 2007) 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 (Adamski and Finnegan 2007) 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. (Kroenke and Auer 2008). 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. (Adamski and Finnegan 2007) 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 Bibliography Adamski, J. and K.Finnegan.2007. New Perspectives on Microsoft Office Access, Comprehensive. Australia: Thomson Course Technology. Alexander, M.2007. Microsoft Access 2007 Data Analysis. U.S.A: Wiley Inc. Kroenke, D. and D.Auer.2008. Database Concepts. U.S.A: Pearson Prentice Hall. Read More