Advanced Computer Skills (ACS) course

Warning: information on this website is valid ONLY for A.Y. 2009/10 up to September 2010 exam.

Computer Science is like Magic: you do everything exactly as in your books and, if nothing comes out you, try and retry from the beginning until it works. Many times nothing works even if you did everything correctly, sometimes something works even if you did nothing correct.

Lecturer: Prof. Paolo Coletti
Teaching assistant: Dott. Damiano Somenzi

Email: Paolo.Colettiunibz.it
Email: Damiano.Somenziunibz.it
Website: www.paolocoletti.it/advancedcomputer

Course

ACS course presentation A.Y. 2009/10.
ACS syllabus A.Y. 2009/10.

For Prof. Coletti complete office times, click here.

Prerequisites

In order to correctly follow this course each student is required previous knowledge on these topics:

• Computer science and Information Processing course. The whole content of this course is taken for granted in ACS course and no questions will be answered. Moreover you will not be helped with file handling during the course or exercises and especially you will not be helped during the exam.
• Statistics A course. From this course it is important to have a clear idea of what these statistical instruments mean and how they are used: mean, median, standard deviation, correlation, histogram, boxplot, contingency table, statistical test, dstribution, regression model. Their theory will not be explicitely asked at the exam, however they will be asked and used during ACS course.

Warning: due to lab room problems, second year students may not attend this course. See School's decision.

Course content

• Relational databases and Microsoft Access. In this part we will see relational databases' architecture, and how to build and modify simple databases
• PASW – Predictive Analytics SoftWare (also called SPSS – Statistical Package for Social Sciences). In this part we will see some aspects of PASW-SPSS, namely data entry, data handling, and data checking, basic data analysis, regression models and statistical tests.
• Visual Basic for Applications. In this part we will see how to build functions and small programs.

Organization

Each lesson and each exercise hour is repeated twice in sequence in exactly the same way.

Prof. Coletti is in charge of:

• Relational databases theory and Access (approximately 8 hours mostly in lab A518),
• Visual Basic algorithms (approximately 6 hours in a standard classrooms),
• PASW-SPSS theory and exercises (approximately 16 hours in lab A518).

The teaching assistant is in charge of:

• Access exercises (approximately 6 hours in lab A518),
• Visual Basic for Applications exercises (approximately 18 hours in lab A518).

Books

• Level 1 and 2 of the Databases course book.
• Excel VBA Macro Programming (Chapters 1, 2, 3, 4, 5, 6) or Excel 2007 VBA Programming for Dummies (Chapters 3, 5, 7, 8, 9, 10, 20) or Excel VBA Programming for Dummies (Chapters 3, 5, 7, 8, 9, 10, 21) or Writing Excel Macros with VBA (Chapters 3.1-4, 4.3-5, 5.1, 5.4-5, 6.1-3, 7.1-3, 8.1-5, 16.2.3, 19.1-2)
• Discovering Statistics Using SPSS (Chapters 2, 3, 4, 5, 6, 7, 8, 13.1-3, 16.1-4) or SPSS Survival Manual (Chapters 1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 16, 17, 18, 22)

Exam

Exam time is one hour. You will enter the lab in turns of 25 students each, which will appear on this website as soon as enrolment is closed. If you have specific time needs, please write an email to Prof. Coletti as soon as possible, before the timetable appears.

The exam is totally open books: you may use any written or electronic document (including books, previous exams with solutions, your personal handwritten or electronic notes and my slides). You may not, however, use any communication program or device.

The exam consists in:

• coursework on Access (30% of the final grade). You are free not to do the coursework. Please, read coursework instructions below;
• practical test on PASW-SPSS (30% of the final grade). You must do at least 40% of this part to pass the exam;
• flowchart and practical test on Visual Basic for Applications (40% of the final grade). You must do at least 40% of this part to pass the exam;
• file handling errors (such as using wrong file names, producing not required files or directory, submitting several copies of the same file) count negatively towards the final grade.

It is a good idea to practice with previous exams that you find below.

Differences with A.Y. 2008/09

Please, if you followed the course in A.Y. 2008/09 take note of these important differences.

• Access coursework: courseworks finished in A.Y. 2008/09 are valid until October 2010, those finished in A.Y. 2007/08 or before are not valid anymore. A strict presentation precedure has been introduced.
• Access additions: temporal databases, table Validation Rule, taking predetermined values list from another tables
• We use PASW 17 instead of SPSS 16.

prof. Coletti

Slides

Examples and exercises

Dott. Somenzi

A.Y. 2009/10

Exam

