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.
DailyQuota
and probablyVisit
need unique constraints. For example, you can insert same(animal_id, food_id)
combination intoDailyQuota
multiple times. – Damir Sudarevic Dec 27 '20 at 15:54