Computer Science Fundamentals

Exercises
Topic 2: Introduction to Databases

Database design
Design the entity-relationship diagram that allows the implementation of a database for the
problems described above. Note that additional fields and/or tables may need to be added, and
both primary keys and foreign keys must be identified.

Before drawing the diagram, you should read the description of each problem in detail and
identify the different entities and relationships. Try to answer the following questions: How many
main entities are there? How many main attributes does each entity have? How many 1:N
relationships are there? How many N:N relationships are there?

1. An engineering company keeps all the information related to its projects and the employees
who work on them. Other important information that we must know:
- For each project, the name, and the budget.
- For each employee, the name, the passport, and the salary.
- Each employee can work on several projects, which in turn can be assigned to several
employees.
- Each project has only one manager, but each employee can be responsible for
several projects.

2. An engineering company keeps all the information related to its departments and employees.
Additionally, employees receive an annual bonus depending on the number of children under 18
years old, so it is also necessary to store it.
- For each department, the name, and the address.
- For each employee, the name, the passport, and the salary.
- For each child, the name, and the birthdate.
- Each employee works in a department, and each department is managed by an
employee.
- Employees can have several children, and both parents may work for the company.

1
 Computer Science Fundamentals

3. A university with several research groups keeps all the information related to its professors and
projects. In particular, research groups are composed of several professors, and some graduate
students carry out collaborative activities in their projects. Other important information that we
must know:
- For each research group, the name, and the area of specialization.
- For each professor, the name, the surname, the office, and the research group to
which they belong.
- For each project, the name, the duration in months, and the budget.
- For each student, the name, the surname, and the projects in which they collaborate.
We must know the number of hours that each student dedicates to each project.
- Research groups can participate in several projects, some of them carried out with
other groups. We must know the number of people in each group that participate in
each project.

4. A university with several degrees keeps all the information related to its subjects, students, and
lecturers. In particular, each subject of a degree has several students enrolled and lecturers who
teach different topics in different subjects. Additionally, some students and lecturers participate
voluntarily in the tutorial plan. Other important information that we must know:
- For each degree, the name and the level.
- For each subject, the name, the type (compulsory, elective), and the degree to which it
belongs.
- For each student, the name, the surname, and the email.
- For each lecturer, the name, the surname, and the office.
- Students are enrolled in several subjects and, for each one, we must know their
marks.
- Each subject is taught by several lecturers, who in turn can teach several subjects.
- Students may participate voluntarily in the tutorial plan. We must know the lecturer
who is in charge of each student in the tutorial plan.

5. A bank is composed of several branches located in different cities of the national territory. The
bank’s customers have bank accounts and loans, which can be shared with other customers
under certain conditions. Other important information that we must know:
- For each branch, the name, the number of employees, and the city.
- For each customer, the name, the surname, and the birthdate.

2
 Computer Science Fundamentals

- For each account, the number, the opening date, the current balance, and the
responsible branch.
- For each loan, the number, the opening date, the amount granted, and the
responsible branch.
- Each account belongs to several customers, who may have other accounts. For each
account, we must know the customer who is the primary owner among all the
customers.
- Each loan belongs to several customers, who may have other loans. For each loan, we
must know the ownership percentage of each customer.