For students who have to graduate: if you have to graduate, or for whatever other personal reason you need to know the exam result quickly, please do not write me an email nor contact me personally before, during, or after the exam for a quick correction, for a pre-correction or to check your exam with me before it is corrected. To avoid possible misunderstandings deriving from ambiguous requests, I do not accept any request on correction priority and I will report to the Dean any unclear request. According to art. 7 of exams regulations I will give the results to the secretary not later two weeks after the exam, usually much earlier. You obviously can, and are invited to, come to me to look at your exam's errors after it has been corrected.

Coursework

General suggestion: read the PDF book on Access and databases, especially for what concerns the documentation.

Note: remember that you must submit the final database 10 days before the exam. So, take your time and do not do it in the last days.
Note: once an architecture proposal is sent, that will be your coursework for the whole academic year and it is not possible to submit a second one.

Phase one: proposal. Decide a database project, different from the examples we did in class (library, exams, buy-sell company, MyFarm) and different from the usual databases such as music/film/book/car collection or rental, or flights organization. Try to be a little original.
Draw the structure diagram with tables, fields and relations and write a brief description of your database: this description must contain your idea, how does your database work, which information do you want to store and especially the description of non obvious fields and non obvious relations. This description, the idea and its comprehensibility will be part of the evaluation.
Submit structure and description to Paolo.Colettiunibz.it by email using only your unibz email account. Do not submit any database now!
You will be randomly assigned to Prof. Coletti or to Dott. Somenzi, who will be your only reference from now on; do not try to contact the other one, he will not answer. After some days we will evaluate your architecture and you, if you need them, will receive modifications to your structure. Together you will receive a request for queries, forms and reports. In some periods like December there are a lot of students sending databases and therefore several days pass before receiving an answer.

Phase two: database.
Build the database and what we requested, exactly as requested. Together with the database you must write its technical documentation. Send database (only in Access 2000, 2002-2003, or 2007 format) and description (in English, only in .doc, .docx or .pdf format) to the person who corrected your architecture by email using only your unibz email account. Deadline is 10 days before the exam, no exceptions.
After some days, you will receive a grade from 0 to 100. This grade will last until October 2010, even if you fail an exam.

Phase three: confirmation.
In any office hour (of your choice without booking) before the exam you must present the database personally to the person who corrected it. Pay attention to our office calendars, especially in Autumn there are very few offices and no exceptions will be done. For Prof. Coletti complete office times, click here.
This procedure consists in asking you to repeat something that you have already done in your database, using your own Access version on your notebook or using Access 2007 in English on my UNIBZ computer. If you are able to do it without hesitations, your grade is confirmed. If you are not able, other two tasks will be asked and you must be able to do both without hesitations to have the grade confirmed. If you fail the confirmation, your grade for Access will be 0 and you will not have the possibility to do the coursework anymore for this academic year.

Some suggestions for the database

• think carefully at your architecture before submitting it (it is approximately 35% of your grade);
• use at least 3 tables with at least 5 fields each and a junction table;
• use a details table if it is appropriate;
• do not build a static database, if it is more natural to build a temporal one
• check that everything works properly and write a comprehensive and detailed documentation (it is approximately 15% of your grade);
• use always the appropriate field types;
• use the index option (at least 3 times, where appropriate, with and without the duplicates);
• use an appropriate field as primary key whenever possible;
• use the required option (at least 3 times, where appropriate);
• use field validation rules and validation text (at least 2 times, where appropriate);
• use table validation rule (at least once, where appropriate);
• use values taken from a predetermined list via Lookup Wizard or from another table (at least once, where appropriate);
• use drop-down menus with Lookup Wizard to build relations;
• always enforce referential integrity to your relations;
• build forms, queries and reports exactly as instructed. If we make specific requests, it's because we use them to give you a grade.

Frequently Asked Questions

Q: Where can I get Windows, Word, Excel, Access, Acrobat, PASW-SPSS, IZArc?
A: You can download IzArc from www.izarc.org for free. For Windows Vista Enterprise Multilanguage, Office 2007 and PASW-SPSS you should open a ticket on https://support.unibz.it and fix an appointment with the network administrators: they will give you the installation disks and you have the right to install these programs on your notebook. Remember to select also Access when you install Office. Adobe Acrobat licence is unfortunately not available, but to produce and protect PDF files you can install alternative free programs such as PDFCreator http://www.pdfforge.org/products/pdfcreator .

