(1)Requirements
·
There are two types of customers, Permit holders
and Non-permit holders
·
Customer can contain customer ID, contact no,
Name, Permanent address
·
Permit Holders can contain a customer ID,
contact no, Name, Permanent address, Permit type, Permit no
·
Non-permit holders can contain a customer ID,
contact no, Name, Permanent address, tax
·
Car sale has many employees, a manager to
control business, several agents to deal with customers, importers to import
cars, exporters to buy cars from companies and send to the importers
·
Employee can contain an Employee ID, Name,
Permanent address, Contact no
·
Each agent has Employee ID, Name, Permanent
address, Contact no, Agent area
·
Manager deals with all agents, importers, and
manager Employee ID, Name, Permanent address, Contact no
·
Importers deals with exporters and import cars
from exporters in different countries
·
Importers have Employee ID, Name, Permanent
address, Contact no, I-warehouse address
·
Exporters have Employee ID, Name, Permanent
address, Contact no, E-warehouse address
·
Cars have Car ID, Car type, Chassis number,
Model version
·
Needs permission from government to import cars
and government has Department ID, Department name, Contact no, Email
·
Importer can import more than one car
·
Exporter can export more than one car
·
Payment has two different options named Finance
method, Direct cash
·
Payment has Payment ID, Payment Date, Price,
Discount
·
Finance method has Payment ID, Payment Date,
Price, Discount, Loan type
·
Direct cash has Payment ID, Payment Date, Price,
Discount, Pay method
Customer(Customer_ID,Name,No,Street)
Customer_phone(Customer_ID,Contact_No)
Foreign key(Customer_ID)references
Customer(Customer_ID)
Permit Holder(Customer_ID,Permit_no,Permit_type)
Foreign
key(Customer_ID)references Customer(Customer_ID)
Non Permit Holder(Customer_ID,Tax)
Foreign
key(Customer_ID)references Customer(Customer_ID)
Payment(Payment_ID,Payment_date,Price,Discount)
Finance Method(Payment_ID,Loan_type)
Foreign key(Payment_ID)references
Payment(Payment_ID)
Direct Cash(Payment_ID,Pay_Method)
Foreign
key(Payment_ID)references Payment(Payment_ID)
Cars(Car_ID,Car_Type,Chassis_Number,Model_version,Exporter_ID,Importer_ID)
Foreign key(Exporter_ID)references
Exporter(Employee_ID)
Foreign
key(Importer_ID)references Importer(Employee_ID)
Buy(Customer_ID,Payment_ID,Car_ID)
Foreign
key(Customer_ID)references Customer(Customer_ID)
Foreign
key(Payment_ID)references Payment(Payment_ID)
Foreign
key(Car_ID)references Cars(Car_ID)
Employee(Employee_ID,Name,Payment_Address)
Employee phone(Employee_ID,Contact_No)
Foreign key(Employee_ID)references
Employee(Employee_ID)
Exporter(Employee_ID,E-warehouse_Address)
Foreign key(Employee_ID)references
Employee(Employee_ID)
Manager(Employee_ID,Report)
Foreign key(Employee_ID)references
Employee(Employee_ID)
Agent(Employee_ID,Agent_area)
Foreign key(Employee_ID)references
Employee(Employee_ID)
Importer(Employee_ID,I-warehouse_address)
Foreign key(Employee_ID)references
Employee(Employee_ID)
Sends(Exporter_ID,Importer_ID)
Foreign key(Exporter_ID)references
Exporter(Employee_ID)
Foreign key(Importer_ID)references
Importer(Employee_ID)
Government(Department_ID,Email,Department_name,Car_ID)
Foreign
key(Car_ID)references Car(Car_ID)
Government_phone(Department_ID,Contact_No)
Foreign key(Department_ID)references
Government(Department_ID)
ISA hierarchy 1
Permit
holder, non-permit holder covers customer and these subclasses are not disjoint
Permit
holder has lots of attributes, So option 1 is most suitable for ISA hierarchy
ISA hierarchy 2
Finance
method, direct cash covers payment and these subclasses are disjoint
Payment
superclass has a relationship. For this ISA hierarchy the most suitable option
is 3
ISA hierarchy 3
Exporter,
Importer, Manager, Agent are not covering Employee and these subclasses are
disjoint.
Exporter
has lots of relationships, so option 2 is most suitable for this ISA hierarchy.
(4)Sample Tables
|
Customer_ID |
Name |
No |
Street |
|
12003 |
Kasun |
147 |
3Rd Lane |
|
12004 |
Dasun |
111 |
4Th Lane |
|
12005 |
Kamal |
123 |
5Th Lane |
|
12006 |
Amal |
154 |
6Th Lane |
|
12007 |
Srimal |
192 |
2nd Lane |
|
12008 |
Nimal |
186 |
9th Lane |
|
12009 |
Sunimal |
173 |
12th Lane |
|
120010 |
Vimal |
323 |
1st Lane |
|
Customer ID |
Contact No |
|
12003 |
072-6363251 |
|
12004 |
075-2323561 |
|
12005 |
071-3322394 |
|
12006 |
070-2222227 |
|
12007 |
077-3233237 |
|
12008 |
070-5262312 |
|
12009 |
076-9541121 |
|
120010 |
075-1245454 |
Permit Holder
|
Customer ID |
Permit_no |
Permit_type |
|
12003 |
Q789562 |
Full tax redundant |
|
12004 |
D456789 |
Half tax redundant |
|
12005 |
Z212123 |
40% tax redundant |
|
12008 |
E799216 |
10% tax redundant |
Non-Permit Holder
|
Customer ID |
Tax |
|
12006 |
200% |
|
12007 |
300% |
|
12009 |
400% |
|
120010 |
523% |
Payment
|
Payment ID |
Payment date |
Price(Rs:) |
Discount |
|
15003 |
12/03/2021 |
15,000,000 |
200,000 |
|
15004 |
15/03/2021 |
1,800,000 |
125,000 |
|
15005 |
18/04/2021 |
22,000,000 |
500,000 |
|
15006 |
22/04/2021 |
33,000,000 |
300,000 |
|
15007 |
25/04/2021 |
58,000,000 |
800,000 |
|
15008 |
25/04/2021 |
125,000,000 |
1,500,000 |
|
15009 |
25/04/2021 |
25,000,000 |
800,000 |
|
150010 |
25/04/2021 |
3,200,000 |
250,000 |
Finance Method
|
Payment ID |
Loan type |
|
15003 |
80% Loan |
|
15004 |
50% Loan |
|
15005 |
30% Loan |
|
15006 |
56% Loan |
|
15007 |
12% Loan |
Direct Cash
|
Payment ID |
Pay Method |
|
15008 |
Direct Bank Transfer |
|
15009 |
Cheque |
|
150010 |
Direct Bank Transfer |
Employee
|
Employee ID |
Name |
Payment Address |
|
56001 |
Dayarathne |
No 56, Kelaniya |
|
56002 |
Madeeshan |
No 59, Kurunegala |
|
56003 |
Mithun |
No 156, Peradeniya |
|
56004 |
Ariyarathna |
No 36, Negombo |
|
56005 |
Mendis |
No 86, Wariyapola |
|
56006 |
Kusal |
No 561, Kandy |
|
56007 |
Thilakarathna |
No 92, Warakapola |
|
56008 |
Malinga |
No 16, Verapola |
|
56009 |
Dananjaya |
No 114, Gampaha |
|
560010 |
Suvinath |
No 326, Chillaw |
Employee phone
|
Employee ID |
Contact No |
|
56001 |
+9870-1111112 |
|
56002 |
+9675-2222223 |
|
56003 |
076-3333334 |
|
56004 |
072-4444445 |
|
56005 |
077-5555556 |
|
56006 |
072-6666667 |
|
56007 |
070-7777778 |
|
56008 |
076-8888889 |
|
56009 |
072-9999999 |
|
560010 |
070-2323223 |
Exporter
|
Employee_ID |
E-warehouse_Address |
|
56001 |
456/89, Tokyo, Japan |
|
56002 |
878/546, New York, USA |
Manager
|
Employee ID |
Report |
|
56003 |
Status Completed |
Agent
|
Employee ID |
Agent area |
|
56004 |
Colombo |
|
56005 |
Kurunegala |
|
56006 |
Jaffna |
|
56007 |
Kandy |
|
56008 |
Anuradhapura |
Importer
|
Employee ID |
I-warehouse_address |
|
56009 |
Lanka Traders,45/3, Colombo 7 |
|
560010 |
Asian Traders,123/3, Kegalle |
Cars
|
Car ID |
Car Type |
Chassis Number |
Model version |
Exporter ID |
Importer ID |
|
45006 |
Sedan |
7894D65642 |
DFGDF4G6 |
56002 |
56009 |
|
45007 |
Coupe |
6545G98798 |
GFDGG49D |
56001 |
560010 |
|
45008 |
Hatchback |
4575T57857 |
TGF88564J |
56001 |
560010 |
|
45009 |
Sports Car |
5757H78524 |
DGDFG456 |
56002 |
56009 |
|
450010 |
Minivan |
7585U85875 |
DFGHFD45 |
56001 |
560010 |
|
450011 |
Sedan |
FG4DF6G5H4 |
45B5CBC64 |
56001 |
560010 |
|
450012 |
Coupe |
FG45HFG465 |
HFG465GFJ |
56002 |
56009 |
|
450013 |
Hatchback |
REW94RG6G |
7FG9DGHH |
56002 |
56009 |
Buy
|
Customer ID |
Payment ID |
Car ID |
|
12003 |
15003 |
45007 |
|
12004 |
15004 |
45008 |
|
12005 |
15005 |
45009 |
|
12006 |
15006 |
450010 |
|
12007 |
15007 |
450011 |
|
12008 |
15008 |
450012 |
|
12009 |
15009 |
450013 |
|
120010 |
150010 |
450014 |
Sends
|
Exporter ID |
Importer ID |
|
56002 |
56009 |
|
56001 |
560010 |
Government
|
Department ID |
Email |
Department name |
Car ID |
|
89007896G |
deptimpt@sltnet.lk |
Import and Export Control Department |
45006 |
|
89007896H |
deptimpt@sltnet.lk |
Import and Export Control Department |
45007 |
|
89007896J |
deptimpt@sltnet.lk |
Import and Export Control Department |
45008 |
|
89007896K |
deptimpt@sltnet.lk |
Import and Export Control Department |
45009 |
|
98004623A |
dgc@customs.gov.lk |
Sri Lanka Customs |
450010 |
|
98004623B |
dgc@customs.gov.lk |
Sri Lanka Customs |
450011 |
|
98004623C |
dgc@customs.gov.lk |
Sri Lanka Customs |
450012 |
|
98004623D |
dgc@customs.gov.lk |
Sri Lanka Customs |
450013 |
Government phone
|
Department ID |
Contact No |
|
89007896G |
011-2225634 |
|
89007896H |
011-2225635 |
|
89007896J |
011-2225636 |
|
89007896K |
011-2225638 |
|
98004623A |
011-5445423 |
|
98004623B |
011-5445424 |
|
98004623C |
011-5445425 |
|
98004623D |
011-5445426 |
SQL Codes
Create table Customer
(
Customer_ID integer primary key,
Name varchar(30) not null,
No int,
Street varchar(40) not null
)
Insert into Customer values(‘12003’,’Kasun’,’147’,’3rd
Lane’)
Insert into Customer values(‘12004’,’Dasun’,’111’,’4Th
Lane’)
Insert into Customer values(‘12005’,’Kamal’,’123’,’5Th
Lane’)
Insert into Customer values(‘12006’,’Amal’,’154’,’6Th
Lane’)
Insert into Customer values(‘12007’,’Srimal’,’192’,’2nd
Lane’)
Insert into Customer values(‘12008’,’Nimal’,’186’,’9th
Lane’)
Insert into Customer values(‘12009’,’Sunimal’,’173’,’12th
Lane’)
Insert into Customer values(‘120010’,’Vimal’,’323’,’1st
Lane’)
Create table Customer_phone
(
Customer_ID integer,
Contact_No integer,
constraint Customer_phone_pk primary key (Customer_ID),
constraint Customer_phone_Customer_fk foreign key
(Customer_ID) references Customer
)
Insert into Customer_phone values(‘12003’,’0726363251’)
Insert into Customer_phone values(‘12004’,’0752323561’)
Insert into Customer_phone values(‘12005’,’0713322394’)
Insert into Customer_phone values(‘12006’,’0702222227’)
Insert into Customer_phone values(‘12007’,’0773233237’)
Insert into Customer_phone values(‘12008’,’ 070-5262312’)
Insert into Customer_phone values(‘12009’,’ 076-9541121’)
Insert into Customer_phone values(‘120010’,’ 075-1245454’)
Create table Permit_Holder
(
Customer_ID integer,
Permit_no varchar(20),
Permit_type varchar(20),
constraint Permit_Holder_pk primary key (Customer_ID),
constraint Permit_Holder_Customer_fk foreign key
(Customer_ID) references Customer
)
Insert into Permit_Holder values (‘12003’,’Q789562’,’ Full
tax redundant’)
Insert into Permit_Holder values (‘12004’,’ D456789’,’ Half
tax redundant’)
Insert into Permit_Holder values (‘12005’, Z212123,’ 40% tax
redundant’)
Insert into Permit_Holder values (‘12008’, E799216,’ 10% tax
redundant’)
Create table Non_Permit_Holder
(
Customer_ID integer,
Tax varchar(30),
constraint Non_Permit_Holder_pk primary key (Customer_ID),
constraint Non_Permit_Holder_Custome_fk foreign key
(Customer_ID) references Customer
)
Insert into Non_Permit_Holder values (‘12006’,’200%’)
Insert into Non_Permit_Holder values (‘12007’,’300%’)
Insert into Non_Permit_Holder values (‘12009’,’400%’)
Insert into Non_Permit_Holder values (‘120010’,’523%’)
Create table Payment
(
Payment_ID integer primary key,
Payment_Date varchar(15),
Price real,
Discount varchar(15)
)
Insert into Payment values (‘15003’,’12/03/2021’,’15,000,000’,’200,000’)
Insert into Payment values (‘15004’,’15/03/2021’,’ 1,800,000’,’125,000’)
Insert into Payment values (‘15005’,’18/04/2021’,’ 22,000,000’,’500,000’)
Insert into Payment values (‘15006’,’22/04/2021’,’ 33,000,000’,’300,000’)
Insert into Payment values (‘15007’,’23/04/2021’,’ 58,000,000’,’800,000’)
Insert into Payment values (‘15008’,’24/04/2021’,’ 125,000,000’,’1,500,000’)
Insert into Payment values (‘15009’,’25/04/2021’,’ 25,000,000’,’800,000’)
Insert into Payment values (‘150010’,’26/04/2021’,’ 3,200,000’,’250,000’)
Create table Finance_method
(
Payment_ID integer,
Loan_type varchar(30),
constraint Finance_Method_pk primary key (Payment_ID),
constraint Finance_method_Payment_fk foreign key (Payment_ID)
references Payment
)
Insert into Finance_method values (‘15003’,’80% Loan’)
Insert into Finance_method values (‘15004’,’50% Loan’)
Insert into Finance_method values (‘15005’,’30% Loan’)
Insert into Finance_method values (‘15006’,’56% Loan’)
Insert into Finance_method values (‘15007’,’12% Loan’)
Create table Direct_cash
(
Payment_ID integer,
Pay_method varchar(30),
constraint Direct_Cash_pk primary key (Payment_ID),
constraint Direct_Cash_Payment_fk foreign key (Payment_ID)
references Payment
)
Insert into Direct_cash values (‘15008’,’Direct Bank
Transfer’)
Insert into Direct_cash values (‘15009’,’Cheque’)
Insert into Direct_cash values (‘150010’,’Direct Bank
Transfer’)
Create table Employee
(
Employee_ID integer primary key,
Name varchar(30),
Payment_address varchar(30),
)
Insert into Employee values (‘56001’,’ Dayarathne’,’ No 56,
Kelaniya’)
Insert into Employee values (‘56002’,’ Madeeshan’,’ No 59,
Kurunegala’)
Insert into Employee values (‘56003’,’ Mithun’,’ No 156,
Peradeniya’)
Insert into Employee values (‘56004’,’ Ariyarathna’,’ No 36,
Negombo’)
Insert into Employee values (‘56005’,’ Mendis’,’ No 86,
Wariyapola’)
Insert into Employee values (‘56006’,’Kusal’,’ No 561,
Kandy’)
Insert into Employee values (‘56007’,’ Thilakarathna’,’ No
92, Warakapola’)
Insert into Employee values (‘56008’,’ Malinga’,’ No 16,
Verapola’)
Insert into Employee values (‘56009’,’ Dananjaya’,’ No 114,
Gampaha’)
Insert into Employee values (‘560010’,’ Suvinath’,’ No 326,
Chillaw’)
Create table Employee_phone
(
Employee_ID integer,
Contact_no varchar(30),
constraint Employee_Phone_pk primary key (Employee_ID),
constraint Employee_phone_Employee_fk foreign key
(Employee_ID) references Employee
)
Insert into Employee_phone values (‘56001’,’+9870-1111112’)
Insert into Employee_phone values (‘56002’,’+9675-2222223’)
Insert into Employee_phone values (‘56003’,’076-3333334’)
Insert into Employee_phone values (‘56004’,’072-4444445’)
Insert into Employee_phone values (‘56005’,’077-5555556’)
Insert into Employee_phone values (‘56006’,’072-6666667’)
Insert into Employee_phone values (‘56007’,’070-7777778’)
Insert into Employee_phone values (‘56008’,’076-8888889’)
Insert into Employee_phone values (‘56009’,’072-9999999’)
Insert into Employee_phone values (‘560010’,’070-2323223’)
Create table Exporter
(
Employee_ID integer,
E_warehouse_address varchar(30),
constraint Exporter_pk primary key (Employee_ID),
constraint Exporter_Employee_fk foreign key (Employee_ID)
references Employee
)
Insert into Exporter values (‘56001’,’ 456/89, Tokyo, Japan’)
Insert into Exporter values (‘56002’,’ 878/546, New York,
USA’)
Create table Manager
(
Employee_ID integer,
Report varchar(30),
constraint Manager_pk primary key (Employee_ID),
constraint Manager_Employee_fk foreign key (Employee_ID)
references Employee
)
Insert into Manager values (‘56003’,’Status completed’)
Create table Agent
(
Employee_ID integer,
Agent_area varchar(30),
constraint Agent_pk primary key (Employee_ID),
constraint Agent_Employee_fk foreign key (Employee_ID)
references Employee
)
Insert into Agent values (‘56004’,’Colombo’)
Insert into Agent values (‘56005’,’Kurunegala’)
Insert into Agent values (‘56006’,’Jaffna’)
Insert into Agent values (‘56007’,’Kandy’)
Insert into Agent values (‘56008’,’Anuradhapura’)
Create table Importer
(
Employee_ID integer,
I_warehouse_address varchar(30),
constraint Importer_pk primary key (Employee_ID),
constraint Importer_Employee_fk foreign key (Employee_ID)
references Employee
)
Insert into Importer values (‘56009’,’ Lanka Traders,45/3,
Colombo 7’)
Insert into Importer values (‘560010’,’ Asian Traders,123/3,
Kegalle’)
Create table Cars
(
Car_ID integer,
Car_type varchar(30),
Chassis_number varchar(30),
Model_version varchar(30),
Exporter_ID integer,
Importer_ID integer,
constraint Cars_pk primary key
(Car_ID,Exporter_ID,Importer_ID),
constraint Exporter_Cars_Employee_fk foreign key
(Exporter_ID) references Exporter
constraint Importer_Cars_Employee_fk foreign key
(Importer_ID) references Importer
)
Insert into Cars values (‘45006’,’Sedan’,’ 7894D65642’,’ DFGDF4G6’,’56002’,’56009’)
Insert into Cars values (‘45007’,’ Coupe’,’ 6545G98798’,’ GFDGG49D,’56001’,’560010’)
Insert into Cars values (‘45008’,’ Hatchback’,’ 4575T57857’,’
TGF88564J,’56001’,’560010’)
Insert into Cars values (‘45009’,’ Sports Car’,’ 5757H78524’,’
DGDFG456,’56002’,’56009’)
Insert into Cars values (‘450010’,’ Minivan’,’ 7585U85875’,’ DFGHFD45,’56001’,’560010’)
Insert into Cars values (‘450011’,’ Sedan’,’ FG4DF6G5H4’,’ 45B5CBC64,’56001’,’560010’)
Insert into Cars values (‘450012’,’ Coupe’,’ FG45HFG465’, ‘HFG465GFJ’,’56002’,’56009’)
Insert into Cars values (‘450013’,’ Hatchback’,’ REW94RG6G’, ‘7FG9DGHH’,’56002’,’56009’)
Create table Buy
(
Customer_ID integer,
Payment_ID integer,
Car_ID integer,
constraint Buy_pk primary key
(Customer_ID,Payment_ID,Car_ID),
constraint Buy_Customer_fk foreign key (Customer_ID)
references Customer
constraint Buy_Payment_fk foreign key (Payment_ID) references
Payment
constraint Buy_Cars_fk foreign key (Car_ID) references Cars
)
Insert into Bus values (‘12003’,’15003’,’45007’)
Insert into Bus values (‘12004’,’15004’,’45008’)
Insert into Bus values (‘12005’,’15005’,’45009’)
Insert into Bus values (‘12006’,’15006’,’450010’)
Insert into Bus values (‘12007’,’15007’,’450011’)
Insert into Bus values (‘12008’,’15008’,’450012’)
Insert into Bus values (‘12009’,’15009’,’450013’)
Insert into Bus values (‘120010’,’150010’,’450014’)
Create table Sends
(
Exporter_ID integer,
Importer_ID integer,
constraint Sends_pk primary key (Exporter_ID,Importer_ID),
constraint Exporter_Sends_Employee_fk foreign key
(Exporter_ID) references Exporter
constraint Importer_Sends_Employee_fk foreign key
(Importer_ID) references Importer
)
Insert into Sends values (‘56002’,’56009’)
Insert into Sends values (‘56001’,’560010’)
Create table Government
(
Department_ID varchar(30),
Email varchar(30),
Department_name varchar(30),
Car_ID integer,
constraint Government_pk primary key (Department_ID,Car_ID),
constraint Government_Cars_fk foreign key (Car_ID) references
Cars
)
Insert into Government values (‘89007896G’,’ deptimpt@sltnet.lk’,’
Import and Export Control Department’,’45006’)
Insert into Government values (‘89007896H’,’ deptimpt@sltnet.lk’,’
Import and Export Control Department’,’45007’)
Insert into Government values (‘89007896J’,’ deptimpt@sltnet.lk’,’
Import and Export Control Department’,’45008’)
Insert into Government values (‘89007896K’,’ deptimpt@sltnet.lk’,’
Import and Export Control Department’,’45009’)
Insert into Government values (‘98004623A’,’ dgc@customs.gov.lk’,’
Sri Lanka Customs’,’450010’)
Insert into Government values (‘98004623B’,’ dgc@customs.gov.lk’,’
Sri Lanka Customs’,’450011’)
Insert into Government values (‘98004623C’,’ dgc@customs.gov.lk’,’
Sri Lanka Customs’,’450012’)
Insert into Government values (‘98004623D’,’ dgc@customs.gov.lk’,’
Sri Lanka Customs’,’450013’)
Create table Government_phone
(
Department_ID varchar(30),
Contact_no integer,
constraint Government_phone_pk primary key (Department_ID),
constraint Govern_phone_Govern_fk foreign key (Department_ID)
references Government
)
· Select customer name who have bought “Sedan” car type and group it with a
condition
Select
c.Name, c.Car_ID,
From
Cars a, Buy b, Customer c,
Where
c.Customer_ID=b.Customer_ID, b.Car_ID=a.Car_ID and Car Type =’Sedan’,
Group
by c.Name, c.Car_ID ,
Having
count (c.Car_ID) > 0
· Select customer name and ID who paid more than 10,000,000
Select
s.Name,b.Payment_ID,
From
Buy b, Customer c,
Where b.Customer_ID = c.Customer_ID and Price > Any(Select p.Price From Payment p, Where p.Price>10,000,000)
(5) Procedure that output Department ID and Department
Name that got permission by a given Cars Car ID
create procedure getDepartmentInfo (@Car_ID int,
@Department_ID varchar(30) output, @Department_Name varchar(30) output)
as
begin
select @Department_ID = go.Department_ID, @Department_Name =
go.Department_name
from Government go, Cars ca,
where ca.Car_ID = go.Car_ID and ca.Car_ID = @Car_ID
end
declare @dep_ID varchar(30)
declare @dep_Name varchar(30)
exec getDepartmentInfo ‘45008’ , @dep_ID output, @dep_Name
output
print @dep_ID
print @dep_Name
(6) Create trigger to ensure that a importer does not
import more than five cars
create trigger CheckNumCars
on Cars
for insert,update
as
begin
declare @Importer_ID int
declare @Total_cars int
select @Importer_ID = Importer_ID from inserted
select @Total_cars = count(Car_ID) from Cars where
Importer_ID = @Importer_ID
if @Total_cars > 5
begin
print ‘Cannot import more than five cars’
rollback transaction
end
end
insert into Cars values(‘56009’,’45005’)