Information Systems and Data Management course - code 30104

Warning: this course in Bruneck does not exist anymore!!! The website is ONLY for the exam. This is not 27000, not 27006, not 27324.

ATTENTION: SPECIAL OFFER !!!

If you fail the exam, you MAY keep any part on which you got at least 70% for the next sessions for maximum 11 months (if I am still in charge of this exam).

 

Lecturer: Dr. Paolo Coletti Paolo.Colettiunibz.it
Office E203 - Office hours: www.paolocoletti.it/timetable
Website: www.paolocoletti.it/informationsystems30104

Course

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, there will be a lesson dedicated to reviewing these topics.

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 reading the book) 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. This is also true for the lessons on database theory, for which the concepts in the first lesson are crucial to understand the next one.

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.

Exam

Exam is divided into two parts.

The first part is held in a standard classroom all students together. This part's time is 45 minutes, but may vary according to the complexity of exercises. 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 45 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 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.

Modifications due to Covid epidemic

Questions

This part will be an oral examination on Teams. Note that oral examinations are public, so there could be other people connected (who obviously cannot speak!). I will not record the exam. I will ask you to show me your surroundings and there must be nobody around nor any course's material nor any other screen. You may hold an A4 paper with you which has one face written with whatever you want, including course's material. You will be evaluated on what you say, how you say it and on how much time does it take to answer (spending 10 minutes reading your A4 paper every time will penalise you).

Database

I will send you the file via email, you draw the schema on paper and take a picture of it or on whatever computer program you wish. Then you send me the picture or the file. During the exam I will call you and ask you to show me your screen. In case during the exam you need my assistance and call me on Teams.

Second part (Excel, Access)

I will send you the file via email and receive the file back via email. During the exam I will call you and ask you to show me your screen. In case during the exam you need my assistance and call me on Teams. You can dro

Cheating

If I suspect you are cheating or have cheated, I will interrupt the exam and report to the Dean. I remind you that the regulations of the Faculty of Economics prescribe for cheating a suspension of one entire session for all exams and one entire year for this exam. It has already happened to some of your colleagues in the past, so please do not even try to cheat.

 

Study resources

Topic
Lessons' slides
Exercises' slides
Videos
Books
Theory

- Computer introduction
- Precourse
- Computer networks

 

Precourse video down here

Cryptocurrencies video down here

Dark web video down here

for the rest of this part use the book on the side

Basic Information Systems book
Excel

Excel

Files for lessons

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

All material

Go down here for lessons videos

Videos of exercises


Relational databases
Relational databases

Relational databases and
Solutions

Go down here

 
Access
Access

Databases

Go down here

Videos of lessons

precourse 01
YouTube Precourse for Windows 10 on unibz network and file handling, first part.
precourse 02
YouTube Precourse for Windows 10 on unibz network and file handling, second part.
precourse Mac 01
YouTube Precourse for Mac on unibz network and file handling, first part.
precourse Mac 02
YouTube Precourse for Mac on unibz network and file handling, second part.
cryptocurrencies and blockchain technology
YouTube A decentralised currency, basic cryptography, Bitcoin history and technology, blockchain technology, advantages and criticisms
A dive into the Dark Web
YouTube Video for educational purposes only. I forbid you from doing illegal activities.
excel 01
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. Autofill, autoincrement. Copy, paste, paste value only. Importing fixed fields tables, tab-delimited or character-delimited tables.
excel 02
YouTube 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.
excel exercises 01
YouTube 00:00 basic Excel; 04:40 file types; 08:00 importing text files; 23:20 automatic series. Video by CZ
excel exercises 02
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

excel03
YouTube AVERAGE, SUMIF, COUNTIF, AVERAGEIF. Logical functions: IF, AND, NOT, OR. Text functions: LEN, RIGHT, LEFT, CONCATENATE, REPT.
excel exercises 03
YouTube 00:00 logical functions; 23:00 aggregate functions SUMIF COUNTIF AVERAGEIF. Video by CZ
excel04
YouTube

