Design of a database about Zoos

I am trying to design a database storing data about several Zoos. I just want to ask if my design is good. I know pretty well SQL and queries, but I'm not an expert at design. Requirement:

You will manage zoos, animals, food, visitors and visits. Each zoo has an id, an administrator, a name and several animals. An animal has an id, a name and date of birth; it can eat various foods, the latter consisting of an id and a name. The system stores the daily quota(integer number) for each animal and food, e.g., an animal A1 ; animal A2 . A visitor is characterized by a personal number(an id), name and age. A visitor can visit several zoos. Such a visit is defined by a unique identifier, a day, the paid price, the visitor's personal number and the zoo id.

This is how I've done the design:

CREATE TABLE Zoo(
    id INT PRIMARY KEY IDENTITY(1,1),
    administrator VARCHAR(30),
    name VARCHAR(40),
);

CREATE TABLE Animal(
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(30),
    dob DATE,
    zoo_id INT REFERENCES Zoo(id)
);

CREATE TABLE Food(
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(25)
);

CREATE TABLE DailyQuota(
    id INT PRIMARY KEY IDENTITY(1,1),
    animal_id INT REFERENCES Animal(id),
    food_id INT REFERENCES Food(id),
    quantity INT
);

CREATE TABLE Visitor(
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(40),
    age TINYINT
);

CREATE TABLE Visit(
    id INT PRIMARY KEY IDENTITY(1,1),
    zoo_id INT REFERENCES Zoo(id),
    visitor_id INT REFERENCES Visitor(id),
    day DATE,
    paid_price SMALLINT
);

Want to hear any opinions

I also had to create a view that shows the ids of the zoos woth the smallest number of visitors. This is how I have done it:

```
GO
CREATE OR ALTER VIEW view_smallestCountVisitors
AS
SELECT t1.zoo_id
FROM (SELECT v.zoo_id, COUNT(v.zoo_id) MYCOUNT
FROM Visit v
GROUP BY v.zoo_id) t1
WHERE t1.MYCOUNT IN (
SELECT MIN(t2.MYCOUNT)
FROM (SELECT v.zoo_

While you've done a pretty thorough job of following the textbook requirements, there are some practical considerations that you might or might not want to include, depending on the expectations of the assignment. Some of these have to do with bad requirements. I don't know if you would get extra marks or reduced marks for pointing these out.

• Food quantity as an integer is probably not precise enough

• Food quantities should have a unit of measure. Some foods will be dry, some will be liquid. Iguanas probably need a few grams of "Iguana Chow" whereas elephants will need multiple kilos of whatever they're eating.

• Amounts of money should be stored as MONEY or another non-integer data type.

• If your zoos are all over the place you might need to consider storing a currency with the monetary amount

• Storing the age of a visitor is a mistake. Visitors age between visits. You're better off storing the date of birth of the visitor and calculating their age at each visit, since you know the date of the visits

• In a real system, some of this information is going to be optional. Not everyone is going to tell you their date of birth. Similarly, you may not know when your giant tortoise was born.

• People's names, if they matter, are usually stored in more than one field (e.g. given name and family name)

• It's highly unlikely that every animal will have a personal record of their dietary requirements. Some animals might, but for a lot of animals it might make more sense to store the requirements for all animals of a given species.

• The fact that animal species is not accounted for in the model is kind of questionable, since you would think that most zoos would care about something like that

StackExchange Database Administrators Q#282198, answer score: 13