Information Systems and Data Management course - code 27000

Important warning: this is ISDM course 27000 for Economics and Social Sciences, first semester. It is not ISDM 27006 for Economics and Management students.

Lecturer: Dr. Paolo Coletti Paolo.Colettiunibz.it - Office E203 - Office hours: www.paolocoletti.it/timetable
Teaching assistant: Dott. Alessio Brutti Alessio.Bruttiunibz.it
Website: www.paolocoletti.it/informationsystems27000

If you are a regular attending student:
1) read this webpage entirely, even if you have listened to it during the first lesson
2) come to every lesson and review it before the next one. If you skip a lesson, watch the corresponding topic on the videos
3) take the mid-terms, paying particular attention to pass the first one which is mandatory for attending the second one
4) if you passed the mid-terms, the exam will be really short for you.

If you are a sporadic attending student:
Skipping many lessons is the worst way to do this course as many of its topics are sequential. I really suggest that you take into consideration attending all the lessons of an entire topic (Excel, or databases+Access) or not attending at all.

If you are a non-attending student:
1) read this webpage entirely
2) read it again. I am not joking, you will find relevant information for you even during the second read which might avoid you trobles later on
3) study the PDF book of the theory and watch the videos of Excel, databases and Access. There is a playlist called 27000 on my YouTube channel
4) come anyway to the first mid-term and, if you pass it, to the second one. If you pass them your exam will be much shorter.

Course

ISDM 27000 course syllabus A.Y. 2017/18.
ISDM 27000 course content A.Y. 2017/18.

Prerequisites

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

A detailed list of prerequisites is here. In any case, the teaching assistant will dedicate its first lessons to a precourse on these prerequisites.

Course content

How to study for this course

This course is different from the majority of courses you are used to. This course is much more technical than theoretical and it is strictly sequential. This means that you have to adapt your study strategy. First of all, you either attend all the lessons (or compensate for missing lessons watching immediately the corresponding videos) or it is really not worth coming to the next one, you will disturb your neighbours asking for help not realizing that your lack of understanding is due to your missing knowledge and not my speed. Moreover, after each practical lesson you must repeat slowly on your own the examples done in class in order to be sure to have fully graped the explained concepts before the next lesson.

For the exam the main difference with respect to other courses is that you have to train much more than studying. The content of this course is easy and does not need extensive study, however it is only with practice that you become skilled enough.

Organization

Dr. Coletti is in charge of:

The teaching assistant is in charge of:

Exam

Exam is divided into two parts.

The first part's time is held in a standard classroom (or in computer room but with computer switched off) and lasts approximately 15 minutes. This part is completely on paper and "closed book": no paper nor electronic help or tool is allowed. It consists in

The second part is held in computer room in turns of 25 students each. Turns will appear on this website as soon as enrollment is closed. This part's time is 30 minutes, but may vary according to the complexity of exercises. If you have specific timetable's needs, please write an email to Dr. Coletti as soon as possible, before the timetable appears. This part 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. This part consists in

Important warning: the crucial point of second part of the exam (and of the second mid-term) is time. If you never practice with Access and Excel, you will still be able to do the exam but you will waste a lot of time looking for the commands and you will not finish it in time. Only with exercises will you be fast enough to complete it in the indicated time. It is a good idea to practice with a clock on the previous exams that you find below.

First mid-term: the first mid-term is on the theoretical questions and it is done at the end of basic computer classes (currently planned on 16th October 2017 at 16:00, but may change in case of problems). It is identical to an exam's exercise. This mid-term is totally closed books, no dictionary, no calculator. The students who reach at least 60% use this grade to skip the theoretical questions at the exam and have the right to do the other mid-term. Students who do not participate or who do not reach 60% cannot participate in the other mid-term, no exceptions.

Second mid-term: the second mid-term is an Excel exercise (no Access, which everybody will do at the exam), identical to an exam's Excel exercise and it is done at the end of Excel classes (currently planned on 22th November 2017 at 14:00, but may change in case of problems). Only students who got at least 60% on the first mid-term mey participate. This mid-term 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 students who reach at least 60% use this grade to skip the Excel exercise at the exam.

Mid-terms are independent, you do not need to pass both. The only constraint is to pass the first one, otherwise you may not participate in the second one.
Your mid-terms grades will last until the last session of current A.Y., even if you fail an exam. If you do NOT want to use one of your mid-terms grades in an exam because it is too low for you, you MUST write an email to dr. Paolo Coletti AT LEAST 7 days before the exam.