Date and time functions: date serial format, time decimal format, additions and subtractions, NOW, TODAY, WEEKDAY, DAY, MONTH, YEAR, DATE, DATEDIF.

excel exercises 04
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
excel05
YouTube Net present value XNPV and internal rate of return XIRR. Internal rate of return: examples of non existence and non uniqueness. Economical meaning of internal rate of return. TAEG and TAN. Mortgage loan with constant rate and constant payments: table, PMT, IPMT, RATE, NPER. Mortgage loan with variable rate: table, variable length, variable payments.
excel exercises 05 (finance)
YouTube ONLY FOR ISDM 27006 00:00 financial functions, 17:54 mortgage loans, 31:50 adjustable mortgage loan. Video by AB
vba01
YouTube Visual Basic for Applications user-defined functions. Function declaration, optional parameters, comments, MsgBox, If Then Else, ElseIf, Nested Ifs. n Internal functions: Ucase , Left, Sqr.
vba02
YouTube Range as parameter, .Cells.Count, .Rows.Count .Columns.Count, For Each Next, Summing
VBA exercises
YouTube ONLY FOR ISDM 27006 Video by AB
excel06
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.
excel exercises 06
YouTube 00:00 Scenario Manager, 13:48 Datatable, 27:58 Goal Seek. Video by AB
excel07
YouTube Document formats, PDF files. Column size calibration, column hiding and unhiding. Automatic styles. Conditional formatting, managing rules.
excel08
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.
excel exercises 07
YouTube Charts. Video by AB
excel exercises 08 (solver)
YouTube Video by AB
excel09
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.

excel exercises 09 (solver 3.3 and 3.6 )
YouTube Solution of extra exercises 3.3 and 3.6. Video by AB
excel exercises 10
YouTube 00:00 statistics with Excel, 07:12 Pivot table, 15:40 Tax exercise, 28:00 data table on tax exercise. Video by AB
databases01
YouTube

Single table database in normal form, primary key, information redundancy, empty fields, one-to-many and many-to-one relations, foreign key.

databases02
YouTube

One-to-one relations, many-to-many relations, junction table, temporal databases.

databases03
YouTube

Junction tables for more relations, details table, foreign keys with more relations, orphans and referential integrity, hierarchical structure, process structure. Suggestions for database design.

access01
YouTube

Northwind database overview. Access overview, Saving operations. Tables, field types, primary key. Queries, query wizard, design view, sorting, criteria.

access02
YouTube

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

YouTube

Summary queries, examples, “where” and “group by”
Reports: structure and examples. Exporting and printing reports.
Tables: Fields and fields’ features. Validation rules, table validation rules. Like operator.
Importing tables from Excel. Building new tables. Relations: relationships diagram, building relations, referential integrity, Lookup Wizard, mandatory and non-mandatory value lists. Forms: structure and examples, subform, locking form and subform.

Access exercise 6.1
YouTube  
Access exercise 6.2
YouTube  
Access exercise 6.3
YouTube  

Exam

Before the exam:

  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 Information Systems 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: What do I need on my computer to attend this course and take the exam?
A:

1) You can download Windows' 7-Zip from www.7-zip.org for free or Mac's Keka from www.kekaosx.com/en/ for free. If you do not have Office, follow the installation instructions on http://knowledge.scientificnet.org/software . If you already have Office 2016 o Office 2013. If you have Office not in English, I give you the translation (eng, ita, ger) of all Excel function used in the course, keep it printed next to you. If you have a Mac or if you have an Office edition without Access, you can use Access through one of the following solutions.
2) Use VMware via web. Go here https://desktop.scientificnet.org , choose the HTML access and enter with your unibz login (use loginname@unibz.it as login name). You will have Windows Labs, which is a virtualized Windows computer, with all the course's programs installed. Alternatively you can use each specific program alone, without the burden of Windows. Pay attention that in this way you will have access only to the \\ubz01fst network disk (My PC or Local Disks are the unibz ones, not yours), not to your own computer's disks. There won't be any problems for the exam nor for attending the course, but it can be annoying.
3) Use VMware via client. Go here https://desktop.scientificnet.org and choose to install the VMware client and choose desktop.scientificnet.org when it asks you the server address. Use loginname@unibz.it when it asks for your login name. You should be able to access your local disk automatically if you say Yes to the many permissions' requests (otherwise, for old versions: from Settings -> Options Tab -> Shared Folders and then from Preferences -> Sharing you can enable access to local disks).

