Computer science for data management
Academic Year 2024/2025 - Teacher: Gaetano STELLAExpected Learning Outcomes
Provide the knowledge and tools to collect, organize, analyze, and visualize data from multiple sources.
The course allows you to learn the basics of two types of tools: spreadsheets and relational database management systems (RDBMS).
The student will acquire the basic knowledge for the management of spreadsheets with Microsoft Excel 365, the design and implementation of a database through the use of Microsoft Access and the use of the SQL language.
Course Structure
Lectures in which the topics will be introduced.
Demonstrations in which the teacher will show the practical use of software applications.
Exercises to be carried out with the assistance of the teacher to verify learning.
Required Prerequisites
Attendance of Lessons
Detailed Course Content
Module A: Fundamentals of Data Management
- Data and Information
- Importance of Data Management
- Digital Data Preservation
- Types of data
- Data management tools
Module B: Microsoft Excel
- Introduction to Spreadsheets
- Folder and Worksheets
- Cells and data type
- Perform calculations on data
- Absolute and Relative Addressing
- Working with Formulas
- Sorting the data
- Graphs
- Pivot Tables
- Page Layout
- Macros
Module C: Elements of Statistics and Probability
- Introduction to Statistics
- Position Indices
- Average
- Weighted Average
- Dispersion indices
- Range
- Variance
- Standard deviation
- Covariance of two statistical variables
- Introduction to Probability
- Probability of an event
- Certain events
- Adverse events
- Compound events (dependent and independent)
- Intersection of events
- Merging Events
- Introduction to Bayes' theorem
Module D: Database
- Introduction to databases
- Relational Database Management System (RDBMS)
- Design a database
- Relationship Entity Schema
- Tables and Reports
- Microsoft Access (RDBMS)
- Create a database
- Tables and Records
- Primary Keys and Foreign Keys
- Views
- Forms
- QBE
- Structured Query Language (SQL)
- Data Manipulation Language (DML)
- Insert, Update, Delete
- Data Definition Language (DDL)
- Create, Drop, Alter
- Data Query Language (DQL)
- Select
- Databases using SQL (with reference to Microsoft SQL Server)
- Searching for Data (Queries)
- Inserting, Editing, and Deleting Records
- Sorting the Result of a Query
- Joined queries
- Grouping data
Textbook Information
2- Microsoft Access 2002 Step by Step, Microsoft Press.
3- Microsoft SQL Server 2012 Step by Step, Patrick Leblanc, Microsoft Press.
4-slide
Course Planning
| Subjects | Text References | |
|---|---|---|
| 1 | Introduction to the course; Data and information; Importance of data management and hints on Big Data. | 4 |
| 2 | Preservation of digital data; file types (text files and binary files); file system; Local and cloud storage. | 4 |
| 3 | Structure data and unstructured data; Information systems; Overview of Data Management Tools (RDBMS). | 4 |
| 4 | Elements of statistics - concepts; Qualitative and quantitative data; representation tools; Position indices (average, weighted average, mode). Dispersion indices | 4 |
| 5 | Elements of probability calculation; independent events and dependent events (compatible and incompatible); Intersection and union of two events; conditional probability. | 1 |
| 6 | Introduction to Microsoft MS Excel; worksheets; feature; cell addressing (relative and absolute); construction of formulas; Data formatting; | 1 |
| 7 | MS Excel functions; conditional functions; application of statistical functions; Graphs; data and tables. | 1 |
| 8 | Pivot Tables; advanced MS Excel functions; Notes on MS OneDrive; Excel office 365 on the web; document sharing; Multi-user worksheet. | 1 |
| 9 | MS Excel: Import and export data in various formats (csv, text, etc.). | 1 |
| 10 | Introduction to databases; overview on various RDBMS (MS Access, MS SQL Sever, Oracle). | 4 |
| 11 | Introduction to database design; objects, attributes, and relationships; Conceptual database and physical database. | 4 |
| 12 | Database languages; DDL and DML;SQL; Relational model; Tables and records | 4 |
| 13 | Conceptual scheme; Design a database; Relationship entity schema; Entities, Attributes, and Relationships. | 4 |
| 14 | Logical design, the relational model. | 4 |
| 15 | Restructuring of the relationship entity schema: elimination of generalizations, partitioning and merging of entities, elimination of multi-valued attributes. | 4 |
| 16 | Translation into the relational model: one-to-many relationship, many-to-many relationship, primary keys, external identifier, associations with multiple entities. | 4 |
| 17 | Microsoft Access: Introduction, interface and menus, Access objects, creating tables, data types, primary keys, and foreign keys. | 2 |
| 18 | MS Access: Query object, operators and formulas in Queries, table joins. | 2 |
| 19 | The SQL language - introduction, DDL statements, data types and constraints on tables. DML Instructions | 2-3 |
| 20 | The SQL language - DQL statements. SELECT statement. JOIN between tables, sorting data, EXISTS operator | 2-3 |
| 21 | The SQL language - Aggregate functions, GROUP BY operator and HAVING clause. Other operators (UNION, ANY and ALL). | 2-3 |
Learning Assessment
Learning Assessment Procedures
During the course, an ongoing test (multiple choice) will be administered on the part of the program relating to statistics, probability calculation and MS excel.
The final exam consists of an oral test on the rest of the program.
The mid-term test is worth 30 points and is considered passed with at least 18 points out of 30. The final oral exam is worth 30 points and is considered passed with at least 18 points out of 30. The final grade will be calculated as the average of the midterm test and the final exam.
For those who do not pass the midterm test or do not use it, the final exam consists of an oral interview on the entire program.
Examples of frequently asked questions and / or exercises
Variance and Covariance Formula
Arithmetic meia
Relationship Entity Schema
Relational databases
Select command syntax