IGCSE ICT MAY JUNE 2017 Paper 21 Data Manipulation part 1



hello guys my name is Evans and welcome to this video tutorial in this room I'm going to continue looking at day 2017 minjoon IGCC ICT pepper – one in the couple videos we've done so far we've done a document production and so we just now need to go and look at that manipulation okay so our scenario is that you're going to prepare some reports for the company okay so make sure all currency values display a currency symbol and add to two decimal places okay so step number 22 examine the data in the files jet two one seven students dot CSV and j21 seven rooms or CSV and so let's go ahead and examine those files so the files in question is files in question our students file as well as their rooms found so I'm going just to examine this two files ok so just going to do that so you noticed that this file is arranged in rows and columns and typically we can use this type of file to create a table okay so let me close this one and open the student file as well tech not also that this file is equally arranged in rows and columns and techno to row number one that it is actually the headings of each it contains the head the headings for each column okay right so let me just close this as well I'm not going to save it okay so these are the files so using a suitable software database package import the file g17 students don't CSV and you're going to use these fields these field names and data types as you import your file ok so let's go ahead and do just that okay so I'm going to run access okay so I run axis and I'll create a blank database out just go it maybe tutorial tutorial or let's say sorry let's just say if May June 2016 p21 okay create then what we're going to do is to may I just put this file inside inside okay let me just put it inside the work area so that you guys can access and this database afterwards okay so I'm just going to put it inside here so that it is easily made available to you guys alright so let's go ahead and now import the file in question so browse to work area and you open the file in question so let's go back to the question paper so we are opening the student file this one then okay now you guys at this stage you should be familiar with importing a CSV file into your access as a database way table first name like we saw contains the field names and so go to next then technology the data types for each of these files so we told that from row number 2 last name that's text so let's say that so row number first name last name as text then date of birth is dead with them that's fine gender is text and then account called payment type and cause they're supposed to be text as well so this is text text text texts then scroll to your right should be some more records there and then let's see weeks is numerical integer and paint is supposed to be boolean or logical so let's go ahead and do that so this one is supposed to be long integer that should be fine and this one is supposed to be boolean so make it yes or no logical okay go to next at this stage now we need to specify whether we should have a primary key or not and we are told that set the row number field as primary key and then we're going to save the data so let's go ahead and set these okay so as you can see or row number is set to primary key go ahead and finish this and once you're done close this open the student table save it that should be perfectly fine next question it says take the screenshot screenshot evidence draw in the field names and data types used in the table insert this into your evidence document okay and they go to three max so what I'm going to do is to make this into design view or change this into design view so that we can access the field names with a corresponding data types at this stage now you can go ahead and change any data type that you see maybe you forgot to change initiative when you're doing the important of the CSV file so go ahead and just get a screenshot of this okay so guys I'm gonna have to pause a little bit I've lost power and so it will just take a moment before the power is restored okay okay so there you go it's back alright so what I'm going to do is to get a screenshot of this okay and copy it then I'm going to open my evidence document and paste past this okay so let's go into the evidence document and we're going to step number three and paste this one okay so I'm going just to push this down there okay that looks good alright so I'll come back here and next step okay so next step is step number twenty-three set criteria which will only allow the letters M or F to be entered in the gender field okay and take a screenshot showing how you have limited data entry into the gender field and inset this screenshot into evidence document okay so even as we go back here we are supposed to take note of some of the things here it says display data as so we need to make sure that the debt field is displaying the data in this format as well as the gender is only accepting M or F this is the question and step register being asked to do and then also unpaid that it is showing yes or no okay so let's go ahead and just change that so under paid we is supposed to change the format and supposed to show yes or no okay and then under gender and the debt so the debt is supposed to have the form a different format as well and it's supposed to be this format okay so just go back to the question paper and see the format it is supposed to be day then the year two characters or two numbers for the day for the month two and then four digits for they yeah okay so let's go ahead and do that so make the debt to be this one okay short debt and then also come back to the gender and this is where you're supposed to set a validation rule that is going to allow only M or F to be set okay so under validation this is where you're going to come here and select M or F and you can even reduce the field size if you want from 255 characters – just a single character or maybe two characters because as you do that you're saving space in your database cause what what happens is that if you don't do that access is going to receive about 255 characters for each record and that if you're going to have maybe thousands and millions of records in this database you multiply that by the fact of 255 in fact that some space would have been used okay so usually if you're designing a professional database you'd want also to remain limit the field size so that your file size for the database is over small okay so on the validation room is just going to indicate in quartz M or F okay so what this means guys is that only except M or F okay and these are supposed to be put in codes okay there's also what is known as validation text and so you can put some text if somebody enters something and then it did not match what you expected so for example I can say please enter only m or female or rather F for male and or female respectively okay so this validation text to be displayed every time somebody fails to enter M on F and then a message will pop up here to say well please enter M or F for male or female respectively but we don't need this because the question has not asked us to do that so once you've done this then we're supposed to take a screenshot we're told that you're supposed to sit and take a screenshot evidence showing how you have limited data entry in the gender field and set this into your evidence document so let's go ahead and get a screenshot of this so I'll select gender as usual and get the screenshot so remember when you get the screenshot you must go all the way down until the validation rule is visible okay so I can end here if I don't mind okay that should be fine and copy it and paste it into your evidence document and that should be perfectly fine okay next so I push this down there next you're supposed to do step number step number 24 it says add this record and then this is the record row number and all these headings and then we have SS 0 0 7 2 Li Lin add then you have 2700 to 1968 and so on and so forth so I'm going to add this record into my database okay so what I'm going to do let me just print out this section of code so that I can easily add it without flipping between the screens okay so let me just print this and have a hard copy then I can look at but you guys you know that we are adding this field okay let's grab my drink out okay there you go all right so let's save this now you notice every time you try to change the to save this not every time but for the first time we want to save this you get this message data integrity rules have been changed existing that I may not be valid for the new rules and this process may take a long time so do not do you want to the existing data to be tested with the new rules and then what you want here is yes okay so suppose the way some data initially in some of the agenda in the gender column which was male instead of m or female instead of F then you can go ahead and test that and it is going to support at some of the areas that were existing and you can go ahead and change that so this consistency in your data in terms of having just M or F as opposed to having male and female I hope this is very clear guys and then you can go ahead and go back to to datasheet view and then go and add a new record just at the bottom here now what you want to do is to enter SS zero zero zero seven seven two then enter l a– and then Leonids and then you enter the dead 2702 and then 19th 1968 alternatively you can use a dead pika and then you can pick up that okay so the gender is female and the account code is supposed to be sa sc2 and the payment type is check and then the cause is history of at okay history of AD and then weeks is supposed to be five and paid is supposed to be yes okay paid this supposed to be yes so go ahead and save your data best check it for any entry errors and then just make sure that your database is actually up to date now like I've always say if you are giving something in bold like this complet is expecting you to enter it as it is without error okay so that is very important so save your data for errors and save the database okay so what you want to do also let's go to step in five anyway import the file okay yeah so let's just go ahead and import this fund then we'll do step 26 in in separate video okay so let's go do to step in five import of out J 2 1 7 rooms dot CSV as a new table into database and set the account code as the primary key okay so let's go ahead and just import that come back here and go to external data I'm going to close this table import a text file browse to the Kitchener import in the rooms CSV file and now go ahead and set the first row to be to contain the field names and then go ahead there we have not been told to change any data type so don't bother to do any change here just go ahead and choose the primary key as account code okay and then next and then finish okay so the next question we are told to create a relationship a relationship between account code field in their students table as well and the account code field in the and the code field in the dayroom stable okay and then we're going to take a screenshot of these relationships and paste into a fitness document okay so this will be the last step that would do for this section of this video okay so let's go ahead and import our CSV file so rather not imported says we recreate our relationship so go to database tools and select relationships double click on each of these stables to add them to do that and then just drag and drop account code drag and drop it on to account code and you need to make sure that you enforce referential integrity okay so what this will do is that it's going to make sure that this consistency in of data between the two fields okay so sometimes some views may not allow you to enforce integrity but that should be fine but most fields would do columns or fields will allow you to enforce the referential integrity just makes your data to be as close as possible to each other so there you go you see the 1 to infinite symbol here this just means a one-to-many relationship now what we're supposed to do is to actually edit the relationship so that we can get a screenshot of this what I'm going to do is to get a screenshot if you forgot you close this you need to get a screenshot of this to show evidence so just do that and get a screenshot of this entire part ok so like I said I'm having a problem with some with power and so my my computer is connected to the UPS and my monitor is not connected to the UPS so what happens when I lose power my screen is going off and my my my computer continues to run in the background ok so I was I was talking my screen was blank and now it has been restored so I'm just having this an unusual I'd interrupt in in power supply and they may affect the rate at which we record these videos okay so anyway I've gotten a screenshot of that and again have lost power so guys I'm gonna have to stop this video damn I'm gonna have to continue it when power is fully restored because at now it keeps on going on and off alternatively let me just finish recording this section and then I 1 plugged my monitor from the main power supply and put it on the UPS and then we can continue okay so let me just get a screenshot of this and paste it in my evidence document ok so that shows you that so this is the what you need so this one is showing here that you have created the relationship and this part is showing you that it's a one-to-many relationship that you have created ok so right so let's go ahead and so this in some step 25 and what we're going to do now in the next video we're going to pick up and step number 26 okay so let me just now pause this video or stop this video and then I'll continue in the next video we step number 26 and then we'll see how far we can go alright so thank you so much for watching guys and I encourage you to like comment and subscribe to my channel alright see you