Q: I have a Linux computer. What do I need to attend this course and take the exam?
A:
See solutions 2 and 3 above.

Q: I have no notebook. What do I need to attend this course and take the exam?
A:
You can use the computer of the A518 classroom for the exam and borrow a notebook from unibz library for the course. Everything will be installed properly.

Q: How can I type backslash, square bracket, strainght vertical, curly braces on the German keyboard of a Mac?
A: Did you want to buy a Mac with a German keyboard? For programming it is lacking some fundamental keys. Here the shortcuts:
- backslash \ is obtained with ALT+SHIFT+7
- square brackets [ and ] are obtained with ALT+5 and ALT+6
- straight vertical | is obtained with ALT+7
- curly braces { and } are obtained with ALT+8 and ALT+9

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. Just go to https://vpn.scientificnet.org and follow the instructions to install Cisco Anywhere Client.
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: Sure, write me an email and we can fix it online. Alternatively, you can write me your questions via email or Teams message and I shall answer very soon.

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: 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, almost everybody does it with his/her own computer. 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 programs and configurations and for the absence on your computer of specific programs.
In any case in classroom A518 you will have a unibz desktop computer in front of you.

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

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 unbz cockpit. 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: 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

Session
Exam link
Solution link
Video solution
Summero 2021 - 78
exam
suggested solution
Excel YouTube Access YouTube
Winter 2019 - 67
exam
suggested solution
YouTube
September 2018 - 64
exam
suggested solution
YouTube
Summer 2018 - 63
exam
suggested solution
C Access Only YouTube   A ExcelYouTube   A Access YouTube   BYouTube
Mid-terms 2018 - 62
exam
suggested solution
A YouTube   BYouTube
Winter 2018 - 61
exam
suggested solution
YouTube
September 2017 - 58
exam
suggested solution
YouTube
July 2017 - 57
exam
suggested solution
A YouTube B YouTube C Access only YouTube
Mid-term 2017 - 56
exam
suggested solution
A YouTube A for Mac YouTube B YouTube C YouTube
Winter 2017 - 55
exam
suggested solution
YouTube
Autumn 2016 - 51
exam
suggested solution
YouTube
Summer 2016 - 50
exam
suggested solution
A YouTube B YouTube C YouTube
Winter 2016 - 48
exam
suggested solution
YouTube
Autumn 2015 - 45
exam
suggested solution
YouTube
Summer 2015 - 43
exam
suggested solution
A YouTube B YouTube C YouTube
Winter 2015 - 42
exam
suggested solution
YouTube
Autumn 2014 - 40
exam
suggested solution
YouTube
Summer 2014 - 38
exam
suggested solution
A YouTube B YouTube
Autumn 2013 - 35
exam
suggested solution
YouTube
Summer 2013 - 34
exam
suggested solution
YouTube
autumn 2012 - 32
exam
suggested solution
YouTube
spring 2012 - 31
exam
suggested solution
A YouTube B YouTube
autumn 2011 - 30
exam
suggested solution
YouTube
autumn 2011 - 29
exam
suggested solution
YouTube
summer 2011 - 28
exam
suggested solution
YouTube
summer 2011 - 27
exam
suggested solution
YouTube
spring 2011 - 26
exam
suggested solution
A YouTube B YouTube
spring 2011 - 25
exam
suggested solution
A YouTube B YouTube

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