DataBase and Data Mining Group

Introduction to Databases

Table of content

• Announcements
• Exams
• Weekly timetable
• Materials
• Exercises
• Written tests
• Written tests
• Demo Exercises Solution
• Practices
• Homeworks

General information

• ECTS: 8
• Professor: Tania Cerquitelli
• Assistant lecturer: Jacopo Fior

Announcements

• The results of the written test taken on June 28th, 2021 are available

Exams

• EXAM RESULTS
  • The results of the exam taken on June 28th, 2021 are available  HERE.
    • Students whose final grade is >=18 who do not wish to register their score should write an email with the subject “Introduction to Databases: Rejection” to  Tania Cerquitelli by tomorrow (Saturday, July 10th, 2021).

Weekly timetable

Materials

• Introduction to the course (pdf)
• Introduction to the databases  (2 slides per page, 6 slides per page)
• Relational data model (2 slides per page, 6 slides per page)
• Relational algebra (2 slides per page, 6 slides per page)

• SQL language:

  • Basics (2 slides per page,6 slides per page)

  • The SELECT statement: basics (2 slides per page,6 slides per page)
  • Nested queries (2 slides per page,6 slides per page)
  • Set operators (2 slides per page, 6 slides per page)
  • Update commands (2 slides per page, 6 slides per page)
  • Managing tables (2 slides per page,6 slides per page)
• SQL language: other definitions
  • Management of views (2 slides per page,6 slides per page)
  • Transactions (2 slides per page,6 slides per page)
  • SQL for applications (2 slides per page, 6 slides per page)
  • Access control (2 slides per page, 6 slides per page)
  • Index management (2 slides per page,6 slides per page)
• Database design
  • Design techniques and models (1 slide per page)
  • Conceptual design (1 slide per page)
    • Time representation (1 slide per page)
  • Logical design (1 slide per page)
  • Normalization (1 slide per page)
• Web programming
  • Client-server architectures (1 slide per page)
  • The HTML language (1 slide per page)
  • The PHP language (1 slide per page, lecture_examples)
  • SQL for PHP (1 slide per page)
  • Cascading Style Sheet (1 slide per page)
  • PHP practice (Artworks_text, Artwork_project)
• Triggers
  • Part I (1 slide per page)
  • Part II (1 slides per page)

Exercises

• Relational algebra and SQL I: Exercises [Solutions: Algebra ] [SQL-solutions-Part I,SQL-solutions-Part II]
• SQL no.2: Exercises (Lecture’s notes)
• Relational algebra exercises (Lecture’s notes)
• Relational algebra exercises (pdf) (Lecture’s notes)
• SQL no.3: Exercises  [Solutions]
• SQL no. 4: Exercises  [Draft solutions]
• SQL exercises (pdf) [Lecture’s notes]
• SQL exercises (pdf)
• ER – AirQ Text [Text and solution]
• ER – Cooking school: Text  [Lecture’s notes]
• ER – Beauty Farm: Text – Lecture’s notes
• ER – Bank Text
• Trigger: Ranking (pdf) [Draft solution]
• Trigger: Event (pdf) []
• Trigger: Scholarship (pdf) [Draft solution]

Written tests

• 2015- 07 – 03
• 2015 – 07 – 17 
  •  Draft solution
• 2014 -07 -17
  • Algebra and SQL
  • ER 
• 2014 – 07 – 03  Draft solution
• 2014 01 23
  • Algebra and SQL Draft Solution
  • ER  – Draft solution
• 2013 – 05 – 24 – Draft solution
• Theory questions: Text

Practices

The laboratory practices will begin on Tuesday, March 30.

Only for the first week, the Lab Practice for TEAM B will happen on Tuesday, March 30 from 16:00 to 17:30. There isn’t any change to the schedule for TEAM A.

• Installation guides, to work on the lab practices you can use oracle directly on your PC or online through Oracle APEX. Follow one of the two guides below:
  • Installing Oracle Database and Oracle sql Developer on PC
  • Using Oracle APEX online
• Practice #1 (SQL queries on an Oracle database) 
  • Text and solutions
  • DB creation script
• Practice #2 (SQL queries on an Oracle database)
  • Text and solutions
• Practice #3 (Database design and SQL queries on an Oracle/MySQL database)
  • Text and solutions
• Practice #4 (PHP and SQL)
  • Text
  • ScriptsSQL
• Practice #5 (PHP and SQL)
  • Text and solutions

Homeworks

Rules

To obtain the points associated with the home assignments, students must observe the following terms:

• Complete all the points of the exercises in the homework text.
• Prepare a file in PDF, DOC or ODT format with the solution of the homework.
• Name the file as HomeworkN_Surname_Name_StudentId.XXX, where
  • StudentId, Surname and Name should be replaced with student information
  • the N character following Homework should be replaced with the number of the submitted homework (e.g., ‘1’ for homework no. 1)
  • the filename extension XXX depends on the file type chosen for the submission (PDF, DOC or ODT).
  • the DOCX format is not supported.
  • Since uploaded files are automatically processed, naming the file with a wrong name implies the cancellation of the related homework submission.
• Load the file on the didactic portal (Portale della didattica) in the section Work Submission (Elaborati) before the deadline.
  • Multiple loadings for the same student and/or for the same homework are not allowed.
  • The upload date shown on the didactic portal is considered for the evaluation.
  • Since uploaded files are automatically processed, the upload after the deadline implies the cancellation of the related homework submission.
• During the upload procedure a description (“Descrizione”) field is required. Insert the same name given to the uploaded file (according to the rules described above).
• Only for students with no access to the course page on the didactic portal, the homework may be submitted before the deadline by sending an email to the assistant lecturer.
• Discuss the homework with a positive evaluation on the fixed date (announcement will be published).
• Homework #4 note: The delivery of the realized code must be done via upload on the portal as for the files of the previous homeworks. In this case, however, each student will have to upload a single .zip file containing the developed code.The archive should follow the same naming convention given above. The archive must contain:
  • all .php and .html files created for the application
  • a file named create_and_populate_database.sql (content described in the homework text)
    
  • a file named interrogation_and_transaction.sql (content described in the homework text)

Homework texts

• [Homework #1] — to be delivered by April 12th, 2021 (UTC/GMT+1)
• [Homework #2] — to be delivered by May 3rd, 2021 (UTC/GMT+1)
• [Homework #3] — to be delivered by June 10th, 2021 (UTC/GMT+1) — [Solution]
• [Homework #4] — to be delivered by June 6th, 2021 (UTC/GMT+1) – Deadline extended to June 7th, 2021 (UTC/GMT+1)

Homework results

• List of students who uploaded Homework #1
• List of students who uploaded Homework #2
• List of students who uploaded Homework #3
• List of students who uploaded Homework #4
• Final homework grades