There is no 60% minimum per exercise at the exams, this rules applies only to mid-terms. Mid-terms attempts do not count as exam's attempts towards the limit of two exams per year (if regulations do not change).

Differences from A.Y. 2016/17

Please, if you followed the course in A.Y. 2016/17 take note of these important differences:

Study resources

Topic
Lessons' slides
Exercises' slides
Videos as alternative to attendance
Books as alternative to attendance
Theory

- Computer introduction
- Windows
- Computer networks

 

Precourse video down here

(for the rest of this part use the book)

Basic Information Systems course book
Excel
Excel

Excel

Extra exercises on data analysis

Go down here for lessons videos

Videos of some exercises classes are also available

Both these:
- Excel 2007 for Dummies: Chapters 2-5, 6 (pages 209-215, 227-229), 7 (pages 231-240), 8, 9, 10 (pages 287-308), 11 (pages 329-332)
- Excel 2007 Data Analysis for Dummies: Chapters 2 (pages 37-44), 3 (pages 66-71), 4-7, 9 (183, 185, 188, 195, 197, 199-202), 10 (223-231), 12 (263-274)

Access
Access (no slides)

Go down here

Databases course book

 

Files used in class

All files in one package

Excel function translation for those who have Excel in German or Italian

Videos of lessons

Warning: videos are large, your best choice is watching them online from YouTube.

If you really want to download them, be sure to have enough space and enough time available. Do not save them on your UNIBZ disk space or you will make your account go over quota! Right click the mouse button and choose Save Target As... and save them either on a USB pendrive on your personal computer. If you have a Mac and the video does not open with QuickTime, try to install and use program VLC http://www.videolan.org/vlc/download-macosx.html

precourse.avi
116 MB
YouTube Precourse on unibz network and file handling.
precourse 02.avi
24 MB
YouTube Update for 2016
excel01.avi
65 MB
YouTube How to activate files' extensions. How to set decimal and list separator for Windows and for Excel. Microsoft Excel introduction, sheet and cells. Cells’ references, columns, rows, name box, formula bar, status bar with autocalculate, sheets tab, sheets operations. Selection of rectangular and non rectangular areas. Data types. Force Excel to accept text. Values: general, number, currency, date, time, percentage. Formulas. Text. Cell format dialog box. Data entry and conversion: problems with dates and with percentages.
excel02.avi
158 MB
YouTube Autofill, autoincrement. Copy, paste, paste value only. Importing fixed fields tables, tab-delimited or character-delimited tables. Excel formulas: mathematical operations, typical errors. Cells references: relative, absolute, formula dragging. Functions and help on functions. Mathematical functions: LOG, LN, PI, SQRT, EXP, ABS, ROUND, ROUNDUP, ROUNDDOWN, RAND, RANDBETWEEN. MAX, MIN, SUM.
excel03.avi
104 MB
YouTube AVERAGE, SUMIF, COUNTIF, AVERAGEIF. Logical functions: IF, AND, NOT, OR. Text functions: LEN, RIGHT, LEFT, CONCATENATE, REPT.
excel06.avi
156 MB
YouTube Statistical functions: STDEV.S, VAR.S, NORM.DIST, NORM.INV. Multiple sheets references and multiple files references. Goal seek. Scenario manager. Assigning cell's name. Sensitivity analysis with data table. Excel solver. Printing: Page setup, Orientation and scaling, Margins and centering, Header and footer, Printing area, Gridlines. Page breaks viewer.
excel07.avi
73 MB
YouTube Document formats, PDF files. Column size calibration, column hiding and unhiding. Automatic styles. Conditional formatting, managing rules.
excel08.avi
79 MB
YouTube

Charts: chart wizard, selecting non-rectangular areas, changing data range and custom series, add and modify data, layout, style, location, plot area features. Axis features, legend, titles, labels, pie chart features.
Chart types for 1 data series: column, bar, line, area, pie. Multiple data series: selecting non-rectangular areas, clustered and stacked column/bar/line/area, scatterplot, bubble. Difference between scatter plot and line plot. Selection of non-rectangular areas. Illustrations' shapes: non transparent text box, arrow.

excel09.avi
152 MB
YouTube Sheet protection. Splitting and freezing panes. Inserting comments.
Lists (small databases): sorting, filtering. Pictures. Hyperlinks.
Pivot table, filtering, sorting, pivot chart, grouping.
Analysis toolpak installation. Descriptive statistics, histograms, changing bins for histograms.
access00_27000.avi
124 MB
YouTube Database architecture overview: single table databases, two table databases, relations, primary key, ID, many-to-one, one-to-many, many-to-many, junction table.
Northwind database overview. Access overview, Saving operations. Tables, field types, primary key. Queries.
access01_27000.avi
17 MB
YouTube Queries, query wizard, design view, sorting, criteria.
access02.avi
65 MB
YouTube