Q: How can I have Windows, Office, PASW-SPSS in English?
A: For Windows Vista, if you have installed the university version Control Panel -> Click, Language and Region -> Change Display Language. If you instead have your own version, probably you have a sigle language version and you may not change the language.
For Office, if you have installed the university version, in the Start -> All Programs -> Microsoft Office -> Microsoft Office Tools -> Microsoft Office 2007 Language Settings. If you instead have your own version, depending on the version you may or may not have this tool.
For PASW 17 or SPSS 16 you can change the language inside the program Edit -> Options -> General.

Q: May I send you the coursework or the proposal in August?
A: If you want to take the exam in February or June, absolutely no. If you want to take the exam in September, sure.

Q: I submitted my proposal in November. May I do the database in April?
A: If you want to take the exam in February, absolutely no. If you want to take the exam in June, yes but you must keep the same proposal.

Q: I cannot come to university to give you the database. May I give it to you just before the exam?
A: Absolutely no. You must send the database via email not later than 10 days before the exam. For the personal presentation, you may do it only in office hours before the exam.

Q: May I take the exam at second year or before taking the Computer Science exam?
A: Please ask this information to the secretary. If you may take it, beware: Statistics and Computer Science are prerequisites for this course, so you will have serious problems during the exam if your knowledge in these subjects is not enough. Unfortunately I see many people wrongly believing to have enough knowledges in Computer Science (especially on file handling) and consequently there are several exams with mistakes which could be easily avoided.

Q: May I attend the curse at second year or before taking the Computer Science exam?
A: Unfortunately, even through the computer room is large and the course is doubled, there are still too many students. In order to improve the lessons' quality there are restrictions on attendance (see School's decision): from A.Y. 2008/09 only third year students or students who have already taken Computer Science exam may attend. If you may attend, beware: Statistics and Computer Science are prerequisites for this course, so you will have understanding problems if your knowledge in these subjects is not enough. Statistics and especially basic Computer Science will not be reviewed.

Q: I am a second year student and I will go on Erasmus at third year. May I attend and take the exam at second year?
A: Read the answers above, the School's decision is very clear and I have no power to make exceptions.

Q: When will the next exam be?
A: I am sorry but the exam date is not decided by me and therefore I do not know it until it is officially published on the university online calendar.

Q: The exam's date is very close to our other exams. May you move it?
A: The exam date is not decided by me, so redirect your request to the secretaries. However, before advancing your request prepare yourself to find 6 consecutive hours where there is no exam of EM, Master, BS, nor any language's exam.

Q: What if I may not enroll online for technical or burocratic reasons?
A: Please write an email to me so I may put you in some turns. In any case you must ask the secretary what to do, since not enrolled students may not attend the exam.

Q: May I do the exam with my computer?
A: Yes. But beware: (1) you must be able to navigate the Internet and to enter directory \\ubz01fst\Courses\Course_Coletti (an alternative can be being able to navigate the Internet and producing a CD). Do not wait for the day before the exam to check it. (2) You are responsable for your different programs' versions and configurations and for the absence on your computer of specific programs. In particular, check that you are able to produce PDF files. (3) If you use another operative system, you are responsable if your files do not open correctly on my Windows Vista.

Q: May I do the exam using Windows in a different language?
A: Yes, sure.

Q: Will the exam be "open books"?
A: During the exam you may use any written or electronic document (including previous exams with solutions, your personal handwritten or electronic notes and my slides). You may not, however, use any communication program or device.

Q: Will the exam be similar to the previous ones?
A: Sure, but every year I add the new topics and do not ask topics which were removed from the syllabus.

Q: I lost a file during the exam because I did not save it correctly. What may you do?
A: Absolutely nothing. With time spent on exercises you should know the unrealiability level of your programs, and how often you have to save.

Q: My files were not copied correctly at the end of the exam. What may I do?
A: Checking that the copy is correct, and practicing file copy even during the exam, is your task and is official prerequisite for this course.

Previous exams

Q: Why cannot I read PASW-SPSS output files .spo? Why are there extra PDF files?
A: SPSS changes version every year. Some output files, expecially those with extension .spo, cannot be opened by the new SPSS versions and new output files cannot be opened by old SPSS versions. Therefore, for every solution I usually either provide the output in the latest format or I provide a pdf file containing the output, in case your SPSS is not able to open the output file correctly. Obviously you do not need to do the same at the exam, unless specifically instructed to produce pdf files.

Q: I cannot produce the same graph that you have in your output file.
A: Probably your SPSS version is different from the version that has been used to produce that file. The important thing is that you are able to fulfill all the requests on the exam's text, producing an identical result is not important.

This page is mantained by Paolo Coletti.