Relationship between tables

Question

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 3-Mar-23 6:45am

Peshhobs

Updated 9-Apr-23 17:13pm

RedDk

v2

Add a Solution

Comments

[no name] 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.

[no name] 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.

[no name] 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

Add a Solution

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

mtoha · Answer 1 · 2023-04-09T17:13:00

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?

Relationship between tables

1 solution

Solution 1

Add your solution here

Preview 0

Relationship between tables

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us