1. Description

The company "Zoo in a slide" is engaged in breeding various animals that can have a name and we can know their date of birth.

If the animal is lucky, it can become the godfather of a well-known personality (celebrity) for which it is essential to know the name, surname and date of birth, so that we do not get the personalities mixed up.

Celebrities can be invited to VIP feedings, where our zoo tries to psychologically push them to promote the zoo with the participation of the media.

Of course, the animals in our zoo are fed not only at VIP feedings, but also when visitors are present - that is, at assisted feedings, where we are mainly interested in the capacity of the persons so as not to attract too many visitors for PR, and also in the name. However, the zoo does not keep track of which visitor participated in the feeding. The zoo records many other types of feedings, but does not track further details for these as it does for assisted and VIP; however, the manager commented along the lines of considering night feedings in the future, but has not yet specified details.

What is important, however, is that for feeding, the food used for feeding should also be recorded. For simplicity, it was agreed that there is only one feed per feeding - so we do not expect to record mixtures of feeds.

Each feeding is carried out by a keeper. The keeeper is an employee and we must always know the personal number of the employee, which is the same throughout his employment and there is no case where two employees have the same number. We also need to know his address in which we must not forget the country. The essential information for an employee is his salary. For simplicity we record this information as the current salary, other things with the salary are already handled by the accountant outside our database. Every employee may have subordinates, however, there are also those who do not have subordinates and do not have supervisors - for example, the director of external relations, nobody talks to him, so he works alone in his workplace. However, we are only interested in employees in different roles, but we only deal with keepers and doctors as further links to our zoo database. However, it may be that an employee is both a doctor and a keeper at the same time, but may hold neither of these roles.

For a doctor, we record the doctor’s specialty, which is the best indication of his or her skills. A doctor may examine or assist in the examination of an animal. Occasionally, but rarely, a doctor will implement one measure for a given animal during an examination.

To work well with animals, it is also necessary to know their species and their current cage location in the section. The zoo has several sections and numbers the cages systematically from 1 to n. Thus, if someone asks for directions to the elephant enclosure, it is sufficient to tell the customer that he is in cage one in the savannah, or if he wants to go to the toad enclosure, he wants to go to the wetlands in cage one.

Since this is a zoo, it is good to know how much was collected in admission fees, so we still keep track of tickets for the cashier’s purposes. For tickets we are only interested in the price and the day it was sold.

2. ER schema

Not all entitities are in detail discussed in description above. Usually, during the database design there appear the necessity to include other aspects of the domain.

3. relational model

Somebody may prefer to see vizualized relational model instead of ER. Let us note, it includes foreign keys.

4. SQL scripts

Here we provide just links to create and insert scripts. If you want to experiment (in SQL), we recommend to create a standalone schema in your PostgreSQL database like this.

-- if the schema already exists, we can drop it (including content):
drop schema zoo_en cascade;

-- create a new empty one
create schema zoo_en;

-- switch default schema to zoo_en:
set search_path=zoo_en;

-- check if you are relly on zoo_en:
select current_schema();

-- run create script:
\i create-zoo.sql

-- run insert script:
\i insert-zoo.sql

5. Queries

Are in a separate document

6. Conclussions

I spent a lot of time on the term paper, and with hindsight I can responsibly say that its scope is excessive. I would never again choose more than 13 entities, because even on them I would be able to implement all queries without problems.

Relational Algebra wasn’t so bad as I could try it in portal. Overall, the portal made my job work fine - except that the portal can’t recognize the same RA and SQL results, only if they are sorted differently.

Furthermore, it would be appropriate to provide more instructions, for example how to work with sequences, when it is practically only mentioned in the sample term paper you are reading.

However, even though it wasn’t my first database project, I learned something and I’m glad I did.

7. References

8. Apendix