Click here to Skip to main content
15,672,526 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, can you give me some advice - mostly about table relationships, because that's the hardest part for me?

Information should be displayed on:
- Species, breeds of animals
- Description of animals
- Registration of sales
- Customers
- Employees

as requests I need to
-get all the animals grouped by type
-the total number of animals grouped by type
-a list of the names of the customers and the animals they bought
-animals with a maximum threshold of life and those with a minimum
-price list of animals

That's what I came up with. Can you give me some guidance on what I can add, remove or change?

What I have tried:

SQL
create table pets(
id_pet int identity not null primary key,
type_pets nvarchar (20) not null,
breed nvarchar (20) not null,
stock int not null,
);

-- description of an animal -- 
create table description_of_animal (
ages decimal (2,2),
weight decimal (10,3),
gender nvarchar (30),
threshold_of_life nvarchar (45) not null,
color nvarchar (20) not null,
price decimal (5,4),
vaccine BIT
);

-- registration sales --
create table registr_sales (
id_sales int identity not null primary key,
id_clent int not null,
id_employee l int not null,
date_sales date not null,
);

-- table client --
create table client(
id_client int identity not null primary key,
fname nvarchar (30),
lname nvarchar (30),
adress nvarchar (30),
phone_number  varchar(15) not null,
email_address varchar(30),
);

-- table employee-- 
create table employee (
id_employee int identity not null primary key,
fname nvarchar (20) not null,
lname nvarchar (20) not null,
city nvarchar(50) not null,
adress nvarchar(50) not null,
phone_number nvarchar(15) not null
);

--tbl employee_pass
create table employee_pass (
employee_id int not null primary key,
username nvarchar(50) not null,
passwords nvarchar(50) not null
);

-- connect with employee and employee_pass
alter table employee_pass
add constraint fk1
foreign key (employee_id)
references employee(id_employee)
Posted
Updated 9-Apr-23 17:13pm
v2
Comments
Gerry Schmitz 3-Mar-23 14:30pm    
What's the difference between "type_pets" and "breed"? You need a "key" to relate your "description" table with your "stock / inventory" table. "type_pets"? Why isn't "breed" part of "description"? (You should worry about "passwords", etc. last)
Peshhobs 3-Mar-23 15:07pm    
type_pets : like dogs, fish, cats
breed : like if i have pet dog - the breed will be akita, bulldog etc
Richard Deeming 6-Mar-23 4:00am    
That means your data is not normalized. You will end up with multiple rows in your pets table with type_pets = 'dog'. With no restriction on what can be entered in that field, you will get typos and variations - 'dig', 'chien', 'hound', etc. - meaning you will have no way to answer simple questions like "How many dogs do we have in stock?"

You need to extract the pet types to its own table, and have a foreign key relationship to the pets table.
Member 15627495 4-Mar-23 10:28am    
you forget "amount" in the table 'sale', without a price : no sales !

I see lots of missing relation established by duet "primary key" link as "foreign key". both are required to have consistancy between tables.
Databases are about "relations" between Datas.
Member 15627495 4-Mar-23 10:32am    
the two tables 'CLIENT' and 'EMPLOYEE' have a lot of common fields.
maybe those two tables need a 'PEOPLE' table to gather all common datas and fields

1 solution

I think you should change this :

create table pets(
id_pet int identity not null primary key,
type_pets nvarchar (20) not null,
breed nvarchar (20) not null,
stock int not null,
created_by varchar(20),
--getdate()
created_date datetime
);

-- description of an animal -- 
create table description_of_animal (
ages decimal (2,2),
weight decimal (10,3),
gender nvarchar (30),
threshold_of_life nvarchar (45) not null,
color nvarchar (20) not null,
price decimal (5,4),
vaccine BIT,
created_by varchar(20),
--getdate()
created_date datetime
);


Because, this information should be displayed, you need some master table, and change your existing tables : pets, and description_of_animal .
Information should be displayed on:
- Species, breeds of animals
- Description of animals


Try to change the existing table to be like this approximately.
create table pets(
id_pet int identity not null primary key,
type_pets nvarchar (20) not null,
breed nvarchar (20) not null,
stock int not null,
created_by varchar (20),
created_date varchar(20)
);

-- description of an animal -- 
create table description_of_animal (
--id is auto generated ID
id int identity(1,1) not null primary key,
--foreign key for pets table
id_pet int,
ages decimal (2,2),
weight decimal (10,3),
gender nvarchar (30),
threshold_of_life nvarchar (45) not null,
color nvarchar (20) not null,
price decimal (5,4),
vaccine BIT,
created_by varchar (20),
created_date varchar(20)
);

create table breed (
id int identity(1,1) not null primary key,
code varchar(20),
name varchar(100)
)

create table type_pets (
id int identity(1,1) not null primary key,
code varchar(20),
name varchar(100)
)



Try to make clear the information, from system analyst. what is difference between breeds and Species. Is that making difference on some part functionalities like price, or filtering?
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900