Management Engineering

FRANCESCO LEOTTA

General Objectives:
The aim of the course is the presentation of the fundamental database principles and the corresponding management systems. The topics are presented from various points of view, covering theoretical, methodological, technological and application aspects. At the end of the course the student should have acquired not only the theoretical knowledge on the subject treated, but also the sufficient methodological techniques and tools to address a complete database project.

Specific objectives:

Knowledge and understanding:
The main models and languages for the design and management of databases.

Apply knowledge and understanding:
Being able to design a database starting from the requirements by defining the conceptual schema completed with appropriate constraints; translate this schema into a logical model and implement it on a relational DBMS, equipped with relevant queries.

Critical and judgment skills:
Being able to evaluate the quality of a database project and the correctness of the applications used in its management.

Communication skills:
The project activities and the course exercises allow the student to be able to communicate / share the requirements of a database, as well as the design choices and the development methods.

Learning ability:
In addition to the classic learning skills provided by the theoretical study of the recommended material, the course structure, in particular the project activities, stimulates the students to deepen their knowledge of the topics, working in team, and to practically apply the concepts and techniques learned during the course.

At the end of the course, the student will be able, starting from the analysis of the requirements provided by the customer, to design a relational data base in third normal form. The student will be also able, by using command line tools, to create, populate and query a data base by using the SQL language.

No previous knowledge required but basic knowledge of computer usage

Preliminar notions - information systems, databases, database management systems
The relational model
Relational Algebra
The SQL language
Definition and manipulation of data in SQL
Nested queries in SQL
Check constraints, assertions and view
Conceptual design
Logical design

R. Ramakrishnan, J. Gehrke. Sistemi di basi di dati. McGraw-Hill, 2004.
P.Atzeni, S.Ceri, S.Paraboschi, R.Torlone. Basi di Dati. Modelli e Linguaggi di Interrogazione. McGraw-Hill, 2014.

No mandatory participation to lessons is required.

Examples of previous exams are available at the link http://www.diag.uniroma1.it/leotta/basididati2021.html. In the following we report an example:

QUESTION 1 (10 points) (minimum required points to pass 5)

Starting from the following requirements, produce an Entity-Relationship diagram

The Ministry of Education wants to take a census of maintenance work in the classrooms of its schools. For each school it has to be taken information on the name, the address (a string) and the number of floors. For each classroom, which is identified by a unique code within the school in which it is located, the number of windows and the surface area in square meters are of interest. The teachers working in schools (at least one) are also relevant for this database. For each teacher (who can work in one and only one school) it has to be taken information on the tax code (ID), name, surname, date of birth and year of graduation. Maintenance work can be carried out on classrooms. Each maintenance work is carried out by a company on a classroom and has a unique code within the classroom. The cost, start date and end date (if any) of
each maintenance work are also relevant. For each company, it has to be taken information on the (identification) VAT number, the company name, the annual turnover and the owner(s) (each company has at least one). For each owner, the fiscal code (identification), the name, the surname, the date of birth and the educational qualification are of interest. Please note that each owner can have several companies in his name (at least one). Please note also that a teacher cannot be a company owner (due to a conflict of interest) and vice versa.

QUESTION 2 (8 points) (minimum required points to pass 3)

Do the logical design of the ER-diagram you produced in the previous question, minimizing the number of NULL values in the database.

QUESTION 3

Consider the following relational schema:
Vehicle(Plate, Manufacturer, Model, Engine)
Person(TaxCode, Name, Surname, DateOfBirth, RegionOfResidence)
OwnedBy(Plate, Code, DateFrom, DateTo)
Crash(PlateA, PlateB, Date, City, Region)

Storing information on vehicles, past owners of the vehicles (each vehicle could have had more owners in the past), and on crashes.
1) Find the tax code of people who never owned a vehicle with an engine bigger than 1500cc. (3 Points)
2) Find the tax code, name and surname of people whose vehicles were never involved in crashes occurred in regions different from their region of residence. (4 Points)
3) Find for each region of residence, the manufacturer that produced more vehicles that are owned by people that have the residence in that same region. (4 Punti)

QUESTION 4 (3 points)

Define the concepts of logical, physical, and external schemas, and discuss the concepts of physical and logical independencies.

• The schedule of the course is available on the website https://sites.google.com/diag.uniroma1.it/basididati2526/home-page