Using other fields for criteria, asking for values, virtual fields, expression builder, functions DateDiff, DateAdd, Year, Between.

YouTube

This short video illustrates how to reach unibz network folder \\ubz01fst (which contains course_coletti and your own personal stuff) using VPN when you are connected from outside university or when you are connected using wifi.
This procedure is not part of exam's stuff.

excel exercises 01
141 MB
YouTube 00:00 basic Excel; 04:40 file types; 08:00 importing text files; 23:20 automatic series. Video by CZ
excel exercises 02
161 MB
YouTube

00:00 references; 02:20 functions; 04:15 mathematical functions; 14:50 relative and absolute references; 26:55 references from other sheets. Video by CZ

excel exercises 03
142 MB
YouTube 00:00 logical functions; 23:00 aggregate functions SUMIF COUNTIF AVERAGEIF. Video by CZ
excel exercises 04
156 MB
YouTube 00:00 text functions; 04:50 date functions, ONLY for ISDM 27006; 18:50 DATEDIF function, ONLY for ISDM 27006; 25:20 statistical functions. Video by CZ
excel exercises 05
173 MB
YouTube Charts

Exam

Before the exam or the practical mid-term:

  1. if you use your own computer, make sure that it works perfectly and everything is installed;
  2. check that you are able to locate \\ubz01fst.unibz.it\Courses directory. You will not be helped during the exam on this topic;
  3. if you are using unibz computer and even if you are using your own computer, make sure that you are able to us the computers in classroom A518 and that your unibz account is properly configured and working correctly, in particolar that you are able to save correctly files on your Desktop. Some of you are over quota (see Basic Computer course book in section 2.3.5 if you do not understand what I am talking about) and this can prevent you from saving files on your Desktop. Coming to one of my office hours before the exam can be helpful if you are not sure of this;
  4. do not come to the practical test without having ever logged on your account to check everything.

Frequently Asked Questions

Q: Where can I get Windows, Word, Excel, Access, 7-Zip?
A: You can download 7-Zip from www.7-zip.org, for Mac the compression program I suggest is Keka www.kekaosx.com/en/. For Windows 10 and Office 2016 for Mac you can download them from http://knowledge.scientificnet.org/software entering with your UNIBZ login and legally install them on your own computer. For Office 2016 for Windows, follow the installation instructions for Office 365 and then you will find a link to install Office 2016 on your computer.

Q: How can I have Windows, Office in English?
A: It is a chaos. Every version of these programs and every edition has different ways and rules to change the language and for some it is not possible. I suggest typing on Google "how to change menu language for" followed by the program and the version you need (Windows 10, Office 2016, Office 2016 for Mac).

Q: Can I take the exam using another operating system or other programs' version?
A:
You may do whatever you want. However I check and double-check that the exam runs smoothly without any bug nor strange situation only for Windows 10 and Office 2016 and for a turn only (see next question) with a Mac. Very likely with Windows 7 or 8 and Office 2010 or 2013 you are able to do the exam without any problem, but I do not take any responsibility if you do not find these programs installed anymore or if they are unable to perform some tasks.

Q: I have a Mac. May I study using it?
A: If you have a Mac, you have several alternatives. My suggested solution is number 3.
1) You can partition your hard disk in two parts and install Windows (the copy offered by university) in the other part. There are many tutorials on the web how to do it, for example see http://support.apple.com/kb/ht1461. The ISO image for Windows and Office with unibz license are here: http://knowledge.scientificnet.org/software . In this way you will have a perfect copy of Windows and Office, including Access, installed at unibz.
2) You can install Windows on a virtual machine using free program VirtulBox which is free. In case you are interested, here are the instructions: https://www.howtogeek.com/186907/how-to-install-windows-on-a-mac-with-boot-camp/ , and here you get the ISO image for Windows and Office with unibz license here: http://knowledge.scientificnet.org/software .
3) You can install unibz Excel for Mac from here http://knowledge.scientificnet.org/software and attend the course using your Mac for the Excel part and a computer borrowed from the library for the Access part. For the exam you can use your Mac for the Excel part and unibz computer for the Access part. Please check that your Mac works correctly, that you have all the necessary programs installed and that you are able to access unibz network directories. In this case you are strongly advised to tell it to me via email before enrollment is closed so I can put you in a special turn in which the exam is also tested for Mac.