28 Comments

  1. Duy Nguyen Nguyen said:

    Great video bro!!

    May 23, 2019
    Reply
  2. lethalkitty said:

    Your videos make me happy because I needed them! God bless you

    May 23, 2019
    Reply
  3. Ahmed Abbas said:

    my exams are on 23th of apr

    May 23, 2019
    Reply
  4. Vortex Lord said:

    Best video ever! So in this comment section all people are taking British exam if I'm not mistake. Because it's Cambridge right? My exam is tomorrow so yeah… Well wish me luck.

    May 23, 2019
    Reply
  5. Kofi Lokko said:

    Thanks. I have my Paper 2 in 3 days, but I'm not worried all thanks to your help. Keep up the great work 👍🏾

    May 23, 2019
    Reply
  6. Sophia Jackson said:

    Sir the paid field is not coming as text

    May 23, 2019
    Reply
  7. Dixshant s said:

    sir how do u get the date in the DD/MM?yyyy format. for months i am getting the data as "2" instead of "02", for example. pls reply as soon as possible

    May 23, 2019
    Reply
  8. Alamgir-K said:

    Thank you for your IGCSE ICT videos. They are the only reason I'll hopefully get good grades this summer during the CIE exams because my school isn't good at all at teaching ICT (even my teacher learns from you). Really thorough and helpful videos. Hope your channel grows well

    May 23, 2019
    Reply
  9. cookies andcream said:

    If anyone is getting import errors then change the date from MDY to DMY through the drop down menu.. It worked for me

    May 23, 2019
    Reply
  10. 184CyberTitan184 said:

    ICT exam in 21 days

    May 23, 2019
    Reply
  11. faiza qadir said:

    Why do I always get the error when importing

    May 23, 2019
    Reply
  12. Iqra Naveed said:

    You and your wife are so cute

    May 23, 2019
    Reply
  13. Ahmet Emin said:

    Hello sir instead of taking screenshot of just the necessary part for not taking risk can I take screenshot of the whole page .

    May 23, 2019
    Reply
  14. Harshita Sipani said:

    Can you pls do the presentation part?

    May 23, 2019
    Reply
  15. Nicole Nyarko said:

    getting import errors😑 so frustrated

    May 23, 2019
    Reply
  16. Siddharth Jadeja said:

    Today is my exams and im practising along with your videos thankyou sir.

    May 23, 2019
    Reply
  17. Hashim Sama said:

    Your evidence 3 is incorrect, the paid field is supposed to be yes/no but you forgot to change it

    May 23, 2019
    Reply
  18. Ravi 8401 said:

    Where do you get the source files

    May 23, 2019
    Reply
  19. Xing Hong Yong said:

    Thankssss and hope the power problem is fixed soon 😃😊

    May 23, 2019
    Reply
  20. Misha Munirah said:

    Dear Evans Chikasa, thank you so much for everything you and your wife are so cute you have helped me so much! i appreciate you sooo much love you and your wife muah xoxo your wife is so pretty and your pics with her are so cute omg❤❤

    May 23, 2019
    Reply
  21. Learn to Play said:

    In step 22 when you were doing the evidence for the screenshot, aren't you supposed to format the 'Paid' field to 'Yes/No' down in the Field properties and display this in the screenshot as well?

    May 23, 2019
    Reply
  22. Shajaan Nufail said:

    This is amazing, Keep up the good work sir. My exams on the 5th

    May 23, 2019
    Reply
  23. KaungOak Soe said:

    Pls tell me my exam is the next tomorrow

    May 23, 2019
    Reply
  24. KaungOak Soe said:

    I cannot open text file and don’t see another text file

    May 23, 2019
    Reply
  25. Varun Rajakumar said:

    to write my igcse exam

    May 23, 2019
    Reply
  26. Varun Rajakumar said:

    thanks i am going

    May 23, 2019
    Reply
  27. Ahmed Jassem said:

    Hi evans,
    Evan can you please start making some AS applied ict past papers
    Thanks 🙂

    May 23, 2019
    Reply
  28. Kelspeth said:

    first !!!!

    May 23, 2019
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *