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

Exam code 30104

Information Systems and Data Management in Bruneck

In charge of the exam: Dr. Paolo Coletti Paolo.Coletti@unibz.it .

Office I303 - Office hours: www.paolocoletti.com/timetable

Website: www.paolocoletti.com/informationsystems

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).

Course

Prerequisites

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

  • English understanding and reading: you must be able to read and understand spoken English at level B2. If, for example, you have problems reading this website, you will not follow the course correctly.
  • Microsoft Windows or Mac: you must already know how to use an operating system in its basic aspects
  • File handling: you must have very good practice on how to move, delete, rename, copy, compress, uncompress files and directories. These are activities with which some students have severe problems.
  • Internet: you must be able to navigate, send and read email, use basic UNIBZ network features and download files.

Course content

  • File handling precourse. Even though this is a prerequisite, it is offered for those students who do not have very good skills in file handling skills to close their gap.
  • Basic computer, Windows, networks and security. This part is theoretical and we will see topics which are of everyday use in a typical office environment.
  • Microsoft Excel. In this part we will see the basic graphs, formulas and functions of Excel. This part contains also a section on financial Excel applications and on creating functions with VBA.
  • Relational databases and Microsoft Access. In this part we will see relational databases' architecture, and how to build and modify simple databases with Access.

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. The main difference with respect to other courses is that you have to train much more than study. The content of this course is easy and does not need extensive study, however, it is only with practice that you become skilled enough and know immediately what to do without wasting time.

 

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

  • questions on computer, Windows, networks (20% of the grade). This consists in a set of true/false sentences. For each one, you may choose not to answer getting 0 points, answer correctly getting 1 point or answer wrongly getting -1 points. However, the total score of this part cannot be negative;
  • written test on relational databases (20% of the grade).

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

  • practical test on Excel, financial functions and VBA (40% of the grade);
  • practical test on Access (20% of the grade);
  • any file handling error (for example wrong names, double extension on the same file, extra or wrong directories, extra files, multiple versions of exam's files and all other files' mistakes) counts negatively towards the final grade.

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.

 

Before the exam:

  1. if you do not use your own notebook, borrow a computer from the library well before the exam and configure it before the exam
  2. make sure that Office is installed and working;
  3. if you are paranoid and are afraid that your notebook crashes the night before, some days before the exam borrow a computer from the library and check that you are able to log in on your unibz account and that you have, or are able to install, everything you need

Modifications due to Covid

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. 

 

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

– Cryptocurrencies and blockchain technology

  

Precourse video below

Cryptocurrencies video below

Dark web video below

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 below for lessons videos

Go below for exercises videos

  
Relational databases
 Relational databases

Relational databases and
Solutions

Go below here

  
Access
 Access

Databases

 Go below here

 

Videos of lessons

Video Title
Link
 Description
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, 18:50 DATEDIF function; 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 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 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.
access03
 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  

 

FAQs

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, straight vertical, curly braces on the German keyboard of a Mac?
A: Did you 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 type square bracket, at symbol, curly braces on the Italian keyboard of a Mac using VMware?
A: Did you buy a Mac with an Italian keyboard? I do not understand why the left Option-Alt key is working only for the euro symbol. Here the shortcuts:

  • square brackets [ and ] are obtained with RIGHT ALT+8 and RIGHT ALT+9
  • at symbol @ is obtained with RIGHT ALT+Q
  • curly braces { and } are obtained with RIGHT ALT+7 and RIGHT ALT+0

Q: May I fix an appointment to talk with you?
A: Sure, write me an email and we can fix it online or live. 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's 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 use the operating system in a different language?
A: Yes, sure. Your business.

 

Q: Will the exam be similar to the other exams on this website?
A: Sure. 

 

Q: I lost a file during the practical 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 practical 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.

 

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
Autumn 2021 – 80
YouTube
Summer 2021 – 78
Winter 2019 – 67
YouTube
September 2018 – 64
YouTube
Summer 2018 – 63
Mid-terms 2018 – 62
A YouTube   BYouTube
Winter 2018 – 61
YouTube
September 2017 – 58
YouTube
July 2017 – 57
Mid-term 2017 – 56
Winter 2017 – 55
YouTube
Autumn 2016 – 51
YouTube
Summer 2016 – 50
Winter 2016 – 48
YouTube
Autumn 2015 – 45
YouTube
Summer 2015 – 43
Winter 2015 – 42
YouTube
Autumn 2014 – 40
YouTube
Summer 2014 – 38
Autumn 2013 – 35
YouTube
Summer 2013 – 34
YouTube
autumn 2012 – 32
YouTube
spring 2012 – 31
autumn 2011 – 30
YouTube
autumn 2011 – 29
YouTube
summer 2011 – 28
YouTube
summer 2011 – 27
YouTube
spring 2011 – 26
spring 2011 – 25
Instagram Youtube Tiktok

Powered by Paolo Coletti

2022 All right reserved