Q: How can I reach network folder \\ubz01fst from outside unibz or connected via wifi?
A: For Windows users: if you are connected to wifi ScientificNetwork try to digit in any explorer address bar \\ubz01fst.unibz.it and see whether you reach it. You need to provide your login and password, but you need to tell to your computer that you are using a different domain and then you have to type unibz\loginname instead of simply loginname. If this fails or if you are no connected to ScientificNetwork, then you need to install VPN. There is a specific video up here.
For Mac users: if you are connected to wifi ScientificNetwork, Finder -> Go -> Connect to server -> smb://ubz01fst.unibz.it . You need to provide your login and password, but you need to tell to your computer that you are using a different domain and then you have to type unibz\loginname instead of simply loginname.

Q: May I fix an appointment to talk with you?
A: I do not fix personal appointments. I have office hours explicitely dedicated to this task, which I try to scatter evenly during the semester with a higher frequency before exams. In any case all the office hours are open to everybody, so you may come also to office hours of my other courses, see list on www.paolocoletti.it/timetable. Obviously you may always ask me questions via email (please, state clearly which course are you talking about and what is your problem).

Q: When will the next exam be? Can you give me a hint on the exam's date because I have to catch a plane? Can you move the exam's date? Can you fix the exam's date on the week I suggest?
A: Please stop writing me emails on this topic. Exam date appears on your timetable as soon as it is official. If you have something to say about it, contact your students' speaker who is the only one who can submit requests on students' behalf.

Q: May I take 27000 exam as part of 27006 exam? May I take 27000 exam at the day of 27006 exam? May I take 27006 exam instead of 27000 exam? May I take 27006 exam at the day of 27000 exam?
A: No, no, no, no. Even though 27000 and 27006 have partly a similar content, the two courses are different, with different content, with two different exams done with different procedures in (usually) different days and sessions. You may not come to the other exam nor you may do a part of your exam during the day of the other one. Please, consider these two exams as if they had completely different names. Discover exactly what your exam is, checking carefully your study plan and asking to the secretary in case of doubt. The right lessons and exams should appear in your timetable. I do not take any responsibility if you attend the wrong course or, even worse, if you show up at the wrong exam.

Q: I may not enrol online for technical or administrative reasons or I forgot to enrol or it is my third attempt and I cannot enrol. Can I do the exam anyway?
A: No, I may not let non-enrolled students take part of the exam. Do not ask me to do illegal things! Ask the secretary whether there is something they can do.

Q: May I do the exam with my computer?
A: Sure. But beware: (1) you must be able to navigate the Internet and to enter directory \\ubz01fst.unibz.it\Courses\Course_Coletti. Do not wait for the day before the exam to check it. (2) You are responsible for your different programs' versions and configurations and for the absence on your computer of specific programs.
In any case you will have a unibz desktop computer in front of you.

Q: May I do the exam using Windows in a different language?
A: Yes, sure. However pay attention that Excel functions which will be very different. I suggest you to study them directly in your Windows version and, in any case, to take the conversion sheet with you during the exam.

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. These parts usually appear as stricken out in the exam's text and appear always in the solution and videosolution.

Q: I am a student of course XYZ. Which exam do I have to take?
A:
Some situations are very complex, therefore the only place which you must trust is your student's portal. You should see there all your exams and their respective code and you should see lessons and exams on your timetable. In case of doubt, ask the secretary. Please discover exactly which one is your exam. I do not take any responsibility if you attend the wrong course or, even worse, if you show up at the wrong exam.

Q: Help me! I can not save my files.
A: Before the exam you must empty your email folders of all the big stuff you have inside. Moreover go to \\ubz01fst\students, locate your directory, and searching through all your subdirectories delete all the big or not necessary files. In this way you will have enough space to save everything. If you cannot save due to a shortage of disk space, we can not and will not do anything to help you.

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 unreliability 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 practising file copy even during the exam, is your task and is official part of the evaluation for this course.

Q: Hey, exam's time is not enough! I could not even finish it. If I only had other 5 minutes I would have done it much better!
A: Sorry but you are wrong, as I calculate more than twice the needed time. Look at the important warning after exam's explanation: the fact that for you exam's time was not enough is instead an indication that you must do many more exercises to be efficient and fast enough. On the other hand, if you have documented medical problems that slow your operations, write an email to me to have more time.

Previous exams

Warning: videos are large, your best choice is watching them online from YouTube.

