Database_Fundamentals

Student Number: 200202650 School of Engineering & Computing Course: HNC Business Information Technology Module: Database Fundamentals Module Reference: CSH1025 Tutor: Adrian Stevart Date: Monday, 21 June 2004 CONTENTS | |ix | |User Requirements |1 | |Normalization |1 | |Forms and there uses |1 | |Data Entry Procedures |1 | |Data Validation |2 | |Query Based Enquiry |2 | |Data Dictionary |2 | |Automation |2 | |Creating a Switchboard |2 > 3 | |Conclusion |4 | | | | |Appendices |5 > 51 | | | | |Bibliography |52 | 1. 1. User Requirements • Database to be used by recruitment company called Finders • Finders work for a number of external clients • All types of employment • Agency get a fee for each vacancy filled • Upon instruction by client, Finders place advert in suitable publication • Details of applicants, jobs, clients, publication details to be stored on database • System must be created so that Mail merge can be used to automate interview letters • Reports and Queries to be used to interrogate the database To achieve all the objectives outlined within the Database scenario and to be able to manipulate the data and to present it in such a way as to be useful for decision-making, a structured process needs to be adhered to. Normalization is a data technique that attempts to capture all the system needs to store and to organize the data into an efficient structure, and one that doesn’t fall down when operational. With the Finders Employment agency in mind this will enable the data to be organized in such a way that can then be applied to creating a Database that works efficiently. This process can start from a list of seemingly disorganized data items collected during the fact-finding stages of analysis. After normalization has been applied and grouped data within logical groups (entities), this then creates a data model, as seen in [1.1] on Appendix A. • First normal form demands that each entity has an identifying key that is unique only to that entity. For this purpose I have created a key field, as seen in each and every entity at the end of the attributes list underlined, as seen in [1.2]. • Normalization provides an algorithm for reducing complex data structures into irreducible simple structures. The data that the system needs to store data is organized into its minimal form. Therefore name and address within the APPLICANTS entity and similarly address within the CLIENTS entity have been broken down further. Please see end of respective attributes list for amendments within [1.2]. Please see Appendix B for refined Entity Relationship Diagram. 2. Data validation puts into place further safeguards that ensure that the data input maintains its integrity. It checks that the data is sensible before processing it. An example of a validation rule that could be applied to Finders Employment Agency’s database is a range check, whereupon the data within a form falls between a specified range of values. For example the month of a persons date of birth falls between 1 and 12. Forms………..what are they good for' Forms are excellent and user-friendlier for data entry than tables, for example. Forms feel more comfortable. Forms also have facilities for data filtering, automation, and validation beyond that possible using tables or queries alone. Within the Finders database all forms were linked to an underlying table. Therefore all data within the form is written to and read from the bound table. Each and every form was created by using the AutoForm Wizard that generates a form by using the data within a table. A wizard speeds up the process of creating a form because it does all the basic work for you. You are still able to amend and add features within the design view. Please refer to Appendix C for example of a Data Input Form (filtered job & filtered client form). Adding Features to increase speed and accuracy An Input Mask is another tool that ensures that the data inserted within the database is consistent and maintains the integrity of the data by fitting in to the pre-defined format. This process is undertaken within the design stage of a table/form by defining the format of a field. This is very essential for the database when it comes to sorting and/or executing queries. I created an Input Mask within the Applicant table and specifically the Date of Birth field. Please refer to [1.3] for example of Input Mask for Date of Birth. I covered every variation of applicant Post Codes by using the following Input Mask: - Aa90 9AA Predefined display formats were also applied for the currency; this again was to ensure that the data entries were consistent. I did this by setting the display format within the design window, and the bottom part of the window and the Validation Rule box. This can be viewed job details form and the ‘Salary PA’ field. Now all data will be returned in the same format, irrespective of what actually gets typed into the field. 3. Please see Appendix D for Associate Printouts. 4. How is QBE used and its relationship to SQL' QBE stands for Query by Grid. It is a front-end interface that allows a user to interact through using an icon-based system, and fits comfortably with people already familiar with Windows based Operating Systems. It enables a user to create complex queries simply by first selecting the range of tables and then by placing various fields relating to the query within the grid. The user is then able to restrict or sort the query within the criteria or the sort field. The query can be manipulated by using the icons supplied on the toolbars. For all intense and purposes, SQL is the language that interrogates the database by using a set of instructions. This is conducted out of sight of the user who just sees that the query has returned the information courtesy of the information in the form of the resulting record/s. It is done this way because QBG is considered more user friendly. Please refer to Appendix E for Mail-merged letters for the short-listed candidates for job 147. 5. Data Dictionary Please refer to Appendix F for limited Data Dictionary for the proposed solution. 6. Automate some database tasks Macro’s help automate common tasks. A macro is a set of one or more actions that each perform a particular operation, such as opening a form or printing a report. For the benefit of the Finders database I created a Macro for the Switchboard, where the pressing of one command button instigated when a user clicks a command button. This was achieved by following the instructions of the Control Wizard within the toolbox within Design View. 7. Creating a Switchboard Menu System 1. On the Tools menu, point to Add-ins, and then click Switchboard Manager. 2. If Microsoft Access asks if you'd like to create a switchboard, click Yes. 3. In the Switchboard Manager dialog box, click Edit. 4. In the Edit Switchboard Page dialog box, type a name for the switchboard in the Switchboard Name box, and then click New. 5. In the Edit Switchboard Item dialog box, type the text for the first switchboard button in the Text box, and then click a command in the Command box. For example, type Review Products in the Text box, and then click Open Form In Edit Mode in the Command box. 6. Depending on which command you click, Microsoft Access displays another box below the Command box. Click an item in this box, if necessary. For example, if you clicked Open Form In Edit Mode in the Command box in step 5, click the name of the form you want to open in the Form box, such as Review Products, and then click OK. 7. Repeat steps 4 through 6 until you've added all the items to the switchboard. If you want to edit or delete an item, click the item in the Items On This Switchboard box, and then click Edit or Delete. If you want to rearrange items, click the item in the box, and then click Move Up or Move Down. 8. Click Close. Please refer to [1.4] for screen dump illustrating Switchboard created within Finders Database. 8. Please refer to Appendix G for additional tasks (results of test data). 9. Conclusion of Report To conclude, I have found my level of understanding to be that much better after interacting with the relational database and by gaining an insight into its mechanism by completing the tasks asked of me. My one and only criticism of the software that I felt impeded the database and in particular the solution to the user requirements was that within Access itself there are some software glitches that would need to be rectified. I recall within the report wizard the information that was shown on screen didn’t reflect the information that was viewed when printed out. Entity Relationship Diagram for Finders Employment Agency Appendices Appendix A Unnormalised ERD of proposed basic system for Finders Employment Agency |Name |Job Title |Organisation |Publication |Employment Type | |Address |Salary PA |Contact | |Payment Period | |Post Code |Manager |Contacts Position | |Commission Percent | |Home Telephone Number |Department |Address | | | |Business Telephone Number |Probation month |Post Code | | | |E-mail address |Fee months |Telephone Number | | | |Date of Birth |Interview Date |Discount Percent | | | |Organisation |Interview Time | | | | |Current Salary |Appointment Date | | | | |Period of notice | | | | | |Qualification | | | | | |CV Received | | | | | |Short List | | | | | |Reference Received | | | | | |Date entered | | | | | Normalized ERD of proposed basic system for Finders Employment Agency |Ref Number |Job Ref Number |Client Ref Number |Publication Ref Number |MOW Ref Number | |Title |MOW Ref Number * |Address 1 | | | |Forename | |Address 2 | | | |Surname | |Address 3 | | | |Address 1 | | | | | |Address 2 | | | | | |Address 3 | | | | | |Job Ref Number * | | | | | Appendix B Appendix C Appendix D Appendix E Appendix F Appendix G Bibliography Britton, C and Doake, J (1993) Software System Development: A Gentle Introduction. – (McGraw-Hill International Series in Software Engineering) 2. ----------------------- Client Details Client Reference No Organisation Contact Contacts Position Address 1 Address 2 Address 3 Post Code Telephone No Discount Percent Job Details Job Reference No Job Title Salary PA Manager Department Client Ref No Probationary month Mode of Employment ref Fee months Interview Date Interview Time Appointment Date Applicant’s details Ref Number Title Forename Surname Address 1 Address 2 Address 3 Post Code Home Phone No Business Phone No E-mail address DOB Organisation Current Employment Present Salary Period of notice Qualification Adverts Seen Ref CV Received Short list Reference Received Date entered Job reference number 1 1 Publication Details Pub Ref No Publication MOW Details MOW Ref No Employment Type Payment Period Commission Percent . . APPLICANT JOBS CLIENTS PUBLICATION MOW