Databases

ISTDBM1 - 3 ECTS - 4th Edition

IST-4-DBM1 - ECTS 3.0

Abstract Part 1: Databases

This course introduces the most important concepts concerning the design and the use of databases. It covers the main issues in data modeling when considering the relational data model (structure, languages like relational algebra and relational calculus) and the key features of attribute-oriented models with respect to constructor-oriented models (like, for instance, Extended Entity-Relationship models). The fundamental concepts like the constraints and the normal forms will be explained and their impact on the quality of designed databases will be discussed. Finally, practical aspects of relational database design and use will be discussed. It includes a presentation of the SQL query language but also a short introduction to data warehousing and On Line Analytical Processing queries (OLAP). To be concrete, the students will have to understand the design of a real relational database and to practice database querying with a professional DataBase Management System (DBMS).

Note: Slides are taken and partially modified from https://www.db-book.com/

SCHEDULE

Topic		Day	Date	From	To	Material	Video	Comment
Introduction to DB	📓	Thursday	2024/09/26	14:00	16:00	Slides
Entity-Relationship Model	📓	Monday	2024/09/30	16:00	18:00	Slides Exe1 Exe2
Entity-Relationship Model	🖐	Thursday	2024/10/03	16:00	18:00	exercises
Relational Algebra	📓+🖐	Tuesday	2024/10/08	16:00	18:00	Slides Exercises	Video
Normal Forms	📓	Friday	2024/10/11	16:00	18:00	Slides Sudeepa Roy
SQL	🖐	Monday	2024/10/14	16:00	18:00	Slides
Project Work	🖐	Thursday	2024/10/17	16:00	18:00
Exam	Exam	Friday	2024/10/25	10:00	12:00	Example exam results
Project Submission	Exam	Sunday	2024/12/08	00:00	00:00
Project Presentation+Panettone	Exam	Monday	2024/12/09	08:00	10:00
Project Presentation+Panettone	Exam	Monday	2024/12/09	16:00	18:00

NB: The course schedule can be subject to changes!

The course exam consists into

• 1h test including 10 questions and 3 exercises (ER Modelling, Relational Algebra, SQL)
• a small project to perform in pairs . Each project should have a presentation and 2-5 pages project report (mandatory) and a presentation.

Final grade is the weighted average between exam (0.4) and project (0.6)

Project Submission and Setup

• Course Repository
• Project Registration and Submission Form

Examples

• Example of Report 1
• Example of Report 2
• Example of Presentation
• Example of Extra Web App

Project Checklist

• Identify the domain of choice, e.g., internet memes
• formulate some questions in natural language that you would like to answer
  • submit the questions for approval
• design the database and perform data entry in Postgres
• ER Diagram and comments about the design decision (using appropriate terminology)
• Implement the questions in SQL, analyse the query plan in relational algebra, experiment alternative implemetations
• Basic queries should cover standard RA operators, Joins, and aggregation functions
• Advanced features nice to show example of
  • Indexes
  • Transactions

NB: Full-stack application is not required, but encouraged.

Project Grading

• 1-15
  • Natural Language Queries
  • ER Diagram Design
  • SQL Implementation
    • DDL + Data Entry
    • Basic SQL Usage (no joins) + Relational Algebra (should also go in the presentation)
  • Intermediate SQL usages
    • Joins and Aggregations
    • Integrity Constratints (check or queries) + Ad-hoc data to test
    • Indexes and Transaction
• 15-20
  • 10 minutes Presentation ( you both have to speak) + 5 minutes Questions (Individual)
    • Use of appropriate terminology (also when speaking)
    • Clarity of the explanation
    • Appropriateness of the answer
  • What to put in the report
    • a general presentation
    • design decisions
    • ER + Implementation
    • problems that you solved
  • what to put in the presentation (4-8 slides):
    • a general presentation
    • ER + Comments of entity
    • Example of SQL + RA
    • Demo is welcomed
• yes, you can decide not to present (max grade is 15)
• up to 3 extra points can be assigned to reward intercation, support to other projects, initiative in project execution ()

Proposed Dataset

• Meme Datasets:
  • Know Your Meme Scraped
  • DBPedia Enrichment
  • Google Vision Dataset
  • Instances from Image Flip
• Industrial ERP (shared by email) (Courtesy of LEM International)
• Hackathon Dataset (Courtesy of Prof Alex Nolte)

Suggested Material

• Book
• Slides
• Exercises + Solutions
• ER-Diagram
• Test Normal Forms

Pre Lab Installations (Courtesy of Mohamed Ragab)

Lab Repository

1. Install PostgreSQL
   1. On Docker ``
   2. on Windows
      • Download and Install PostgreSQL for all platforms
      • Follow this tutorial for more details (Windows installation).
   3. on linux
      • Follow this tutorial
2. Install the psycopg2 Python Package to connecto to PostgreSQL
   • use th command

     1	pip install psycopg2

     in your command line.
3. Accessing PostgreSQL from Command Line:
   • Add the PostgreSQL installation \/home\ and \/bin\ directories to the enviroment variables.
   • use the command

     1	psql -U postgres postgres

     to connect to the the by-default created database \postgres\ with the user \postgres.
   • Enter your set \postgres\ password (i.e password of the default password that you have been asked at the time of installtion).
4. Connect To a PostgreSQL Database Server
   • show you how to connect to the PostgreSQL using psql tool and pgAdmin 4 GUI tool.

Previous Years

• 2023
• 2022