Session
Exam link
Solution link
Video solution
Video solution
September 2017 - 58
exam
suggested solution
YouTube
July 2017 - 57
exam
suggested solution
YouTube
February 2017 - 55
exam
suggested solution
YouTube
December 2016 - 54
exam
suggested solution
YouTube
Mid-term 2016 - 53
exam
suggested solution
YouTube
Autumn 2016 - 52
exam
suggested solution
YouTube
Summer 2016 - 49
exam
suggested solution
YouTube
The bubble chart in exam 49 might look completely different according to the Office version and whether you select the cells exactly or the entire column. Do not worry. The only thing to check is, using Select Data, that your series are on the right axis: column F for the x axis, I for the y axis and B for bubble size.
Winter 2015 - 47
exam
suggested solution
YouTube
exam47-27000.avi
(45 MB)
Autumn 2015 - 46
exam
suggested solution
YouTube
exam46-27000.avi
(44 MB)
Summer 2015 - 44
exam
suggested solution
YouTube
exam44-27000.avi
(37 MB)
Winter 2015 - 41
exam
suggested solution
YouTube
exam41-27000.avi
(45 MB)
Autumn 2014 - 39
exam
suggested solution
YouTube
exam39-27000.avi
(48 MB)
Winter 2014 - 37
exam
suggested solution
YouTube
exam37-27000.avi
(61 MB)
Autumn 2013 - 36
exam
suggested solution
YouTube
exam36-27000.avi
(48 MB)
Winter 2013 - 33
exam
suggested solution
YouTube
exam33-27000.avi
(38 MB)
autumn 2012 - 32 for 27006
exam
suggested solution
YouTube
exam32.avi
(60 MB)
spring 2012 - 31 for 27006
exam
suggested solution
A YouTube B YouTube
exam31A.avi
(79 MB)
exam31B.avi
(61 MB)
autumn 2011 - 30 for 27006
exam
suggested solution
YouTube
exam30.avi
(38 MB)
autumn 2011 - 29 for 27006
exam
suggested solution
YouTube
exam29.avi
(45 MB)
summer 2011 - 28 for 27006
exam
suggested solution
YouTube
exam28.avi
(80 MB)
summer 2011 - 27 for 27006
exam
suggested solution
YouTube
exam27.avi
(69 MB)
spring 2011 - 26 for 27006
exam
suggested solution
A YouTube B YouTube
exam26A.avi
(38 MB)
exam26B.avi
(52 MB)
spring 2011 - 25 for 27006
exam
suggested solution
A YouTube B YouTube
exam25A.avi
(40 MB)
exam25B.avi
(44 MB)
autumn 2010 - 24 for 27006
exam
suggested solution
Warning: these exams are not updated any more to the current course's modifications.
You might thus find questions or requests which are not part of the course anymore.
summer 2010 - 23 for 27006
exam
suggested solution

summer 2010 - 22 for 27006

exam
suggested solution
autumn 2009 - 21 for 27006
exam
suggested solution

autumn 2009 - 20 for 27006

exam
suggested solution
summer 2009 - 19 for 27006
exam
suggested solution

summer 2009 - 18 for 27006

exam
suggested solution
autumn 2008 - 17 for 27006
exam
suggested solution

autumn 2008 - 16 for 27006

exam
suggested solution
summer 2008 - 15 for 27006
exam
suggested solution

summer 2008 - 14 for 27006

exam
suggested solution

autumn 2007 - 13 for 27006

exam
suggested solution
summer 2007 - 12 for 27006
exam
suggested solution
summer 2007 - 11 for 27006
exam
suggested solution

summer 2007 - 10 for 27006

exam
suggested solution
autumn 2006 - 9 for 27006
exam
suggested solution
autumn 2006 - 8 for 27006
exam
suggested solution
summer 2006 - 7 for 27006
exam
suggested solution
summer 2006 - 6 for 27006
exam
suggested solution
summer 2006 - 5 for 27006
exam
suggested solution
prototype A.Y. 2005/06 - 4 for 27006
exam
suggested solution

This page is maintained by Paolo Coletti.

Marisa Crucitti il teatro per ringiovanire Paolo Coletti personal page La stanza dell'arte Paolo Coletti Paolo Associazione culturale e ricreativa Kender Trento Bolzano La stanza dell'arte Marzia Centro Felix Trento Aarghen Thael Il Vecchio Continente GURPS Marisa Crucitti il teatro per ringiovanire Laboratorio d'arte Gabbana cornici Rovereto Nursing Up sindacato infermieri Bolzano ASL Italia Advanced Squad Leader Club scherma Bolzano Bozen Fecht club spada fioretto sciabola