SQL Server – Database Design for Zoos

database-designsql server

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 <food F1, 10; food F2, 5>; animal A2 <food F2, 1; food F5, 2>. 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_id, COUNT(v.zoo_id) MYCOUNT
                FROM Visit v
                GROUP BY v.zoo_id) t2
        );
GO

It works fine but I am just wondering if this can be done in a shorter way.

Best Answer

I don't think there's much to say, it looks like you captured the requirements of the instructions you were given. The only small remark is when the instructions say "a visit is defined by a unique identifier", sometimes "unique identifier" is synonymous with the word GUID (and in some database systems UniqueIdentifier is the actual data type name for a GUID, like Microsoft SQL Server). Not sure if it's meant to be interpreted that way in this context, but figured I'd make you aware.

To answer your second question regarding shortening your view to get the Zoo_Id with the fewest visits, you can do the following that uses a CTE and the ROW_NUMBER() window function:

WITH CTE_ZooVisits_Sorted AS
(
    SELECT v.zoo_id, ROW_NUMBER() OVER (ORDER BY COUNT(v.zoo_id), v.zoo_id) AS SortId -- Generates a unique sequential ID, ordered by the number of Zoo visits, then by the Zoo's ID to break any ties
    FROM Visit v
    GROUP BY v.zoo_id
)

SELECT zoo_id
FROM CTE_ZooVisits_Sorted
WHERE SortId = 1 -- Returns only one row with the minimum amount of Zoo Visits (ties broken by whichever Zoo was created first)

Note with ROW_NUMBER() when there's a tie, it randomly chooses which one comes first in the sort unless you provide a unique field as the tie-breaker, which in my example above I did by zoo_id. (What this logically means is if two Zoo's are tied for number of visits, the Zoo that was created first will break the tie and sorted first.) You can remove the WHERE SortId = 1 in the final SELECT and replace it with an ORDER BY SortId to get the full list of zoo_id ordered by the least amount of visits to most amount of visits.

If you want an alternative where you want to sort ties in the same order without a tie-breaker then instead of the ROW_NUMBER() window function, you can use RANK() or DENSE_RANK() like so:

WITH CTE_ZooVisits_Sorted AS
(
    SELECT v.zoo_id, DENSE_RANK() OVER (ORDER BY COUNT(v.zoo_id)) AS SortId -- Generates a sequential ID, ordered by the number of Zoo visits, ties will have the same sequential ID generated
    FROM Visit v
    GROUP BY v.zoo_id
)

SELECT zoo_id
FROM CTE_ZooVisits_Sorted
WHERE SortId = 1 -- Returns all rows with the minimum amount of Zoo Visits (multiple rows for when there's a tie among minimum visits between multiple Zoos)

Note using a window function like ROW_NUMBER(), RANK(), or DENSE_RANK() is additionally helpful because it lets you select any and all fields of the row that has the minimum number of zoo visits (or whatever sort criteria you want to use).

Related Question