Normalisation Problem Essay

The adult education class needs a new database as running the courses with books proves a problem. There is a selection of books with each set of data in. This data can become damaged and the staff can forget to update each book. Also as there are a few books, staff has to update each one with the new data, this can become tenuous and annoying. Another point is that there are many different subjects, rooms and tutors that need to be linked together. This can be a problem as some classes can be in different rooms each day of the week. With a book this can be a problem, as the design may not have a feature for this. The new database will put all the data into separate tables and link them all together. This will enable all the tables to function together allowing the user to change the data in one and it will be changed in them all. The user will also be able to run queries asking the database questions. This will enable the user to gather information without having to search through all the data. This can save a lot of time and improve accuracy as human error may be nearly deleted. The database is going to have to incorporate the student’s personnel data, the class times and the tutors. The database will be assessable to all the teachers and admin staff so, with little training everyone will be able to finds out important information. With a book system only the admin staff would know how to use it and if the teachers tried then they may not do it correctly. This could be a problem as the information could get confused and then people are sent to the wrong lesson, room or the teachers may not turn up. Another good point of the database is that it I more professional to use a computer then a book. This may rub of on the company’s image especially if running an ICT course. If a prospective customer/student sees that they are running it with a book system then they may not be impressed and choose not to do the course. Normalisation Normalisation is a process used to come up with the best possible design for a related database. Tables should be organised in such a way that; * No data is unnecessarily duplicated * Data is consistent throughout the database * The structure of each table is flexible enough to allow you to enter as many or as few times * The structure should enable a user to make all kinds of different queries relating from different tables. Unormalised First name, surname, title, street, town, county, country, postcode, telephone no., D.O.B, occupation, date of first class, fee payable, type of payment, classes taken (subjects), tutors, date of classes, time of classes, room no. First normalised form This would not work as each student can take more then one class. To get it to work you would have to use a second normalised form, which would include a selection table in the middle like this; Second normalised form This selection table would enable students to do more then one course on the database. Third normalised form. You then have to link the tutors in with the courses. To do this you will create a totally different table but link the primary key in with the course table. Original Table Design > Student number – I will require this so I can link the tables together, as this will be my primary key. This will be of the Auto Number format so each student has a unique number they can be identified by. > Title – The title field will be of the text format and will be up to 5 characters long. This will have a validation rule of â€Å"Mr, Mrs, Miss, Dr, Mastr, Other† > Surname – This will be of the text format and be up to 25 characters long. > Forenames – This will also be of the text format and will be up to 20 characters long. > House number/name – This will be of the memo format as it can include numbers and letters. Also as the house names can be long it gives plenty of space. > Street – This is a text field, there is a maximum of 15 characters in this field. > Town – This is also a text field as most are. This has got a maximum of 20 characters as some towns can have long names. > County – Again this is a text field, there is a maximum of 20 characters as again some counties have a long name sometimes up to 20 characters. > Country – Another text field, and again 20 characters. I decided to add country as people may be learning English in this class and may still live abroad. > Postcode – This is a memo field as there are both letters and numbers in the postcodes. > Telephone Number – This field is surprisingly a text field as the phone number has a space between the area code and the number. > Mobile Number – This again will be a text field as there is a space after the first 5 numbers. > Date of birth (DOB) – This is a Date/Time field as you can set a date such as 10/02/77. This looks good and is also easy to read and easy to input. This will have an input mask of dd/mm/yy. 1st Amended Table Design > Student number – No Amendments > Title – No Amendments > Surname – No Amendments > Forenames – No Amendments > House number/name – No Amendments > Street – No Amendments > Town – No Amendments > County – No Amendments > Country – The Default value of this field is set as â€Å"England†. This is because if a student is of a different nationality trying to gain English qualifications, then to be contacted they will have to have an English address. > Postcode – I was going to add an input mask into this field, but realised that different parts of the country have different postcodes with different amounts of letters/numbers. > Telephone Number – I have added an input mask into this field to aid the accuracy of phone numbers. If a number is wrongly inputted then the student cannot be contacted in short notice. The input mask looks like this : !00000-000000. For example 01603-7456474. No more numbers can be added or none lost. > Mobile Number – This also has an input mask so no mistakes are made. This one will look like !00000-000000. For example 07763-369961 > Date of birth (DOB) – No Amendments. This will also have an input mask, which I have already stated. The system I will be using The system at school My system at home Pentium 2 350MHz AMD Athlon 600MHz 64 MB 128 MB 6.4 Gig Memory 27 Gig Memory 14†³ SVGA Monitor 17†³ SVGA Monitor Microsoft office 97 Microsoft office 97 + Microsoft word 2000 Microsoft Windows 98 Microsoft Windows 98 8MB HND Graphics Card TNT 2 Graphics card Constraints and limitations As I will be doing work at home and at 6th form I may experience a few constraints. For example at home I have Microsoft word 2000 whereas at 6th form they have the 97 edition. This can prove to be a problem, as I cannot take work to and from 6th form to complete at home. This means that I will have to complete all word documents either at home or at 6th form. This is only a minor problem which I can overcome by selecting work to take home as I have access 97 the same as the 6th form. If I know I have to do a word document and I may have to take it home I will do it at 6th form rather than other work which I can take home. I will have to manage my time. My second slight limitation is that other classes in the school often book the computer rooms at 6th form. This can mean that I am not able to work on a computer at certain times. This can be a problem but I can over come this by writing it up on paper in a study room and then type it up at a later date. This can be frustrating by something that you cannot do anything about so you have to live with it. Also if a student is away in another class then there may be the odd spare computer I can work on. Apart from these there are not many other problems that I think I will encounter. Also these can be overcome with ease. My ICT skills I have studied ICT at GCSE level and gained a level ‘B’ at the subject. This helps as I have done pervious work with the programs at a high level. I also work on my home computer regularly and pick up skills. Screenshots of my database Screenshot 1: Bookings Table Screenshot 2: Courses Table Screenshot 3: Members table Screenshot 4: Tutors table Screenshot 5: Front page Screenshot 6: Student link from front page Screenshot 7: Tutors link from front page Screenshot 8: Bookings link from front page Screenshot 9: Application from for new members Screenshot 11: Add new tutor form linked from tutor’s page Screenshot 12: Change tutor details linked from tutor’s main page Screenshot 13: New bookings linked from bookings main page Screenshot 14: Delete booking linked from bookings main page Screenshot 15: Check bookings query, linked from ‘check bookings for course’ button Screenshot 16: Result of running query shown above Screenshot 17: Macro needed for ‘add new member’ Screenshot 18: Macro needed for ‘add new tutor’ Screenshot 19: Macro needed for ‘new bookings’ Screenshot 20: Design of query for course query Screenshot 21: Main relationship Evaluation * Data can be viewed, recalled and saved easily. I have met this requirement as I can view and recall data through queries and forms and save data through my forms using macros. If I had a criticism it would be that more queries are needed to access all data easier than now. All records can be viewed using forms based off information tables. This means that I have all the information there is and that it is in a format which is easy to use and view. Also I can use my action and navigation buttons to move around easily. * Data can be easily updated or changed, changing all data in database. For example a booking change in a members records would change a booking in the tutors records, with only putting the data in once. I have fulfilled this requirement as I can do this. This is because I have my relationships between all tables, linking them so if I change a piece of information in one form then it will automatically changed in all other concerning the matter. * Mistakes will be easily to spot, with database flagging up any un-normal inputted data. I have tried to reduce the possibility of human error when imputing data or changing data. I have done this with input masks. I have added input masks to most date, time and telephone number fields in my forms so if it dopes not meet a certain criteria then it will flag it up and not accept it. An example of a input mask when ‘wrong’ data has been inputted. * Must be able to print out which course tutors teach and when they are. My query will help with this once you have searched for a course, it takes you to a screen which allows you to print out the data. I did add a query and from combo in which once the data is searched for it automatically prints the data. Once I had this in place realised that it may be a nuisance to the end user, as they may not want a print out all the time so I thought that I would leave it up to the. If it printed out automatically it may waste paper and ink therefore cost the business money. User guide Introduction The database will open on the front page. When you want to do something the first place you need to go is the main page. This has access to all you will need to do. As you will see there are three buttons, ‘Students’, ‘Tutors’ and ‘Bookings’. Each will link you to the part of the database you need. For example if you wanted to make a booking then you will need to click on the bookings button. Once you have clicked on anyone of these you will then get a specialised menu for each section. For example if I clicked on bookings I would get a list of options for bookings like this: You then have a list for what you may want to do. When you click on these you will be linked to a form which will allow you to input or browse information easily. For example if I clicked on ‘New Booking’ in the bookings section I would get a form which allows you to input new information for a new booking. This is what it would look like; That’s the basics I will now show you how to do some of the most common tasks. 1) Adding a new student or tutor: Open the database, as members are under the student section click on students, if you want to add a new tutor then just click on tutors. Both are the same, below are instructions for adding a new student but adding a new tutor is exactly the same so just follow the same instructions but just with ‘tutors’. You will then need to click on the ‘New Student button’ this will link you to a form which will allow you to put in information so a new member is added. The from has a macro linked to it so you will always get a blank form when you click this button. If you want to browse other details then click on the 2 buttons, ‘Previous record’ or ‘Next record’. The page you get will look like this; Once you have inputted all the information you want just click on ‘Save record’ and the information will be saved. Then click on close form and you will go back to the students menu, if you do not want to be here either then click on ‘Back to the main screen’ which is on every selection screen. This links you back to the main menu so you can choose what you want to do next. 2) To change details of students or tutors: Again changing students or tutors details are done is the same way. Below are a list of instructions for changing tutors details but again, if you want students then you will have to follow the same instructions but just with ‘Students’. Click on the ‘Tutors’ button on the main page, this will link you to a tutors menu just like the ‘students’ one. Here you can add a new tutor or change a existing tutors details. To change details just click on the ‘Change Tutor Details’ button as shown below.